Three Usage Scenarios for sys.dm_db_index_operational_stats
There are several dynamic management objects (DMOs) that I use on an ongoing basis in performance tuning scenarios. However, I’ve lately gained an increased appreciation of the sys.dm_db_index_operational_stats DMO. This 44 column (as of SQL 2008) DMO returns data at a database-object-index-partition granularity and tracks an assortment of allocation, latching, locking and I/O related statistics.
Similar to sys.dm_db_index_physical_stats which you can use to track fragmentation, sys.dm_db_index_operational_stats requires input arguments in order to return a result set – expecting either a numeric value or NULL for database_id, object_id, index_id, and partition_id. If you designate NULL values for a parameter, it will be treated as “all inclusive” based on the specified scope. Also note that if you provide an invalid ID, it will be treated like a NULL, again returning results in an “all inclusive” manner based on the specified scope.
This blog post describes three usage scenarios where I’ve recently found sys.dm_db_index_operational_stats to have been very helpful. One key point to keep in mind is that these operational statistics are accumulated since the last SQL Server instance restart, and may not be representative of your workload statistics if the SQL Server instance has not been running for a significant period of time. So if you are looking to use the following techniques, make sure your representative workloads have been executed since the last restart.
Identifying Top Objects Associated with Lock Contention
Let’s say you are troubleshooting a report from the application team on perceived slow-down of an application. You use the Waits and Queues methodology, and your analysis reveals blocking as your primary bottleneck. If you have a significant number of database objects, you can use sys.dm_db_index_operational_stats to efficiently identify tables associated with a significant amount of blocking. Relevant columns from this DMV include row_lock_wait_count, row_lock_wait_in_ms, page_lock_wait_count, and page_lock_wait_in_ms. (On a side note – you can also identify latching waits through this DMV via the page_latch_wait_count, page_latch_wait_in_ms, page_io_latch_wait_count, and page_io_latch_wait_in_ms columns.)
The following query demonstrates identifying the top 3 objects associated with waits on page locks:
SELECT TOP 3
OBJECT_NAME(o.object_id, o.database_id) object_nm,
o.index_id,
partition_number,
page_lock_wait_count,
page_lock_wait_in_ms,
case when mid.database_id is null then 'N' else 'Y' end as missing_index_identified
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) o
LEFT OUTER JOIN (SELECT DISTINCT database_id, object_id
FROM sys.dm_db_missing_index_details) as mid
ON mid.database_id = o.database_id and mid.object_id = o.object_id
ORDER BY page_lock_wait_count DESC
* There is an extra feature added to this query, compliments Denzil Ribeiro, where he suggested adding a lookup against sys.dm_db_missing_index_details to validate if there were any missing indexes identified with the high page lock wait indexes. This serves as a little extra information that may point you to a means of reducing locking contention.
Lock Escalations
You can use sys.dm_db_index_operational_stats to track how many attempts were made to escalate to table locks (index_lock_promotion_attempt_count), as well as how many times escalations actually succeeded (index_lock_promotion_count). The following query shows the top three objects with the highest number of escalations:
SELECT TOP 3
OBJECT_NAME(object_id, database_id) object_nm,
index_id,
partition_number,
index_lock_promotion_attempt_count,
index_lock_promotion_count
FROM sys.dm_db_index_operational_stats
(db_id(), NULL, NULL, NULL)
ORDER BY index_lock_promotion_count DESC
Page Split Tracking
Excessive page splitting can have a significant effect on performance. The following query identifies the top 10 objects involved with page splits (ordering by leaf_allocation_count and referencing both the leaf_allocation_count and nonleaf_allocation_count columns). The leaf_allocation_count column represents page splits at the leaf and the nonleaf_allocation_count represents splits at the non-leaf levels of an index:
SELECT TOP 10
OBJECT_NAME(object_id, database_id) object_nm,
index_id,
partition_number,
leaf_allocation_count,
nonleaf_allocation_count
FROM sys.dm_db_index_operational_stats
(db_id(), NULL, NULL, NULL)
ORDER BY leaf_allocation_count DESC
Summary
There are more usage scenarios to be generated from this DMO; however these are the three areas that I’ve utilized sys.dm_db_index_operational_stats most recently (and with a positive outcome). It is an excellent way to clearly identify objects involved with specific contention issues, including blocking, lock escalation, and page split issues.
One final point related to the overall topic of performance tuning, if you haven’t already done so, check out the “Troubleshooting Performance Problems in SQL Server 2008” white paper, a highly recommended read.
Joe Sack, Dedicated Support Engineer - SQL Server
Comments
Anonymous
June 10, 2009
I just posted a new entry on the SQL Server Premier Field Engineer Blog on Three Usage Scenarios forAnonymous
May 30, 2015
Amazing post! I feel this is a very underutilized information for SQL DBA. If they start using this, they can tune the queries even better!