SQL Server Cached blocking history with sys.dm_db_index_operational_stats

15 March,2012 by Jack Vamvas

Transactions generate IO, latching  and  locking on tables and indexes  , while attempting to access data. The sys.dm_db_index_operational_stats  DMV returns aggregated data on this activity.

Warning : sys.dm_db_index_operational_stats returns data  only as long as the metadata cache object that represents the heap or index is available. A frequently used object is more likely to have metadata availale, whereas a infrequently used object less likely.

The columns row_lock_wait_in_ms + page_lock_wait_in_ms (Block waits ms) focus on lock contention and wait time.  This aggregate is a good indicator of Block Wait time.  When there is a clear pagelock or rowlock completed it’s recorded . Range locking is not included , which makes the row_lock_wait_in_ms + page_lock_wait_in_ms inaccurate.

How to use ?

1)       In conjunction with the sys.dm_db_index_usage_stats , a DBA creates a profile of index usage and blocking. A typical scenario is : sys.dm_db_index_operational_stats  returns high Block waits ms for an index. Upon closer analysis the DBA observes that multiple indexes must be updated – even though those indexes are never used.

2)       A DBA identifies high blocking on a table.   On closer analysis it’s other tables  creating the delays.

3) The Blocked Process Report and how to read    explains how to capture blocking . Use the information to analyse queries

select db_name(database_id) DB,
QUOTENAME(OBJECT_SCHEMA_NAME(object_id, database_id)) 
+ N'.' 
+ QUOTENAME(OBJECT_NAME(object_id, database_id)) ObjDetails,
row_lock_wait_in_ms + page_lock_wait_in_ms Block_Wait_Time_in_ms
from sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL)
order by Block_Wait_Time_in_ms desc,ObjDetails desc

 

 

Read More

sys.dm_db_index_usage_stats

List all indexes of all tables

Top 5 SQL Server DMV for Index Analysis

 


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 Cached blocking history with sys.dm_db_index_operational_stats


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