It is important to understand the impact of InMemory OLTP Hash Collisions on the performance of your SQL Server. In a previous blog post, we discussed how these collisions can occur and the performance overhead they can cause. If you haven’t read that post yet, I recommend checking it out before proceeding with this one.
In that post, we saw how extreme the performance can be affected when the configurations are skewed or misconfigured. After receiving a request from one of our readers, I decided to enhance the blog by providing a way to identify such settings.
One way to identify the number of unique index key values as per the configuration is by using the following query:
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 of this query will show you the bucket count and row count for each table. By comparing the bucket counts, you can identify any differences that may indicate a problem with the configuration.
Another way to analyze the situation is by looking at the average number of rows collected in each of the buckets. You can use the following query for this:
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'));
The output of this query will show you the average chain length for each table. If you notice a high average chain length, such as in the case of ‘InMem_Data2’ where the average chain length is 976, it indicates that Hash Collisions are happening in the system.
By using these queries, you can easily identify and debug any Hash Collisions happening in your environment. If you have been using InMemory OLTP with SQL Server in your applications, I would love to hear about your experiences and any insights you may have.
Stay tuned for more SQL Server tips and tricks!