It is about a few weeks back that I wrote about how InMemory OLTP Hash Collisions can happen and how it can affect performance. Here is a recap of that post for your reference and do check the same before proceeding reading this blog post. SQL Server – InMemory OLTP Hash Collisions Performance Overhead
InMemory OLTP Hash Collisions
In that post, you can see how extreme the performance can be when the configurations are skewed badly or misconfigured. I got a request from one of the readers to enhance this blog to identify such settings done. Here is the first of the DMVs that I would like to share. Below query gives you the number of unique index key values as per the configuration.
SELECT 'InMem_Data1' AS 'table_name', (SELECT [bucket_count] FROM sys.hash_indexes WHERE object_id = OBJECT_ID('InMem_Data1') AND name LIKE 'PK_%') AS 'pk_bucket_count', COUNT(*) AS row_count FROM dbo.InMem_Data1 UNION SELECT 'InMem_Data2' AS 'table_name', (SELECT [bucket_count] FROM sys.hash_indexes WHERE object_id = OBJECT_ID('InMem_Data2') AND name LIKE 'PK_%') AS 'pk_bucket_count', COUNT(*) AS row_count FROM dbo.InMem_Data2;
The output in this statement would be like below:
Now, as per this, we can see the bucket count we had given for the two tables are different. Obviously this shows the difference in the bucket counts to the actual row counts.
Even a better way to look at the same would be to see the average number of rows collected in each of the buckets. This can be got from the below query:
SELECT OBJECT_NAME(hi.object_id) AS 'object_name', i.name AS 'index_name', hi.total_bucket_count, hi.empty_bucket_count, FLOOR((CAST(empty_bucket_count AS float)/total_bucket_count) * 100) AS 'empty_bucket_percent', hi.avg_chain_length, hi.max_chain_length FROM sys.dm_db_xtp_hash_index_stats AS hi INNER JOIN sys.indexes AS i ON hi.object_id = i.object_id AND hi.index_id = i.index_id WHERE hi.object_id IN (OBJECT_ID('dbo.InMem_Data1'), OBJECT_ID('dbo.InMem_Data2')) GO
The output of this shows that in the case of table 2 (InMem_Data2), the average chain length is 976. This means we have an average of 976 rows in each of the buckets. This is a typical sign for Hash Collisions happening in the system.