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.
SELECT OBJECT_NAME(xnis.object_id) AS tableName, i.name AS indName, delta_pages AS deltaP, internal_pages AS internalP, leaf_pages AS leafP, outstanding_retired_nodes, page_update_count AS pageUp, page_update_retry_count AS pageUpRe, page_consolidation_count AS pageCon, page_consolidation_retry_count AS pageConRe, page_split_count AS pageSpl, page_split_retry_count AS pageSplRe, key_split_count AS keySpl, key_split_retry_count AS keySplRe, page_merge_count AS pageMrg, page_merge_retry_count AS pageMrgRe, key_merge_count AS keyMrg, key_merge_retry_count AS keyMrgRe FROM sys.dm_db_xtp_nonclustered_index_stats xnis INNER JOIN sys.indexes i ON xnis.object_id = i.object_id AND xnis.index_id = i.index_id
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.
UPDATE inMem2_tbl SET col2 = col1 + 1
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.
Tomorrow I will be covering another In-Memory optimized tables related DMV. So stay tuned. Till then
Happy Learning,
Manu
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook
