Avg. Lock Wait Time
ignacio.jose
Posts: 2
I had a request from my manager to see what is causing the Avg. Lock Wait Time to increase over a period of time. I have identified the date and time where the Metrics Analysis graph is showing the peaks and gone into the Global Overview to see what was happening on the last 15 minutes at a particular date and time.
Now the Global overview is showing me the TOP 10 Waits and the Top 10 Queries. My question is what wait types does the Avg. Lock Wait Time include out of the Top 10 Waits. Does it include all wait types or just those specific to database transactional lock waits? Eg. does it include CXPACKET, PREEMPTIVE_OS_AUTHENTICATION, WRITELOG, SOS_SCEDULER_YIELD, DBMIRROR_EVENT_QUEUE .... or just CXPACKET, LATCH_EX, PAGEIOLATCH_SH, PAGEIOLATCH_EX...
Now the Global overview is showing me the TOP 10 Waits and the Top 10 Queries. My question is what wait types does the Avg. Lock Wait Time include out of the Top 10 Waits. Does it include all wait types or just those specific to database transactional lock waits? Eg. does it include CXPACKET, PREEMPTIVE_OS_AUTHENTICATION, WRITELOG, SOS_SCEDULER_YIELD, DBMIRROR_EVENT_QUEUE .... or just CXPACKET, LATCH_EX, PAGEIOLATCH_SH, PAGEIOLATCH_EX...
Comments
The description of the metric from the Analysis graph page is:
So I believe any wait that would be included in that Perfmon counter would be included.
https://technet.microsoft.com/en-us/library/aa905137%28v=sql.80%29.aspx
I believe the top waits are collected via a different method and include some of those waits you mention.
Kind regards,
Alex
Have you visited our Help Center?