Avg. Lock Wait Time

ignacio.jose

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...

Alex B

Hi Ignacio,

The description of the metric from the Analysis graph page is:

Avg. Lock Wait Time: The average wait time (in milliseconds) for each lock that could not be satisfied immediately and had to wait for resources.
Equivalent PerfMon counter: SQLServer:Locks - Average Wait Time (ms).

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