Detecting Blocking Problem

By : Kasim Wirama, MCDBA

SQL Server uses locks as ACID implementation. Locks ensure data consistency in multiuser environment in database. Locks become issue when several processes waiting for a resource that is currently held by incompatible lock. In this situation, a process blocks other processes to access shared resource. When your database had excessive locking issue, you need to find out immediately and resolve it. I will tell you how you monitor your server regarding to blocking with performance monitor and DMV for more information about blocking.

You can have information from these performance counter name below to get sense about current situation for blocking. They are : SQLServer:Locks: Average Wait Time, SQLServer:Locks: Lock Requests/sec, SQLServer:Locks: Lock Wait Time, SQLServer:Locks: Lock Waits/sec, SQLServer:Locks: Number of Deadlocks/Sec, SQLServer:General Statistics: Processes Blocked and SQLServer:Access Method: Table Lock Escalations/Sec.

Besides these counters above, you can get history of shared lock and exclusive lock of pageiolatch by querying DMV sys.dm_os_wait_stats

SELECT wait_type, waiting_tasks_count, wait_time_ms_max_wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC

The wait type you need to notice is LCK_M_S, PAGEIOLATCH_SH and PAGEIOLATCH_EX. Compare wait_time_ms and max_wait of them, if percentage of max_wait againsts wait_time_ms is large, you need monitor blocking events from profiler.

Another DMV that is useful to detect current blocking is sys.dm_tran_locks. To get the query involved in blocking, you need to join the DMV with other DMVs, i.e. sys.dm_os_waiting_tasks and sys.dm_exec_requests.