次の方法で共有

ハッシュ インデックスの正しいバケット数の決定

メモリ最適化テーブルを作成するときは、 BUCKET_COUNT パラメーターの値を指定する必要があります。 このトピックでは、 BUCKET_COUNT パラメーターの適切な値を決定するための推奨事項を示します。 正しいバケット数を特定できない場合は、代わりに非クラスター化インデックスを使用してください。 不適切な BUCKET_COUNT 値 (特に低すぎる値) は、ワークロードのパフォーマンスとデータベースの復旧時間に大きな影響を与える可能性があります。 バケット数を過大評価することをお勧めします。

キーが同じバケットにハッシュされ、そのバケットのチェーンが増加するため、インデックス キーが重複すると、ハッシュ インデックスのパフォーマンスが低下する可能性があります。

非クラスター化ハッシュ インデックスの詳細については、「Memory-Optimized テーブルでインデックスを使用するためのハッシュ インデックスとガイドライン」を参照してください。

メモリ最適化テーブルのハッシュ インデックスごとに 1 つのハッシュ テーブルが割り当てられます。 インデックスに割り当てられるハッシュ テーブルのサイズは、CREATE TABLE (Transact-SQL) または CREATE TYPE (Transact-SQL) のBUCKET_COUNT パラメーターによって指定されます。 バケット数は内部的に 2 の次の累乗に切り上げられます。 たとえば、バケット数を 300,000 に指定すると、実際のバケット数は 524,288 になります。

バケット数に関する記事とビデオへのリンクについては、「 ハッシュ インデックス (In-Memory OLTP) の適切なバケット数を決定する方法」を参照してください。

推奨事項

ほとんどの場合、バケット数はインデックス キー内の個別の値の数の 1 ~ 2 倍にする必要があります。 インデックス キーに多数の重複する値が含まれている場合、インデックス キー値ごとに平均で 10 行を超える場合は、代わりに非クラスター化インデックスを使用します

特定のインデックス キーに含まれる値または持つ値の数を常に予測できるとは限りません。 BUCKET_COUNT値がキー値の実際の数の 5 倍以内の場合は、パフォーマンスを許容できる必要があります。

既存のデータ内の一意のインデックス キーの数を確認するには、次の例のようなクエリを使用します。

主キーと一意のインデックス

主キー インデックスは一意であるため、キー内の個別の値の数はテーブル内の行数に対応します。 AdventureWorks データベースの Sales.SalesOrderDetail テーブルの主キー (SalesOrderID、SalesOrderDetailID) の例については、次のクエリを実行して、テーブル内の行数に対応する個別の主キー値の数を計算します。

SELECT COUNT(*) AS [row count]   
FROM Sales.SalesOrderDetail

このクエリでは、121,317 の行数が表示されます。 行数が大幅に変更されない場合は、バケット数 240,000 を使用します。 テーブル内の販売注文の数が 4 倍になると予想される場合は、バケット数 480,000 を使用します。

一意でないインデックス

(SpecialOfferID、ProductID) の複数列インデックスなど、他のインデックスの場合は、次のクエリを発行して、一意のインデックス キー値の数を確認します。

SELECT COUNT(*) AS [SpecialOfferID_ProductID index key count]  
FROM   
   (SELECT DISTINCT SpecialOfferID, ProductID   
    FROM Sales.SalesOrderDetail) t

このクエリは、非クラスター化ハッシュ インデックスの代わりに非クラスター化インデックスを使用する必要があることを示す 484 のインデックス キー数 (SpecialOfferID、ProductID) を返します。

重複の数の決定

インデックス キー値の重複値の平均数を決定するには、行の合計数を一意のインデックス キーの数で除算します。

インデックスの例 (SpecialOfferID、ProductID) の場合、121317 / 484 = 251 になります。 つまり、インデックス キーの値の平均は 251 であるため、非クラスター化インデックスである必要があります。

バケット数のトラブルシューティング

メモリ最適化テーブルのバケット数の問題をトラブルシューティングするには、 sys.dm_db_xtp_hash_index_stats (Transact-SQL) を使用して、空のバケットと行チェーンの長さに関する統計情報を取得します。 次のクエリを使用して、現在のデータベース内のすべてのハッシュ インデックスに関する統計情報を取得できます。 データベースに大きなテーブルがある場合、クエリの実行には数分かかることがあります。

SELECT   
   object_name(hs.object_id) AS 'object name',   
   i.name as 'index name',   
   hs.total_bucket_count,  
   hs.empty_bucket_count,  
   floor((cast(empty_bucket_count as float)/total_bucket_count) * 100) AS 'empty_bucket_percent',  
   hs.avg_chain_length,   
   hs.max_chain_length  
FROM sys.dm_db_xtp_hash_index_stats AS hs   
   JOIN sys.indexes AS i   
   ON hs.object_id=i.object_id AND hs.index_id=i.index_id

