Export (0) Print
Expand All

sys.dm_db_xtp_hash_index_stats (Transact-SQL)

SQL Server 2014

These statistics are useful for understanding and tuning the bucket counts. It can also be used to detect cases where the index key has many duplicates.

A large average chain length indicates that many rows are hashed to the same bucket. This could happen because:

  • If the number of empty buckets is low or the average and maximum chain lengths are similar, it is likely that the total bucket count is too low. This causes many different index keys to hash to the same bucket.

  • If the number of empty buckets is high or the maximum chain length is high relative to the average chain length, it is likely that there are many rows with duplicate index key values or there is a skew in the key values. All rows with the same index key value hash to the same bucket, hence there is a long chain length in that bucket.

Long chain lengths can significantly impact the performance of all DML operations on individual rows, including SELECT and INSERT. Short chain lengths along with a high empty bucket count are in indication of a bucket_count that is too high. This decreases the performance of index scans.

sys.dm_db_xtp_hash_index_stats scans the entire table. So, if there are large tables in your database, sys.dm_db_xtp_hash_index_stats may take a long time run.

For more information, see Determining the Correct Bucket Count for Hash Indexes.

Applies to: SQL Server (SQL Server 2014 through current version).

Column name

Type

Description

object_id

int

The object ID of parent table.

index_id

int

The index ID.

total_bucket_count

bigint

The total number of hash buckets in the index.

empty_bucket_count

bigint

The number of empty hash buckets in the index.

avg_chain_length

bigint

The average length of the row chains over all the hash buckets in the index.

max_chain_length

bigint

The maximum length of the row chains in the hash buckets.

Requires VIEW DATABASE STATE permission on the server.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft