Skip to content

sys-dm-db-xtp-nonclustered-index-stats-transact-sql.md

Latest commit

 

History

History
69 lines (58 loc) · 4.88 KB

sys-dm-db-xtp-nonclustered-index-stats-transact-sql.md

File metadata and controls

69 lines (58 loc) · 4.88 KB
title sys.dm_db_xtp_nonclustered_index_stats (Transact-SQL)
description sys.dm_db_xtp_nonclustered_index_stats includes statistics about operations on nonclustered indexes in memory-optimized tables.
author rwestMSFT
ms.author randolphwest
ms.date 02/27/2023
ms.service sql
ms.subservice system-objects
ms.topic reference
f1_keywords
dm_db_xtp_nonclustered_index_stats_TSQL
dm_db_xtp_nonclustered_index_stats
sys.dm_db_xtp_nonclustered_index_stats_TSQL
sys.dm_db_xtp_nonclustered_index_stats
helpviewer_keywords
sys.dm_db_xtp_nonclustered_index_stats dynamic management view
dev_langs
TSQL
monikerRange >=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

sys.dm_db_xtp_nonclustered_index_stats (Transact-SQL)

[!INCLUDEsql-asdb-asdbmi]

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 [!INCLUDEinmemory] Internals for SQL Server 2016](../in-memory-oltp/sql-server-in-memory-oltp-internals-for-sql-server-2016.md), 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