SQL Server 2014 実践シリーズ （HTML 版）
「No.1 インメモリ OLTP 機能の実践的な利用方法」

4.9　BUCKET_COUNT の違いによる性能差

HASH インデックスでは、BUCKET_COUNT（バケット数）を適切な値へ設定していないと、性能低下の原因に繋がります。

これについて、col2 の検索（取得件数が約 5件になる検証）で利用したのと同じテーブル（1,000万件のデータ）で説明します（以下）。

col2 列の HASH インデックスの BUCKET_COUNT を 400万、100万、10万、1万に設定したテーブルを 4つ作成しました。

このテーブルに対して、次のように INSERT .. SELECT ステートメントを利用して、1,000万件分のデータを一括コピーしています。

◆ BUCKET_COUNT の違いによる INSERT .. SELECT の性能差

上の INSERT .. SELECT で 1,000万件のデータを一括コピーしたときの性能差は、次のようになりました。

BUCKET_COUNT が 100万のときは 1.1倍、10万のときは 2.1倍、1万のときは 9.2倍も遅くなっていることを確認できました。

今回の col2 列は、次のように一意な値が約 200万件あります。

また、特定の値で検索すると、約 5件の結果が返ります（約200万件 * 約5件=1,000万件）。

◆ BUCKET_COUNT の違いによる SELECT の性能差

BUCKET_COUNT が異なる場合の SELECT ステートメント（col2 列での検索）の性能差は、次のようになりました。

BUCKET_COUNT が 100万のときは 1.01倍（ほぼ同じ）、10万のときは 1.1倍、1万のときは 1.66倍も遅くなっていることを確認できました。

このように、BUCKET_COUNT の設定は、INSERT や SELECT へ影響があることを確認できました。特に BUCKET_COUNT を小さい値に設定した場合（col2 の例では １万件）は、性能が大きく低下するので注意が必要です。

◆ BUCKET_COUNT の設定基準

BUCKET_COUNT の設定基準は、オンライン ブックの以下のトピックに記載されています。

ハッシュ インデックスの適切なバケット数の決定
http://msdn.microsoft.com/ja-jp/library/dn494956.aspx

このトピックでは、「ほとんどの場合、バケット数はインデックス キーにおける別個の値の数の 1 倍から 2 倍の範囲に設定する必要があります」と記載されています。別個の数は、前述の DISTINCT で検索した一意な値の数のことで、col2 では約200万件でした（以下に再掲）。

したがって、オンライン ブックの記述によれば、col2 列では、200万（1倍）～400万（2倍）ぐらいが妥当な設定値であるということになります。実際、400万に設定したときと、100万に設定したときでは、性能差が現れたので（100万に設定したほうが若干遅くなったので）、col2 に関しては、400万程度が妥当な設定値であることが分かります。また、BUCKET_COUNT は、テーブルの作成時（CREATE TABLE 時）に設定して、後から変更することができないものなので、将来増えるであろうデータ量も想定して、多めに設定しておく必要があります（その分、メモリを消費することになりますが、どれぐらいのメモリを消費するのかについては後述します）。

また、このトピックでは、「バケット数は内部的に、最も近い 2 のべき乗に切り上げられます。たとえば、バケット数に 300,000 を指定すると、実際のバケット数は 524,288 になります。」と記載されていて、BUCKET_COUNT で設定した値に、最も近い 2のべき乗に設定される（切り上げられる）とあります。

したがって、代表的な 2のべき乗を知っておいた方が設定がしやすくなるので、次の表が参考になると思います。

◆ 空きバケット数、ハッシュ インデックスのチェーンの長さ

HASH インデックスでは、十分な BUCKET_COUNT を設定している場合（一意な数よりも大きい値へ設定している場合）には、同じ値が、同じハッシュ値になって、チェーンが構成されます。

これに対して、BUCKET_COUNT の設定が小さい場合には、異なる値でも、同じハッシュ値になってしまうことがあるので、次のようにチェーンが長くなってしまいます。これは、ハッシュ コリジョン（衝突）と呼ばれています。

現在のバケット数や、空いているバケット数、チェーンの長さなどは、次のように「dm_db_xtp_hash_index_stats」動的管理ビューを利用すると確認することができます。

この結果のうち、一番上のものが BUCKET_COUNT を 1万に設定したときのもので、現在のバケット数（total_bucket_count）が 16,384、空きバケット数（empty_bucket_count）が 0、avg_chain_length（平均のチェーンの長さ）が 610 にもなっていて、ハッシュ コリジョンが多発していることが分かります。

これに対して、上から 4つ目の結果が BUCKET_COUNT を 400万に設定したときのもので、現在のバケット数が 4,194,304、空きバケット数が 2,590,043（259万空いている）、平均のチェーンの長さが 6 になっていて、妥当なチェーンの長さになっていることが分かります（col2 は、約5件の結果が返るので、チェーンの長さは 5前後が妥当になります）。

このように、設定した BUCKET_COUNT が妥当かどうかは、このクエリを実行することで確認することができるので、確認しておくことをお勧めします。

◆ バケット数の違いによるメモリ使用量の差

バケット数の違いによるメモリ使用量の差は、dm_db_xtp_table_memory_stats 動的管理ビューを利用して確認することができます。

memory_allocated_for_indexes_kb が、インデックスに割り当てられたメモリ量で、この値は、バケット数をもとに決定されます。インメモリ OLTP では、1つのバケット数に 8バイトを消費するので、次の表のようになります。

今回のテーブルは、PK（col1）の BUCKET_COUNT を 1億に設定しているので、1,048,576 KB（1GB）にプラスして、col2 の BUCKET_COUNT が 100万なら +8,192（8MB）で、1,056,768 KB、400万なら +32,768（8MB）で、1,081,344 KB を消費することになります。

なお、メモリ使用量の見積もり方法については、オンライン ブックの以下のトピックも参考になります。

メモリ最適化テーブルのメモリ必要量の推定
http://msdn.microsoft.com/ja-jp/library/dn282389.aspx

メモリ最適化テーブルのテーブルと行のサイズ
http://msdn.microsoft.com/ja-jp/library/dn205318.aspx