ハッシュ インデックスの正常性の 2 つの主要なインジケーターは次のとおりです。

empty_bucket_percent
empty_bucket_percent は、ハッシュ インデックス内の空のバケットの数を示します。

empty_bucket_percentが 10% 未満の場合、バケット数が少なすぎる可能性があります。 理想的には、 empty_bucket_percent は 33% 以上である必要があります。 バケット数がインデックス キー値の数と一致する場合、ハッシュ分散のため、バケットの約 1/3 が空になります。

平均チェーン長
avg_chain_length は、ハッシュ バケット内の行チェーンの平均長さを示します。

avg_chain_lengthが 10 より大きく、empty_bucket_percentが 10% を超える場合は、多くの重複するインデックス キー値があり、非クラスター化インデックスの方が適している可能性があります。 平均チェーン長 1 が理想的です。

チェーンの長さに影響を与える要因は 2 つあります。

  1. 重複;重複するすべての行は、ハッシュ インデックス内の同じチェーンの一部です。

  2. 複数のキー値が同じバケットにマップされます。 バケット数が少ないほど、複数の値がマップされるバケットが多くなります。

例として、次の表とスクリプトを考えて、サンプル行を表に挿入します。

CREATE TABLE [Sales].[SalesOrderHeader_test]  
(  
   [SalesOrderID] [uniqueidentifier] NOT NULL DEFAULT (newid()),  
   [OrderSequence] int NOT NULL,  
   [OrderDate] [datetime2](7) NOT NULL,  
   [Status] [tinyint] NOT NULL,  
  
PRIMARY KEY NONCLUSTERED HASH ([SalesOrderID]) WITH ( BUCKET_COUNT = 262144 ),  
INDEX IX_OrderSequence HASH (OrderSequence) WITH ( BUCKET_COUNT = 20000),  
INDEX IX_Status HASH ([Status]) WITH ( BUCKET_COUNT = 8),  
INDEX IX_OrderDate NONCLUSTERED ([OrderDate] ASC),  
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )  
GO  
  
DECLARE @i int = 0  
BEGIN TRAN  
WHILE @i < 262144  
BEGIN  
   INSERT Sales.SalesOrderHeader_test (OrderSequence, OrderDate, [Status]) VALUES (@i, sysdatetime(), @i % 8)  
   SET @i += 1  
END  
COMMIT  
GO

このスクリプトでは、テーブルに 262,144 行が挿入されます。 主キー インデックスと IX_OrderSequence に一意の値が挿入されます。 インデックス IX_Statusに多数の重複する値が挿入されます。スクリプトでは 8 つの個別の値のみが生成されます。

BUCKET_COUNTトラブルシューティング クエリの出力は次のとおりです。

インデックス名 total_bucket_count 空のバケット数 empty_bucket_percent 平均チェーン長 max_chain_length
IX_Status 8 4 50 65536 65536
IX_OrderSequence 32768 13 0 8 26
PK_SalesOrd_B14003C3F8FB3364 262144 96319 36 1 8

このテーブルの 3 つのハッシュ インデックスについて考えてみましょう。

  • IX_Status: バケットの 50% が空です。これは適切です。 ただし、平均チェーン長は非常に長くなります (65,536)。 これは、重複する値の数が多い場合を示します。 そのため、この場合、非クラスター化ハッシュ インデックスの使用は適切ではありません。 代わりに、非クラスター化インデックスを使用する必要があります。

  • IX_OrderSequence: バケットの 0パーセントが空であり、これは低すぎます。 さらに、平均チェーン長は 8 です。 このインデックスの値は一意であり、平均で 8 つの値が各バケットにマップされていることを意味します。 バケット数を増やす必要があります。 インデックス キーには 262,144 個の一意の値が含まれるので、バケット数は少なくとも 262,144 である必要があります。 将来の成長が予想される場合は、その数を大きくする必要があります。

  • 主キー インデックス (PK__SalesOrder...): バケットの 36% が空です。これは適切です。 また平均チェーン長は1で、これも良い。 変更は必要ありません。

メモリ最適化ハッシュ インデックスに関する問題のトラブルシューティングの詳細については、「Memory-Optimized ハッシュ インデックスに関する一般的なパフォーマンスの問題のトラブルシューティング」を参照してください。

さらなる最適化に関する詳細な考慮事項

このセクションでは、バケット数を最適化するためのその他の考慮事項について説明します。

ハッシュ インデックスの最適なパフォーマンスを実現するには、ハッシュ テーブルに割り当てられたメモリの量と、インデックス キー内の個別の値の数のバランスを取ります。 また、ポイント参照とテーブル スキャンのパフォーマンスにはバランスがあります。

  • バケット数の値が大きいほど、インデックスに空のバケットが多くなります。 これは、各バケットがテーブル スキャンの一部としてスキャンされるため、メモリ使用量 (バケットあたり 8 バイト) とテーブル スキャンのパフォーマンスに影響します。

  • バケット数が少ないほど、1 つのバケットに割り当てられる値が多くなります。 これにより、ポイント参照と挿入のパフォーマンスが低下します。SQL Server では、検索述語で指定された値を検索するために、1 つのバケット内の複数の値を走査する必要がある場合があるためです。

