sys.dm_db_xtp_nonclustered_index_stats (Transact-SQL)
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
The sys.dm_db_xtp_nonclustered_index_stats system dynamic management view includes statistics about operations on nonclustered indexes in memory-optimized tables. The result set 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.
For more information about memory-optimized indexes, see SQL Server In-Memory OLTP Internals for SQL Server 2016, page 20.
| 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.
Permissions for SQL Server 2022 and later
Requires VIEW DATABASE PERFORMANCE STATE permission on the database.