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.
Related content
Повратне информације
https://aka.ms/ContentUserFeedback.
Стиже ускоро: Током 2024. године постепено ћемо укидати проблеме са услугом GitHub као механизам повратних информација за садржај и заменити га новим системом повратних информација. За додатне информације, погледајте:Пошаљите и прегледајте повратне информације за