バケット数が一意のインデックス キーの数よりも大幅に少なければ、多くの値が各バケットにマップされます。 これにより、ほとんどの DML 操作 、特にポイント参照 (個々のインデックス キーの参照) と挿入操作のパフォーマンスが低下します。 たとえば、SELECT クエリのパフォーマンスが低く、WHERE 句のインデックス キー列と一致する等値述語を持つ UPDATE および DELETE 操作が表示される場合があります。 バケット数が少ない場合は、データベースの起動時にインデックスが再作成されるため、データベースの復旧時間にも影響します。

インデックス キー値の重複

値が重複すると、ハッシュ競合のパフォーマンスへの影響が大きくなる可能性があります。 通常、各インデックス キーに重複する数が少ない場合、これは問題になりません。 ただし、一意のインデックス キーの数とテーブル内の行数の不一致が非常に大きくなる場合、これは問題になる可能性があります。

同じインデックス キーを持つすべての行は、同じ重複チェーンに入ります。 ハッシュの競合が原因で複数のインデックス キーが同じバケット内にある場合、インデックス スキャナーは常に、2 番目の値に対応する最初の行を見つける前に、最初の値の完全な重複チェーンをスキャンする必要があります。 また、キーが重複することで、ガベージコレクションが行を特定しづらくなります。 たとえば、キーに 1,000 個の重複があり、いずれかの行が削除された場合、ガベージ コレクターは 1,000 個の重複のチェーンをスキャンして、インデックスから行のリンクを解除する必要があります。 これは、ガベージ コレクターがすべてのインデックスからのリンクを解除する必要があるため、削除を検出したクエリで行を検索するために、より効率的なインデックス (主キー インデックス) を使用した場合でも当てはまります。

ハッシュ インデックスの場合、インデックス キー値の重複による作業を減らす方法は 2 つあります。

  • 代わりに非クラスター化インデックスを使用してください。 アプリケーションに変更を加えずにインデックス キーに列を追加することで、重複を減らすことができます。

  • インデックスのバケット数が非常に多い値を指定します。 たとえば、一意のインデックス キーの数の 20 ~ 100 倍です。 これにより、ハッシュの競合が軽減されます。

小さいテーブル

テーブルのサイズが小さい場合、インデックスのサイズはデータベース全体のサイズと比較して小さくなり、通常はメモリ使用率は問題になりません。

次に、必要なパフォーマンスの種類に基づいて選択する必要があります。

  • インデックスに対するパフォーマンスクリティカルな操作が主にポイント参照や挿入操作である場合は、ハッシュ競合の可能性を減らすためにバケット数を増やすことが適切です。 行数の 3 倍以上が最適なオプションです。

  • 完全なインデックス スキャンがパフォーマンスクリティカルな主要な操作である場合は、インデックス キー値の実際の数に近いバケット数を使用します。

ビッグ テーブル

大きなテーブルの場合、メモリ使用率が問題になる可能性があります。 たとえば、4 つのハッシュ インデックスを持つ 2 億 5,000 万行のテーブルでは、それぞれバケット数が 10 億であるため、ハッシュ テーブルのオーバーヘッドは 4 インデックス * 10 億バケット * 8 バイト = 32 ギガバイトのメモリ使用率です。 インデックスごとに 2 億 5,000 万のバケット数を選択すると、ハッシュ テーブルのオーバーヘッドの合計は 8 ギガバイトになります。 これは、各インデックスが個々の行に追加するメモリ使用量の 8 バイトに加えて、このシナリオでは 8 ギガバイトであることに注意してください (4 インデックス * 8 バイト * 2 億 5,000 万行)。

フル テーブル スキャンは、通常、OLTP ワークロードのパフォーマンスクリティカル パスには含まれません。 そのため、メモリ使用率とポイント参照操作と挿入操作のパフォーマンスのどちらを選択します。

  • メモリ使用率に問題がある場合は、インデックス キー値の数に近いバケット数を選択します。 バケット数はインデックス キー値の数を大幅に減らさないでください。これは、ほとんどの DML 操作と、サーバーの再起動後にデータベースの復旧にかかる時間にも影響するためです。

  • ポイント参照のパフォーマンスを最適化する場合、一意のインデックス値の数の 2 倍または 3 倍のバケット数が多いほど適切です。 バケット数が多いほど、メモリ使用率が増加し、インデックスのフル スキャンに必要な時間が長くなります。

こちらもご覧ください

Memory-Optimized テーブルのインデックス