Export (0) Print
Expand All

sys.dm_db_xtp_hash_index_stats (Transact-SQL)


Updated: June 10, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2014)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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.

Column nameTypeDescription
object_idintThe object ID of parent table.
xtp_object_idbigintID of the memory-optimized table.
index_idintThe index ID.
total_bucket_countbigintThe total number of hash buckets in the index.
empty_bucket_countbigintThe number of empty hash buckets in the index.
avg_chain_lengthbigintThe average length of the row chains over all the hash buckets in the index.
max_chain_lengthbigintThe maximum length of the row chains in the hash buckets.
xtp_object_idbigintThe in-memory OLTP object ID that corresponds to the memory-optimized table.

Requires VIEW DATABASE STATE permission on the server.

Memory-Optimized Table Dynamic Management Views (Transact-SQL)

Community Additions

© 2016 Microsoft