Hash Index Bucket Count for Memory-Optimized Tables

The table is in memory, but its equality lookup still does too much work. The hash index bucket count helps explain that surprise. Count distinct keys before guessing how many buckets to allocate.

Match the Index to the Lookup

I start with the predicate when reviewing a memory-optimized index. A hash index serves equality comparisons on its full key. It doesn't provide an ordered path for a range search.

Each key hashes to a bucket. Keys sharing a bucket form a chain of entries to inspect. Too few buckets increase collisions between distinct keys.

Rows with duplicate key values also share a bucket. A larger array cannot separate equal keys. That distinction matters before spending more memory on buckets.

A memory-optimized nonclustered range index supports more flexible access. It handles inequalities and ordered traversal better than a hash index. Choose it when the workload needs those operations.

The examples need a SQL Server instance supporting In-Memory OLTP and an appropriate database setup. Use a disposable database. This is a feature prerequisite, not an instruction to convert a production table.

Prepare a Separate Memory-Optimized Database

The setup below creates a dedicated test database and its memory-optimized filegroup. Replace the placeholder container path with a new path under an existing parent directory. The SQL Server service needs permission there.

The container itself must be suitable for creation by SQL Server. Don't point it at an existing database file. Check the paths before running the setup.

The sample table uses SCHEMA_ONLY durability. Its rows won't survive a database restart. That keeps this experiment separate from a durable-data design discussion.

The small initial bucket allocation is intentional. It gives you a starting condition to inspect. The sample generator supplies input rows rather than an asserted observed population.

Run the setup once and keep its database isolated from application connections. The hash index is named so the later rebuild can target it. Don't copy that name into another database without inspecting its definition.

CREATE DATABASE [HashBucketDemo];
GO
ALTER DATABASE [HashBucketDemo] ADD FILEGROUP [HashBucketMemory] CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE [HashBucketDemo] ADD FILE
(NAME = N'HashBucketContainer', FILENAME = N'C:\SqlData\HashBucketContainer-new')
TO FILEGROUP [HashBucketMemory];
GO
USE [HashBucketDemo];
GO
CREATE TABLE dbo.HashItemsDemo
(
    ItemId int NOT NULL,
    GroupId int NOT NULL,
    ItemName nvarchar(80) NOT NULL,
    CONSTRAINT PK_HashItemsDemo PRIMARY KEY NONCLUSTERED HASH(ItemId)
        WITH (BUCKET_COUNT = 16),
    INDEX IX_HashItemsDemo_Group NONCLUSTERED(GroupId)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
INSERT dbo.HashItemsDemo(ItemId, GroupId, ItemName)
SELECT TOP (2000) ROW_NUMBER() OVER (ORDER BY object_id), ABS(object_id) % 10, N'Sample item'
FROM sys.all_objects;

Size the Bucket Count From Distinct Values

A useful starting allocation is one to two buckets per distinct key. For a unique ItemId, each row supplies a distinct value. For a repeated GroupId, the distinct count is smaller than the row count.

The count below measures both populations from the test table. Use those returned values to plan the allocation. Don't size a repeated-key index from total rows without considering duplicates.

SQL Server rounds the requested bucket count to a supported power of two. Read the actual allocation afterward. The requested number and observed total aren't always identical.

Allow for realistic growth in the key population. Rebuilding an array after every small growth step isn't a maintenance strategy. Also avoid allocating a huge mostly empty array without evidence.

Bucket count is a capacity choice with a memory cost. The array itself consumes memory even when many buckets are empty. A larger value isn't automatically a faster index.

SELECT COUNT_BIG(*) AS TotalRows,
       COUNT(DISTINCT ItemId) AS DistinctItemKeys,
       COUNT(DISTINCT GroupId) AS DistinctGroupKeys
FROM dbo.HashItemsDemo;

Read Empty Buckets and Chain Length Together

sys.dm_db_xtp_hash_index_stats reports total and empty buckets. It also reports average and maximum chain length. Join to sys.indexes to identify the index being inspected.

Few empty buckets alongside long chains suggests too many distinct keys competing for the allocation. Many empty buckets with a long exceptional chain suggests duplicates or skew. The combination guides the next investigation.

A large maximum compared with the average deserves attention. One highly repeated key can dominate that chain. Increasing buckets doesn't divide those equal-key rows into different buckets.

The DMV scans the memory-optimized table to collect this information. It isn't a free counter lookup on a large object. Schedule the inspection appropriately and avoid unnecessary repeated polling.

On SQL Server 2022 and later, the inspection requires VIEW DATABASE PERFORMANCE STATE. Earlier versions use VIEW DATABASE STATE. Check the permission requirement for the actual server.

SELECT OBJECT_NAME(h.object_id) AS TableName, i.name AS IndexName,
       h.total_bucket_count, h.empty_bucket_count,
       100.0 * h.empty_bucket_count / NULLIF(h.total_bucket_count, 0) AS EmptyBucketPercentage,
       h.avg_chain_length, h.max_chain_length
FROM sys.dm_db_xtp_hash_index_stats AS h
JOIN sys.indexes AS i ON i.object_id = h.object_id AND i.index_id = h.index_id
WHERE h.object_id = OBJECT_ID(N'dbo.HashItemsDemo');

Rebuild the Test Index With a New Bucket Count

The following command changes the sample hash allocation. The requested value is a test input. Choose your production value from its distinct population and growth plan.

Collect the DMV output again after rebuilding. Compare empty buckets and chain lengths with the earlier result. Also test the actual equality workload before calling the change beneficial.

On my test copy, the 16-bucket index showed no empty buckets and chains well over a hundred entries long. After the rebuild, most buckets held one entry, and more than half stayed empty. That is the trade in one picture: shorter chains, more idle memory.

ALTER TABLE changes the table's index structure. Rehearse its resource demand and operational effects before scheduling it on a busy database. In-memory doesn't mean maintenance has no cost.

I keep the row population stable during that comparison. Otherwise the before and after chain statistics describe different tests. Capture both the counts and index definition with the result.

The range index remains separate from this rebuild. Changing the hash array doesn't turn it into an ordered index. Keep the access capabilities distinct.

ALTER TABLE dbo.HashItemsDemo
ALTER INDEX PK_HashItemsDemo REBUILD WITH (BUCKET_COUNT = 4096);
SELECT ItemId, ItemName FROM dbo.HashItemsDemo WHERE ItemId = 100;
SELECT ItemId, GroupId FROM dbo.HashItemsDemo WHERE GroupId BETWEEN 2 AND 4;

Change the Index Type When the Question Changes

A composite hash key needs equality conditions covering all key columns. Searching only the first key column doesn't receive the same hash lookup benefit. A range index can support leading-key access patterns differently.

A repeated status value is also a poor candidate for assuming short unique-key chains. Count its duplicates explicitly. Read the whole workload rather than choosing hash for every in-memory table.

What does the application request besides an exact identifier lookup? Include ranges, ordering and partial keys in the review. Those operations determine whether another index is needed.

I choose the index type before refining its allocation. Otherwise the team spends time tuning an allocation for the wrong access method. More bins don't turn a lookup into a sorted shelf.

Bucket count deserves measured inspection after deployment too. Growth and skew change the original assumptions. Recheck distinct keys and chains when the workload changes materially.

Related reading on this blog: Identifying InMemory OLTP Hash Collisions and SQL Server: InMemory OLTP Hash Collisions Performance Overhead.

A hash allocation is not a substitute for index design, it is capacity for a particular equality lookup.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.