SQL SERVER – Identifying InMemory OLTP Hash Collisions

7 years ago

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.

I hope such queries will help you debug such usecases happening in your environment. Do let me know if you have been a fan of using InMemory OLTP with SQL Server in your applications. I am sure we can learn a lot from you over the same.

Reference: Pinal Dave (https://blog.sqlauthority.com)