Export (0) Print
Expand All

sys.dm_db_xtp_nonclustered_index_stats (Transact-SQL)

SQL Server 2014

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), SQL Database Update (preview).

Column name

Data type

Description

object_id

int

ID of the object.

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.

Requires VIEW DATABASE STATE permission on the current database.

Community Additions

ADD
Show:
© 2014 Microsoft