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

05 November,2012 by Jack Vamvas

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 -


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server – Find tables with page locks using sys.dm_db_index_operational_stats


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer