Applies To: SQL Server 2014, SQL Server 2016 Preview
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), SQL Database V12.
The object ID of parent table.
ID of the memory-optimized table.
The index ID.
The total number of hash buckets in the index.
The number of empty hash buckets in the index.
The average length of the row chains over all the hash buckets in the index.
The maximum length of the row chains in the hash buckets.
The in-memory OLTP object ID that corresponds to the memory-optimized table.
Requires VIEW DATABASE STATE permission on the server.