Include column that summarizes rows read in sys.dm_db_index_operational_stats - by Jason Strate

Status : 


Sign in
to vote
ID 605452 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 9/26/2010 10:55:42 PM
Access Restriction Public


The DMV sys.dm_db_index_operational_stats includes columns for operations on indexes that result in write operations (such as leaf_insert_count, leaf_delete_count, leaf_update_count, and leaf_ghost_count).  Since the operations occur for these at the row level, the counts tabulated on these columns can provide insight into the number of rows affected.  

The DMV also includes range_scan_count which counts the number of operations on the index.  This though does not include a count of the number of rows that were read in these operations.  

The read count would be useful to use in comparison to write counts as a measure of determining if the IO on the index is practical for the index.  For instance, if the database is writing to substantially more rows than are being read - the performance hit on the writes may be more important the benefit of the performance improvement for the reads.
Sign in to post a comment.
Posted by Microsoft on 4/1/2014 at 5:03 PM

Based on your scenario, the DMV already shows (a) delete, update, insert row count (b) row lock count. These counters should give you a pretty good idea on the usage of the index. For now, I am closing this. If you diagree, please feel free to re-activate with the rationale

Posted by Jason Strate on 11/8/2010 at 2:14 PM
sys.dm_db_index_usage_stats provides some useful information, but doesn't fully cover the scenario I am looking at. Part of the problem with sys.dm_db_index_usage_stats is that delete and inserts are included in seek operations. Also, a seek in sys.dm_db_index_usage_stats might return 1 row or it could return 10,000 rows. It only includes a tick in the counters if the index is included in a execution plan. It provides an acceptable 10,000 foot view of whether an index provides value.

What I'm looking for are statistics that can provide lower level information on the costs associated with maintaining the index. Comparing these costs with the usage of the index can be used to determine it's value.

This information can also help uncover bottlenecks or head off potential future performance issues. By tracking these stats over time a DBA can watch as tipping point thresholds are approached and plan ahead for addressing indexing on tables. For example, there will be a point where the average rows returned from seeks will abandon the seeks for scans.
Posted by Microsoft on 10/12/2010 at 10:39 AM
Jason, thanks for contacting Microsoft SQL Server team. I am wondering if sys.dm_db_index_usage_stats is more appropriate for the specific scenario you have mentioned? I agree that INSERT operation gets increasingly more expensive with numnber of indexes but for large tables, if the index is missing, the SQL Server will be forced to scan the full table. So the real measure is how many rows will need to be read if the index was missing. SQL Optimizer chooses indexes only when doing table scan is not cost effective. So I think index usage stats should be a very good indicator.

Please let us know what you think?