sys.dm_db_xtp_nonclustered_index_stats (Transact-SQL)


Updated: August 29, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2014)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

sys.dm_db_xtp_nonclustered_index_stats includes statistics about operations on nonclustered indexes in memory-optimized tables. sys.dm_db_xtp_nonclustered_index_stats contains one row for each nonclustered index on a memory-optimized table in the current database.

The statistics reflected in sys.dm_db_xtp_nonclustered_index_stats are collected when the in-memory index structure is created. In-memory index structures are recreated on database restart.

Use sys.dm_db_xtp_nonclustered_index_stats to understand and monitor index activity during DML operations and when a database is brought online. When a database with a memory-optimized table is restarted, the index is built by inserting one row at a time into memory. The count of page splits, merges, and consolidation can help you understand the work done to build the index when a database is brought online. You can also look at these counts before and after a series of DML operations.

Large numbers of retries are indicative of concurrency issues; call Microsoft Support.

For more information about memory-optimized, nonclustered indexes, see SQL Server In-Memory OLTP Internals Overview, page 17.

Applies to: SQL Server (SQL Server 2014 through current version).
Column nameData typeDescription
object_idintID of the object.
xtp_object_idbigintID of the memory-optimized table.
index_idintID of the index.
delta_pagesbigintThe total number of delta pages for this index in the tree.
internal_pagesbigintFor internal use. The total number of internal pages for this index in the tree.
leaf_pagesbigintThe total number of leaf pages for this index in the tree.
outstanding_retired_nodesbigintFor internal use. The total number of nodes for this index in the internal structures.
page_update_countbigintCumulative number of operations updating a page in the index.
page_update_retry_countbigintCumulative number of retries of an operation updating page in the index.
page_consolidation_countbigintCumulative number of page consolidations in the index.
page_consolidation_retry_countbigintCumulative number of retries of page consolidation operations.
page_split_countbigintCumulative number of page split operations in the index.
page_split_retry_countbigintCumulative number of retries of page split operations.
key_split_countbigintCumulative number of key splits in the index.
key_split_retry_countbigintCumulative number of retries of key split operations.
page_merge_countbigintCumulative number of page merge operations in the index.
page_merge_retry_countbigintCumulative number of retries of page merge operations.
key_merge_countbigintCumulative number of key merge operations in the index.
key_merge_retry_countbigintCumulative number of retries of key merge operations.

Requires VIEW DATABASE STATE permission on the current database.

Memory-Optimized Table Dynamic Management Views (Transact-SQL)

Community Additions