sys.dm_db_xtp_nonclustered_index_stats – Day 76 – One DMV a Day

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, AS indName,
	delta_pages AS deltaP,
	internal_pages AS internalP,
	leaf_pages AS leafP,
	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

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.

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.

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


About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published.