1 of 1 people found this helpful
Hey Mike, here are some of my thoughts and things for you to look at.
Commits is being called out as it's own activity/statement likely because that's how it's being used by the application.
Think in terms like the following transaction:
update table foo set bar = 10
insert into table foo2 values (...)
delete from users where userid = (select userid from users where username = 'Mike.King')
In this one transaction or unit of work, you have 3 SQL statements plus a commit. Each statement (and the commit) will accrue wait time against them which will be tracked/displayed by DPA.
When a commit is done, the database then has to persist that transaction to the transaction log so that it can replay it in case of a recovery since the last backup point (oversimplifying here a bit).
The time it takes to persist that transaction to the tran log is tracked by SQL Server under the WRITELOG wait type.
Things to look for:
- Make sure the write latency for the storage where the transaction log resides is healthy (if mission critical database, less than 10ms). You can look for this by looking at the "Resources" correlation tab below the wait times in trend and timeslice. Look for a metric called SQL disk write latency. You can also drill into an hour in the Storage I/O tab and select the file for the transaction log, then select latency vs throughput to see what kind of volume and stalls I/O requests are being experienced for this file.
- Also, look at the logic for the transactions as well. It's not uncommon for high volume transactions to commit after each and every update say instead of batching them up (batching means significantly reduced commits and reduced wait times).
Hope this helps and provides you at least some things to dig into.
Others may have thoughts or things to look at as well...
Thanks for the reply it's really appreciated.
Ok that makes sense, with regard to it tracking it's own time and not being directlylinked. I can't really find any queries before hand though that are run in transactions. 90% of our calls are via Entity Framework, and the rest are Sprocs. I will dig into that further to see if there's anything I'm missing.
As for the disk and latency stats I've added the charts below. There definitely seems to be an issue here, but I guess it's chicken and egg and trying to see what is causing this spike, although there doesn't seem to be anything application based that would cause this when i investigate the SQL, mainly selects rather than update/inserts.
Few additional questions i have:
* I can see Tempdb/templog spike before everything else and seem to set the latency on an upward trend, if i can see the "commits" on DB A, is it possible that DB B is the cause which then makes DB A slow? Note all the logs/files are on the same disk, but our customer has discounted this as the cause.
* In the final graph the biggest proportion of Wait time is other waits, from reading i have done on the forum I can see that this is non-IO waits, so does this imply the issue is actually elsewhere?
1 of 1 people found this helpful
Let me verify if WRITELOG is included in i/o waits. I think it should be, but want to make sure.
This may be easier to discuss on a phone call... Can you open a support case and mention you have been working with me on this?
We can coordinate a time next week to hop on a screenshare and investigate more. (I'm currently OOO until Friday)
Let me know your thoughts.
Yeah that's great. I've raised the support call, so we'll arrange a call next week.