Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

High Waits for LCK Expand / Collapse
Posted Tuesday, October 23, 2012 1:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 13, 2016 1:22 AM
Points: 1,416, Visits: 2,931
Hi All

I'm using the following DMV to get an idea of the highest waits on my system:
wait_type ,
wait_time_ms ,
signal_wait_time_ms ,
wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )
AS percent_total_waits ,
100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )
AS percent_total_signal_waits ,
100.0 * ( wait_time_ms - signal_wait_time_ms )
/ SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0 -- remove zero wait_time
AND wait_type NOT IN -- filter out additional irrelevant waits
ORDER BY wait_time_ms DESC

My system reports that 23% of waits have been for the LCK_M_S wait type.

My SQL instance has 20 databases, how can I track down which database is experiencing these lock waits?

Also, what is the most common cause of a blocking problem?

Post #1375864
Posted Tuesday, October 23, 2012 1:48 AM



Group: General Forum Members
Last Login: Today @ 9:20 AM
Points: 1,950, Visits: 11,012
This may be worth looking at where it also links to

sys.dm_os_waiting_tasks will show lock resources and linking it to sys.dm_exec_requests will give you the database id.

Steve Hall
Blog Site
Post #1375882
Posted Tuesday, October 23, 2012 2:27 AM



Group: General Forum Members
Last Login: Saturday, December 3, 2016 5:18 AM
Points: 45,619, Visits: 44,147
Chapter 6:

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1375897
Posted Wednesday, October 24, 2012 7:06 AM



Group: General Forum Members
Last Login: Today @ 8:08 PM
Points: 5,665, Visits: 8,189
1) sp_whoisactive can help you see locking waits in real time (along with a TON of other useful features - and it is FREE)

2) the most common cause of lock waits is suboptimal indexing

3) others provided you with links to help you figure out what is being blocked by what


Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1376452
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse