このトピックでは、ハッシュ インデックスに関する一般的な問題のトラブルシューティングと回避に焦点を当てます。
検索にはハッシュ インデックス キー列のサブセットが必要です
発行： ハッシュ インデックスでは、ハッシュ値を計算し、ハッシュ テーブル内の対応する行を検索するために、すべてのインデックス キー列の値が必要です。 そのため、クエリに WHERE 句内のインデックス キーのサブセットのみの等値述語が含まれている場合、SQL Server はインデックス シークを使用して WHERE 句の述語に対応する行を検索できません。
これに対し、ディスク ベースの非クラスター化インデックスやメモリ最適化非クラスター化インデックスなどの順序付きインデックスでは、インデックスの先頭列である限り、インデックス キー列のサブセットに対するインデックス シークがサポートされます。
症状： これにより、SQL Server はインデックス シークではなく完全なテーブル スキャンを実行する必要があり、通常は高速な操作であるため、パフォーマンスが低下します。
トラブルシューティングの方法: パフォーマンスの低下に加えて、クエリ プランの検査では、インデックス シークではなくスキャンが表示されます。 クエリが非常に単純な場合、クエリ テキストとインデックス定義を検査すると、検索にインデックス キー列のサブセットが必要かどうかも表示されます。
次の表とクエリについて考えてみましょう。
CREATE TABLE [dbo].[od]
(
o_id INT NOT NULL,
od_id INT NOT NULL,
p_id INT NOT NULL,
CONSTRAINT PK_od PRIMARY KEY NONCLUSTERED HASH (o_id, od_id) WITH (BUCKET_COUNT = 10000)
)
WITH (MEMORY_OPTIMIZED = ON)
SELECT p_id
FROM dbo.od
WHERE o_id=1
テーブルには 2 つの列 (o_id、od_id) のハッシュ インデックスがあり、クエリには等値述語 (o_id) があります。 クエリにはインデックス キー列のサブセットに対してのみ等値述語が含まれるので、SQL Server は PK_od を使用してインデックス シーク操作を実行できません。代わりに、SQL Server は完全なインデックス スキャンに戻す必要があります。
回避策: 考えられる回避策がいくつかあります。 例えば次が挙げられます。
非クラスター化ハッシュではなく非クラスター化型としてインデックスを再作成します。 メモリ最適化非クラスター化インデックスは順序付けされるため、SQL Server は先頭のインデックス キー列に対してインデックス シークを実行できます。 この例の結果の主キー定義は
constraint PK_od primary key nonclustered。WHERE 句の列と一致するように現在のインデックス キーを変更します。
クエリの WHERE 句の列と一致する新しいハッシュ インデックスを追加します。 この例では、結果のテーブル定義は次のようになります。
CREATE TABLE dbo.od ( o_id INT NOT NULL, od_id INT NOT NULL, p_id INT NOT NULL, CONSTRAINT PK_od PRIMARY KEY NONCLUSTERED HASH (o_id,od_id) WITH (BUCKET_COUNT=10000), INDEX ix_o_id NONCLUSTERED HASH (o_id) WITH (BUCKET_COUNT=10000) ) WITH (MEMORY_OPTIMIZED=ON)
メモリ最適化ハッシュ インデックスは、特定のインデックス キー値に重複する行が多数存在する場合は最適に実行されないことに注意してください。この例では、列o_idの一意の値の数がテーブル内の行数よりもはるかに少ない場合は、インデックスを追加するのが最適ではありません (o_id)。代わりに、インデックスPK_odの型をハッシュから非クラスター化に変更することをお勧めします。 詳細については、「 ハッシュ インデックスの正しいバケット数の決定」を参照してください。