sys.dm_db_xtp_nonclustered_index_stats (Transact-SQL)

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 name Data type Description
object_id int ID of the object.
xtp_object_id bigint ID of the memory-optimized table.
index_id int ID of the index.
delta_pages bigint The total number of delta pages for this index in the tree.
internal_pages bigint For internal use. The total number of internal pages for this index in the tree.
leaf_pages bigint The total number of leaf pages for this index in the tree.
outstanding_retired_nodes bigint For internal use. The total number of nodes for this index in the internal structures.
page_update_count bigint Cumulative number of operations updating a page in the index.
page_update_retry_count bigint Cumulative number of retries of an operation updating page in the index.
page_consolidation_count bigint Cumulative number of page consolidations in the index.
page_consolidation_retry_count bigint Cumulative number of retries of page consolidation operations.
page_split_count bigint Cumulative number of page split operations in the index.
page_split_retry_count bigint Cumulative number of retries of page split operations.
key_split_count bigint Cumulative number of key splits in the index.
key_split_retry_count bigint Cumulative number of retries of key split operations.
page_merge_count bigint Cumulative number of page merge operations in the index.
page_merge_retry_count bigint Cumulative number of retries of page merge operations.
key_merge_count bigint Cumulative number of key merge operations in the index.
key_merge_retry_count bigint Cumulative number of retries of key merge operations.

Permissions

Requires VIEW DATABASE STATE permission on the current database.

See Also

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