メモリ最適化テーブルのハッシュインデックスのトラブルシューティング

[アーティクル]
04/26/2023

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance

前提条件

この記事を理解するための重要なコンテキスト情報は、次の記事に収められています。

実際的な数値

メモリ最適化テーブルのハッシュインデックスを作成する場合、作成時にバケットの数を指定する必要があります。ほとんどの場合、バケット数は、理想的にはインデックスキーの個別の値の数の 1 から 2 倍の範囲内にします。

ただし、BUCKET_COUNT が推奨される範囲を多少逸脱している場合でも、ハッシュインデックスのパフォーマンスは、許容できるか容認できる可能性があります。少なくとも、ハッシュインデックスの BUCKET_COUNT には、メモリ最適化テーブルが増大すると予測される行数とほぼ等しい値を指定することを検討してください。
テーブルに 2,000,000 の行があるが、その数が 10 倍の 20,000,000 行に増大すると予測していると仮定します。テーブル内の行数の 10 倍のバケット数から始めます。これにより、行数の増加に対応する余地ができます。

理想的には、行数が最初のバケット数に達したときに、バケット数を増やすことになります。
行数がバケット数の 5 倍に増大したとしても、多くの場合、パフォーマンスは良好です。

ハッシュインデックスに 10,000, 000 の個別のキー値があると仮定します。

バケット数 2,000,000 は、容認できるほぼ最低の値です。パフォーマンスの低下の度合いは、許容できる可能性があります。

インデックス内の重複する値が多すぎる

ハッシュインデックス値の重複率が高いと、ハッシュバケットのチェーンが長くなります。

前の T-SQL 構文のコードブロックと同じ SupportEvent テーブルがあると仮定します。次の T-SQL コードは、 すべての 値の 一意の 値に対する比率を検出して表示する方法を示しています。

-- Calculate ratio of:  Rows / Unique_Values.  
DECLARE @allValues float(8) = 0.0, @uniqueVals float(8) = 0.0;  
  
SELECT @allValues = Count(*) FROM SupportEvent;  
  
SELECT @uniqueVals = Count(*) FROM  
  (SELECT DISTINCT SupportEngineerName  
      FROM SupportEvent) as d;  
  
    -- If (All / Unique) >= 10.0, use a nonclustered index, not a hash.   
SELECT Cast((@allValues / @uniqueVals) as float) as [All_divby_Unique];  
go

比率 10.0 以上は、インデックスの種類としてハッシュは適していないことを意味します。代わりに非クラスター化インデックスを使用することを検討してください。

ハッシュインデックスのバケット数のトラブルシューティング

このセクションでは、ハッシュインデックスのバケット数のトラブルシューティング方法について説明します。

チェーンと空のバケットの統計を監視する

次の T-SQL SELECT を実行することで、ハッシュインデックスの統計を監視できます。この SELECT では、 sys.dm_db_xtp_hash_index_statsという名前のデータ管理ビュー (DMV) を使用します。

SELECT  
  QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(h.object_id)) as [table],   
  i.name                   as [index],   
  h.total_bucket_count,  
  h.empty_bucket_count,  
    
  FLOOR((  
    CAST(h.empty_bucket_count as float) /  
      h.total_bucket_count) * 100)  
                            as [empty_bucket_percent],  
  h.avg_chain_length,   
  h.max_chain_length  
FROM  
        sys.dm_db_xtp_hash_index_stats  as h   
  JOIN sys.indexes                     as i  
          ON h.object_id = i.object_id  
          AND h.index_id  = i.index_id  
JOIN sys.memory_optimized_tables_internal_attributes ia ON h.xtp_object_id=ia.xtp_object_id
JOIN sys.tables t on h.object_id=t.object_id
WHERE ia.type=1
ORDER BY [table], [index];

SELECT の結果を、次の統計ガイドラインと比較します。

空のバケット:
- 33% は適切な目標値ですが、通常は、より大きなパーセント値 (ちょうど 90%) がふさわしい値となります。
- バケット数が個別のキー値の数と等しい場合は、バケットの約 33% が空です。
- 10% 未満の値は低すぎます。
バケット内のチェーン:
- 平均チェーン長は、重複するインデックスキーの値がない場合、1 が最適です。通常、10 までのチェーン長を使用できます。
- 平均チェーン長が 10 より大きく、空のバケットの割合が 10% を超える場合、非常に多くのデータが重複しているため、ハッシュインデックスは最も適切な種類ではない可能性があります。

チェーンと空のバケットのデモンストレーション

次の T-SQL コードブロックを使用すると、 SELECT * FROM sys.dm_db_xtp_hash_index_stats;を簡単にテストできます。このコードブロックは 1 分で完了します。次のコードブロックのフェーズを以下に示します。

いくつかのハッシュインデックスがあるメモリ最適化テーブルを作成します。
テーブルに数千行を設定します。
a. モジュロ演算子を使用して、StatusCode 列の重複する値の割合を構成します。
b. INSERT ループは、262,144 行を約 1 分間で挿入します。
PRINT は、前述の SELECT を sys.dm_db_xtp_hash_index_statsから実行することを指示するメッセージを出力します。

