Hello Geeks and welcome to the Day 5 of the long series to come in One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

As I have mentioned in yesterday’s blog, I will be explaining the most informative of all Index related DMVs. sys.dm_db_index_operational_stats is a DMV which is similar to sys.dm_db_index_usage_stats but give the details about operational stats than usage. To put it more clearly, the later tells you about how is your index used and sys.dm_db_index_operational_stats will tell you what operations are happening on the indexes. This is one of the most useful DMVs if you start using it regularly or collect this DMV data for troubleshooting performance related issues.

In this post I will try to show the most important columns in the DMV and how you can use it to interpret the information in the DMV. Similar to usage stats the stats in this DMV are also cumulative and are refreshed when your instance is restarted. So, as I always say, a DBA is as good as his information collected to troubleshoot or debug any issue.

To understand how to use this DMV I will first create a database, a table and a procedure to collect the output of sys.dm_db_index_operational_stats into a table. Later we will interpret the output of this table to derive to our results.

The result you will be interested are the counter values increase from first result set to the second and so on. The difference will give you the exact stats with the operation of inserts, updates and deletes that we are going to run in the below script. For the ease of reading I will put the output in a more readable format from the collected table.

We have collected all the Operational stats after each operation from sys.dm_db_index_operational_stats into Operational_stats_tbl. As I cannot put the screenshot of the table due to high number of columns I have pull the data into excel and got the differences for each operation for all counters.

leaf_insert_count 0 100000 0 0
leaf_delete_count 0 0 0 18574
leaf_update_count 0 0 50000 0
nonleaf_insert_count 0 1409 0 0
nonleaf_delete_count 0 0 0 266
nonleaf_update_count 0 0 0 0
row_lock_count 0 202816 6162 6161
row_lock_wait_count 0 0 0 0
row_lock_wait_in_ms 0 0 0 0
page_lock_count 0 101408 87 88
page_lock_wait_count 0 0 0 0
page_lock_wait_in_ms 0 0 0 0
page_latch_wait_count 0 3 0 0
page_latch_wait_in_ms 0 4 0 0
page_io_latch_wait_count 0 198 6 4
page_io_latch_wait_in_ms 0 218 140 51


The results are pretty self-explanatory and you can also see the wait times on latch and lock waits which you can further use in troubleshooting performance issues. The leaf pages counts are changes happened due to the operation in the leaf pages which is the actual data. Similarly the non leaf counts is the changes happened to non leaf level pages which are the index pages.

So Now you know the importance of sys.dm_db_index_operational_stats and why to collect this data in definite intervals. Tomorrow I will be covering another DMV. So, stay tuned. Till then

Happy Learning,

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook