In Parts 1 through 6 of this blog series the author discussed various aspects of SQL Server indices such as:
https://blog.sqlrx.com/2017/10/26/rx-for-demystifying-index-tuning-decisions-part-1/
https://blog.sqlrx.com/2017/11/02/rx-for-demystifying-index-tuning-decisions-part-2/
https://blog.sqlrx.com/2017/11/09/rx-for-demystifying-index-tuning-decisions-part-3/
https://blog.sqlrx.com/2017/11/16/rx-for-demystifying-index-tuning-decisions-part-4/
https://blog.sqlrx.com/2017/11/30/rx-for-demystifying-index-tuning-decisions-part-5/
https://blog.sqlrx.com/2017/11/30/rx-for-demystifying-index-tuning-decisions-part-6/
Part 7 contains a detailed discussion of the sys.dm_db_index_operational_stats DMF (and how to use it).
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
To identify overhead & contention caused by latching and locking
These values Indicate how many times Database Engine tried to acquire row and page locks (overhead)
These values Indicate whether lock contention exists on index or heap, and significance of contention
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
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.
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!
Session expired
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
[…] https://blog.sqlrx.com/2017/12/07/rx-for-demystifying-index-tuning-decisions-part-7/ […]