Rx for Demystifying Index Tuning Decisions – Part 7

By Jeffry Schwartz | Missing Indexes

Dec 07


In Parts 1 through 6 of this blog series the author discussed various aspects of SQL Server indices such as:

  • Types of indices and their variations, e.g., clustered, nonclustered, filtered, and covering
  • Physical versus logical index access methods, e.g., seeks, scans, key/RID lookups, singleton lookups, and range scans
  • Why index tuning should begin with queries
  • Determining an Appropriate Strategy for Index Tuning
  • Determining Queries that Need Indices the Most
  • Capturing query performance information using Extended Events
  • Using query plans to determine relationships between queries and indices
  • Query Optimizer
  • Statistics and how they affect index usage and query performance
  • Consequences of too many indices
  • Overlapping Indices
  • Overview of Dynamic Management Functions and a detailed discussion of the sys.dm_db_index_usage_stats DMV








Part 7 contains a detailed discussion of the sys.dm_db_index_operational_stats DMF (and how to use it).

sys.dm_db_index_operational_stats DMF

The sys.dm_db_index_operational_stats DMF requires four parameters and returns one row per index. It provides additional information regarding how the seeks and scans from the sys.dm_db_index_usage_stats DMV are actually implemented within SQL Server. For example, this DMF records how many range and table scans or single record retrievals were performed against an index or heap. Note: although heaps are not indices, their activity is recorded by this DMF using an index ID of zero. Other important information regarding leaf and non-leaf level page and row lock and latch wait times is also provided. Finally, page split information for both the leaf and non-leaf levels is provided via this DMF as well.

Four parameters are required:

{ database_id | NULL | 0 | DEFAULT } (use db_id() for current db)

{ object_id | NULL | 0 | DEFAULT }

{ index_id | NULL | -1 | DEFAULT }

{ partition_number | NULL | 0 | DEFAULT }


Use NULL parameters to obtain information for all available entities for a given level, e.g., databases or tables. The following command will return information for all databases, tables, indices, and partitions that are attached to a particular SQL Server instance: select * from sys.dm_db_index_operational_stats (NULL, NULL, NULL, NULL).

Combining the outputs of sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats enable the analyst to determine whether the majority of usage seeks actually resulted index range scans or single record lookups. This combination can also be used to identify the approximate percentage of heap or clustered single record accesses that resulted from RID/Key lookups because these are usually performed in loops of single record accesses. Another excellent source of information involves row, page, page latch, and page I/O latch wait times because these can point to specific tables and indices (and indirectly, keys) that are involved in application delays. Finally, invaluable information involving page splits is also available via this DMF. Note: all of this information is available for both leaf and non-leaf levels. The lists below are provided to aid the reader in addressing specific performance problems.

To analyze common table or index partition access pattern use

  • leaf_insert_count
  • leaf_delete_count
  • leaf_update_count
  • leaf_ghost_count
  • range_scan_count
  • singleton_lookup_count

To identify overhead & contention caused by latching and locking

  • row_lock_count and page_lock_count

These values Indicate how many times Database Engine tried to acquire row and page locks (overhead)

  • row_lock_wait_in_ms and page_lock_wait_in_ms

These values Indicate whether lock contention exists on index or heap, and significance of contention

  • page_latch_wait_count and page_latch_wait_in_ms

These values Indicate whether latch contention exists on index or heap, and significance of contention

To analyze statistics of physical I/Os on an index or heap partition

  • page_io_latch_wait_count and page_io_latch_wait_in_ms

These values Indicate how many physical I/Os were issued to bring index or heap pages into memory and how much waiting was involved

Table 12 demonstrates the kind of report that is available using this data. The highlighted SQL Server tables in this example show that the ix_ci_RowNumEvent index is used almost exclusively for single record lookups, whereas the ix_RowNumEvent index is only ranged scanned. ix_CETRowNum and ix_Checksum are used similarly. This kind of report can be extended easily to include various wait types as well.


Table 12: Index Operational Stats Summary


The next and final article in this blog series will cover determining missing indices and their estimated impact if implemented as well as incorporating missing index recommendations, either into existing indices or as few indices as possible. Until then…good luck with your tuning efforts and please contact us with any questions.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

About the Author