Hello Geeks and welcome to the Day 76 of the long series of 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.

Yesterday we have seen how to check the In-Memory tables’ memory usage of data and index using sys.dm_db_xtp_table_memory_stats. Today I will get back to the index related DMVs of memory optimized tables. I will cover sys.dm_db_xtp_nonclustered_index_stats. By name we can clearly say this DMV provides stats of a nonclustered index.

To see the use of sys.dm_db_xtp_nonclustered_index_stats we need to understand when operations happens on nonclustered index. We know that the In-Memory tables are rebuilt when a database is brought online. Each index is built during that time. The stats immediately collected after the start up is the cost to rebuild the index. So my initial stats look like this after the start up.

sys.dm_db_xtp_nonclustered_index_stats 1

All the columns are self-explanatory. Each column indicates either the count or retry count of update, consolidation, split or merge operation on index pages or keys. As these values are cumulative a delta of this results should be used when considering stats for a single or set of DML operations. So let us run an update on the table and observe the result from sys.dm_db_xtp_nonclustered_index_stats.

Now let us see the output from sys.dm_db_xtp_nonclustered_index_stats. The delta of this result from the initial output is the operations count happened due to the update DML run on the table.

sys.dm_db_xtp_nonclustered_index_stats 2

Tomorrow I will be covering another In-Memory optimized tables related 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