SQL Server blocking is a common SQL Server performance issue. Once blocking is identified as the main bottleneck - it’s useful to identify the tables experiencing the highest levels. In the scenario below, blocking is identified as a bottleneck , and the DMV sys.dm_db_index_operational_stats is queried to list the tables with the highest waits on page locks.

Once you’ve identified the tables – analyse the usage and decide on relevant action. For example, there may be a missing index.

Common methods of identifying blocking are :

Waits and Queues analysis

Top Wait Stats include : LCK_*

Deadlocks are high

According to SQL Server Books Online . the relevant columns for page locks are page_lock_wait_count, and page_lock_wait_in_ms.