SQL Server – Find tables with page locks using sys.dm_db_index_operational_stats

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.

If the index_id is 0 – it’s a heap

---identifying page locks with sys.dm_db_index_operational_stats
SELECT      TOP 10
            OBJECT_NAME(OBJECT_ID, database_id) OBJ_NAME,
            index_id,
            page_lock_wait_count, 
            page_lock_wait_in_ms
FROM sys.dm_db_index_operational_stats (DB_ID(), NULL, NULL, NULL) 
ORDER BY page_lock_wait_in_ms DESC

Read More

What is SQL Server Query Tuning?

SQL Server – Find queries causing Lock Timeouts per second -