SQL Server 内存中 OLTP 哈希索引设置 BUCKET_COUNT 值

Question

在内存优化表中创建哈希索引时，我可以设置变量BUCKET_COUNT的值

CREATE TABLE [Table1] (
[Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY NONCLUSTERED HASH 
                                WITH (BUCKET_COUNT = 1000000),
[Name] NVARCHAR(100) NOT NULL
) 
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

该变量的最佳值是多少？

Answer 1

根据Determining the Correct Bucket Count for Hash Indexes :

In most cases the bucket count should be between 1 and 2 times thenumber of distinct values in the index key. If the index key containsa lot of duplicate values, on average there are more than 10 rows foreach index key value, use a nonclustered index instead

You may not always be able to predict how many values a particular index key may have or will have. Performance should be acceptable if the BUCKET_COUNT value is within 5 times of the actual number of key values.

还有:

Primary Key and Unique Indexes

Because the primary key index is unique, the number of distinct values in the key corresponds to the number of rows in the table.