All 7 Hash Indexes & Hash Join Operations questions
More in Hash Indexes & Hash Join Operations
What is a hash join, and how does SQL Server use it to combine two tables? What is a hash index, and how is it different from the more common B-tree index used for regular disk based tables? In what situations does SQL Server's query optimizer typically prefer a hash join over a nested loop join or a merge join? What are the differences in how a hash index and a memory optimized nonclustered index handle range queries and equality lookups? What happens when a hash join does not have enough memory available to build its hash table entirely in memory, and how does this affect performance? How would you troubleshoot a query that is unexpectedly using a slow hash join when a much faster index seek and nested loop join should be possible?
Advertisement
SQL Server › Hash Indexes & Hash Join Operations

How does the BUCKET_COUNT setting affect the performance of a hash index on a memory optimized table?

Intermediate

Answer

The bucket count determines how many hash buckets are available to distribute rows into, and choosing a bucket count too small compared to your actual row count causes too many rows to share the same bucket, which slows down lookups, while a reasonable rule of thumb is to set it to roughly one and a half to two times the expected number of unique values in the indexed column.
-- A bucket count too small for the actual data volume
-- causes many rows to collide in the same bucket, slowing lookups
ALTER TABLE SessionData
ALTER INDEX IX_SessionId REBUILD WITH (BUCKET_COUNT = 200000);
Real-world example A team notices lookups on their session table slowing down as data grew, and after increasing the bucket count to better match their actual row count, lookup performance returned to expected levels.

Common follow-ups: What happens if the bucket count is set much higher than actually needed?;How do you check the current bucket count and collision rate for an existing hash index?

In-Memory OLTP (Memory-Optimized Tables);Query Optimization & Plans
All 7 Hash Indexes & Hash Join Operations questions