DROP TABLE IF EXISTS SalesOrder_Mem;  
go  
  
  
CREATE TABLE SalesOrder_Mem  
(  
  SalesOrderId   uniqueidentifier  NOT NULL  DEFAULT newid(),  
  OrderSequence  int               NOT NULL,  
  OrderDate      datetime2(3)      NOT NULL,  
  StatusCode     tinyint           NOT NULL,  
  
  PRIMARY KEY NONCLUSTERED  
      HASH (SalesOrderId)  WITH (BUCKET_COUNT = 262144),  
  
  INDEX ix_OrderSequence  
      HASH (OrderSequence) WITH (BUCKET_COUNT = 20000),  
  
  INDEX ix_StatusCode  
      HASH (StatusCode)    WITH (BUCKET_COUNT = 8),  
  
  INDEX ix_OrderDate       NONCLUSTERED (OrderDate DESC)  
)  
  WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)  
go  
  
--------------------  
  
SET NOCOUNT ON;  
  
-- Same as PK bucket_count.  68 seconds to complete.  
DECLARE @i int = 262144;  
  
BEGIN TRANSACTION;  
  
WHILE @i > 0  
BEGIN  
  
  INSERT SalesOrder_Mem  
      (OrderSequence, OrderDate, StatusCode)  
    Values  
      (@i, GetUtcDate(), @i % 8);  -- Modulo technique.  
  
  SET @i -= 1;  
END  
COMMIT TRANSACTION;  
  
PRINT 'Next, you should query:  sys.dm_db_xtp_hash_index_stats .';  
go

前述の INSERT ループは、次の操作を行います。

主キーインデックスと ix_OrderSequence に一意の値を挿入します。
StatusCode の 8 つのみの個別の値を表す数十万行を挿入します。したがって、ix_StatusCode インデックスの値は高い割合で重複しています。

バケット数が最適でない場合のトラブルシューティングを行うために、 sys.dm_db_xtp_hash_index_statsからの SELECT の次の出力を確認します。これらの結果を得るために、セクション D.1 からコピーした SELECT に WHERE Object_Name(h.object_id) = 'SalesOrder_Mem' を追加しています。

SELECT の結果をコードの後に示しています。見やすくするために、結果を幅の狭い 2 つの結果表に意図的に分割しています。

バケット数に関する結果は次のとおりです。

IndexName	total_bucket_count	empty_bucket_count	EmptyBucketPercent
ix_OrderSequence	32768	13	0
ix_StatusCode	8	4	50
PK_SalesOrd_B14003...	262144	96525	36

チェーン長に関する結果を次に示します。

IndexName	avg_chain_length	max_chain_length
ix_OrderSequence	8	26
ix_StatusCode	65536	65536
PK_SalesOrd_B14003...	1	8

これらの結果表から、3 つのハッシュインデックスを解釈してみましょう。

ix_StatusCode:

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

ix_OrderSequence:

バケットの 0% が空です。この値は低すぎます。
このインデックスのすべての値が一意であっても、平均チェーン長は 8 になっています。
- したがって、バケット数を増やして、平均チェーン長が 2 または 3 に近づくまで減らす必要があります。
インデックスキーには 262,144 個の一意の値があるため、バケット数は最低でも 262,144 にする必要があります。
- 今後の増大が予測される場合、バケット数はさらに高くする必要があります。

Primary key index (PK_SalesOrd_...):

バケットの 36% が空です。これは適切な値です。
平均チェーン長は 1 であり、これも適切な値です。変更する必要はない。

トレードオフのバランスを考慮する

OLTP ワークロードは、個々の行に注目します。フルテーブルスキャンは、通常は OLTP ワークロードのパフォーマンスのクリティカルパスではありません。したがって、メモリ使用率の数量と、等値テストと挿入操作のパフォーマンスとのトレードオフのバランスを考慮する必要があります。

メモリの使用量のほうが大きな問題である場合:

一意のインデックスキー値の数に近いバケット数を選択します。
バケット数は、一意のインデックスキー値の数より大幅に小さくならないようにする必要があります。大幅に小さい場合、ほとんどの DML 操作や、サーバーの再起動後のデータベースの復旧に要する時間にも影響があります。

等値テストのパフォーマンスのほうが大きな問題である場合:

一意のインデックス値の数の 2 倍または 3 倍のバケット数が適しています。高いバケット数は、次を意味します。
- 1 つの特定の値を検索する場合の高速での取得。
- メモリ使用率の上昇。
- ハッシュインデックスのフルスキャンに必要な時間の増加。

追加の参考資料

メモリ最適化テーブルのハッシュインデックス
 メモリ最適化テーブルの非クラスター化インデックス

次の方法で共有

メモリ最適化テーブルのハッシュインデックスのトラブルシューティング

前提条件

実際的な数値

インデックス内の重複する値が多すぎる

ハッシュインデックスのバケット数のトラブルシューティング

チェーンと空のバケットの統計を監視する

チェーンと空のバケットのデモンストレーション

トレードオフのバランスを考慮する

追加の参考資料

フィードバック

その他のリソース

次の方法で共有

メモリ最適化テーブルのハッシュ インデックスのトラブルシューティング

前提条件

実際的な数値

インデックス内の重複する値が多すぎる

ハッシュ インデックスのバケット数のトラブルシューティング

チェーンと空のバケットの統計を監視する

チェーンと空のバケットのデモンストレーション

トレードオフのバランスを考慮する

追加の参考資料

フィードバック

その他のリソース

メモリ最適化テーブルのハッシュインデックスのトラブルシューティング

ハッシュインデックスのバケット数のトラブルシューティング