非クラスタ化インデックスのサイズの算出

  • [アーティクル]

非クラスタ化インデックスを格納するために必要な領域は、次の手順で算出することができます。

  1. 非クラスタ化インデックスの非リーフ レベルのインデックス情報の格納に使用する領域を計算します。
  2. 非クラスタ化インデックスのリーフ レベルのインデックス情報の格納に使用する領域を計算します。
  3. 計算した値を合計します。

手順 1. 非リーフ レベルのインデックス情報の格納に使用する領域を計算します。

ms190620.note(ja-jp,SQL.90).gif重要 :
この手順で使用する値は、手順 2. でも使用するため、控えておいてください。

上位レベルのインデックスの格納に必要な領域は、次の手順で算出することができます。

  1. 次のように、テーブル内の行数を指定します。
    Num_Rows = テーブル内の行数
  2. 次のように、インデックス キーの固定長列と可変長列の数を指定し、それらの列の格納に必要な領域を計算します。
    インデックスのキー列には、固定長列と可変長列を含めることができます。内部レベルのインデックス行サイズを算出するには、これら各列のグループがインデックス行内を占有する領域を計算します。列のサイズは、データ型と長さの指定によって異なります。詳細については、「データ型 (データベース エンジン)」を参照してください。
    Num_Key_Cols = キー列 (固定長および可変長) の総数
    Fixed_Key_Size = すべての固定長キー列の合計バイト サイズ
    Num_Variable_Key_Cols = 可変長キー列の数
    Max_Var_Key_Size = すべての可変長キー列の最大バイト サイズ
  3. インデックスが一意ではない場合に必要なデータ行ロケータの領域を、次のように計算します。
    非クラスタ化インデックスが一意ではない場合、データ行ロケータと非クラスタ化インデックス キーを組み合わせて、各行に一意のキー値が生成されます。
    非クラスタ化インデックスがヒープ上にある場合は、データ行ロケータはヒープ RID になります。このサイズは 8 バイトです。
    Num_Key_Cols = Num_Key_Cols + 1
    Num_Variable_Key_Cols = Num_Variable_Key_Cols + 1
    Max_Var_Key_Size = Max_Var_Key_Size + 8
    非クラスタ化インデックスがクラスタ化インデックス上にある場合は、データ行ロケータはクラスタ化キーになります。非クラスタ化インデックス キーと組み合わせる必要がある列は、非クラスタ化インデックスのキー列のセットに含まれていないクラスタ化キーの列です。
    Num_Key_Cols = Num_Key_Cols + 非クラスタ化インデックスのキー列のセットに含まれていないクラスタ化キー列数 (クラスタ化インデックスが一意でない場合は + 1)
    Fixed_Key_Size = Fixed_Key_Size + 非クラスタ化インデックスのキー列のセットに含まれていない固定長クラスタ化キー列の合計バイト サイズ
    Num_Variable_Key_Cols = Num_Variable_Key_Cols + 非クラスタ化インデックスのキー列のセットに含まれていない可変長クラスタ化キー列数 (クラスタ化インデックスが一意でない場合は + 1)
    Max_Var_Key_Size = Max_Var_Key_Size + 非クラスタ化インデックスのキー列のセットに含まれていない可変長クラスタ化キー列の最大バイト サイズ (クラスタ化インデックスが一意でない場合は + 4)
  4. NULL ビットマップと呼ばれる行の一部が、列に NULL 値を許容するかどうかを管理するために予約されている場合があります。このサイズは次のように計算します。
    手順 1.3. で説明している必要なクラスタ化キー列を含め、インデックス キーに NULL 値を許容する列が存在する場合には、インデックス行の一部が NULL ビットマップ用に予約されます。
    Index_Null_Bitmap = 2 + ((NULL 値を許容するキー列数 + 7) / 8)
    上記の式の計算結果は、整数部分だけを使用します。小数部分は無視してください。
    NULL 値を許容するキー列が存在しない場合は、Index_Null_Bitmap を 0 に設定します。
  5. 可変長データ サイズを計算します。
    必要なクラスタ化インデックスのキー列を含め、インデックス キーに可変長列が存在する場合は、インデックス行内に可変長列を格納するのに使用する領域を次の式で計算します。
    Variable_Key_Size* = 2 + (Num_Variable_Key_Cols x 2) + *Max_Var_Key_Size この式は、すべての可変長列がいっぱいになることを前提としています。可変長列のストレージ領域の使用率が 100% 以下になることが予想される場合、その使用率に基づいて Max_Var_Key_Size の値を調整し、テーブルの全体サイズをより正確に算出することができます。
    可変長列が存在しない場合は、Variable_Key_Size を 0 に設定します。
  6. 次の式でインデックス行のサイズを計算します。
    Index_Row_Size = Fixed_Key_Size + Variable_Key_Size + Index_Null_Bitmap + 1 (インデックス行の行ヘッダー オーバーヘッド) + 6 (子ページ ID のポインタ)
  7. 次の式で、1 ページあたりのインデックス行数を計算します (1 ページあたりの空きバイト数は 8,096 です)。
    Index_Rows_Per_Page = 8096 / (Index_Row_Size + 2)
    インデックス行は複数のページにまたがらないため、計算結果の端数は切り捨ててください。上の式の 2 という値は、ページのスロット配列内にある行の入力用です。
  8. 次の式で、インデックス内のレベル数を計算します。
    Levels = 1 + log Index_Rows_Per_Page (Num_Rows / Index_Rows_Per_Page)
    この値には、非クラスタ化インデックスのリーフ レベルは含まれません。
  9. 次の式で、インデックス内のページ数を計算します。
    Num_Index_Pages = ∑Level (Index_Rows_Per_Page)Level – 1
    この場合、1 <= Level <= Levels という関係になります。
    簡単な例として、リーフ レベルより上位のレベルで必要なインデックス行の総数が 1,000 行で、1 ページあたり 10 行のインデックス行を収めることができるインデックスを例に取ります。つまり、1,000 行のインデックス行を格納するために 100 ページが必要になります。次のレベルのインデックスでは、100 行のインデックス行を格納する必要があります。つまり、10 ページが必要になります。最上位レベルのインデックスでは、10 行のインデックス行を格納する必要があります。つまり、1 ページが必要になります。これらの数値を上記の式に当てはめると、次のようになります。
    Height = 1 + log10 (1000 / 10) = 3
    Num_Index_Pages = (10)3-1 + (10)2-1 + (10)1-1 = 111、これが例で説明したページ数です。
  10. 次の式で、インデックスのサイズを計算します (1 ページあたりの総バイト数は 8,192 です)。
    Index_Space_Used* = 8192 x *Num_Index_Pages

手順 2. リーフ レベルのインデックス情報の格納に使用する領域を計算します。

インデックスのリーフ レベルを格納するために必要な領域は、次の手順で算出することができます。この手順を完了するには、手順 1. で控えた値が必要になります。

  1. 次のように、リーフ レベルの固定長列と可変長列の数を指定し、それらの列の格納に必要な領域を計算します。
    ms190620.note(ja-jp,SQL.90).gifメモ :
    SQL Server 2005 では、インデックス キー列以外に、非キー列を追加することで、非クラスタ化インデックスを拡張できるようになりました。このように追加された列は、非クラスタ化インデックスのリーフ レベルにしか格納されません。詳細については、「付加列インデックスの作成」を参照してください。
    ms190620.note(ja-jp,SQL.90).gifメモ :
    SQL Server 2005 では、定義済みのテーブルの合計サイズが 8,060 バイトを超える varchar、nvarchar、varbinary、または sql_variant 列の連結が可能になりました。この場合も、varchar、varbinary、または sql_variant 列の場合は 8,000 バイト、nvarchar 列の場合は 4,000 バイトの制限内に、各列のサイズを収める必要があります。ただし、これらの列を連結したサイズは、テーブルの制限である 8,060 バイトを超過してもかまいません。これは、既に付加列を含んでいる非クラスタ化インデックスのリーフ行にも当てはまります。詳細については、「8 KB を超える場合の行オーバーフロー データ」を参照してください。
    非クラスタ化インデックスに付加列が含まれていない場合は、手順 1.3. での変更を含め、手順 1. の値を使用します。

Num_Leaf_Cols* = *Num_Key_Cols Fixed_Leaf_Size* = *Fixed_Key_Size Num_Variable_Leaf_Cols* = *Num_Variable_Key_Cols Max_Var_Leaf_Size* = *Max_Var_Key_Size 非クラスタ化インデックスに付加列が含まれている場合は、手順 1.3. での変更を含め、手順 1. の値に適切な値を加えます。列のサイズは、データ型と長さの指定によって異なります。詳細については、「データ型 (データベース エンジン)」を参照してください。
Num_Leaf_Cols = Num_Key_Cols + 付加列の数
Fixed_Leaf_Size = Fixed_Key_Size + 固定長付加列の合計バイト サイズ
Num_Variable_Leaf_Cols = Num_Variable_Key_Cols + 可変長付加列の数
Max_Var_Leaf_Size = Max_Var_Key_Size + 可変長付加列の最大バイト サイズ
2. データ行ロケータに必要な領域を次のように計算します。
非クラスタ化インデックスが一意でない場合は、既に手順 1.3. でデータ行ロケータのオーバーヘッドが考慮されているので、他の変更は必要ありません。次の手順に進みます。
非クラスタ化インデックスが一意の場合は、リーフ レベルのすべての行のデータ行ロケータに必要な領域を計算します。
非クラスタ化インデックスがヒープ上にある場合は、データ行ロケータはヒープ RID (8 バイト) になります。
Num_Leaf_Cols = Num_Leaf_Cols + 1
Num_Variable_Leaf_Cols = Num_Variable_Leaf_Cols + 1
Max_Var_Leaf_Size = Max_Var_Leaf_Size + 8
非クラスタ化インデックスがクラスタ化インデックス上にある場合は、データ行ロケータはクラスタ化キーになります。非クラスタ化インデックス キーと組み合わせる必要がある列は、非クラスタ化インデックスのキー列のセットに含まれていないクラスタ化キーの列です。
Num_Leaf_Cols = Num_Leaf_Cols + 非クラスタ化インデックスのキー列のセットに含まれていないクラスタ化キー列数 (クラスタ化インデックスが一意でない場合は + 1)
Fixed_Leaf_Size = Fixed_Leaf_Size + 非クラスタ化インデックスのキー列のセットに含まれていない固定長クラスタ化キー列数
Num_Variable_Leaf_Cols = Num_Variable_Leaf_Cols + 非クラスタ化インデックスのキー列のセットに含まれていない可変長クラスタ化キー列数 (クラスタ化インデックスが一意でない場合は + 1)
Max_Var_Leaf_Size = Max_Var_Leaf_Size + 非クラスタ化インデックスのキー列のセットに含まれていない可変長クラスタ化キー列のバイト サイズ (クラスタ化インデックスが一意でない場合は + 4)
3. 次のように、NULL ビットマップのサイズを計算します。
Leaf_Null_Bitmap = 2 + ((Num_Leaf_Cols + 7) / 8)
上記の式の計算結果は、整数部分だけを使用します。小数部分は無視してください。
4. 可変長データ サイズを計算します。
上記の手順 2.2. で説明した必要なクラスタ化キー列を含め、インデックス キーに可変長列が存在する場合は、インデックス行内の可変長列を格納するのに使用する領域を次の式で計算します。
Variable_Leaf_Size* = 2 + (Num_Variable_Leaf_Cols x 2) + *Max_Var_Leaf_Size この式は、すべての可変長列がいっぱいになることを前提としています。可変長列のストレージ領域の使用率が 100% 以下になることが予想される場合、その使用率に基づいて Max_Var_Leaf_Size の値を調整し、テーブルの全体サイズをより正確に算出することができます。
可変長列が存在しない場合は、Variable_Leaf_Size に 0 を設定します。
5. 次の式でインデックス行のサイズを計算します。
Leaf_Row_Size = Fixed_Leaf_Size + Variable_Leaf_Size + Leaf_Null_Bitmap + 1 (インデックス行の行ヘッダー オーバーヘッド) + 6 (子ページ ID のポインタ)
6. 次の式で、1 ページあたりのインデックス行数を計算します (1 ページあたりの空きバイト数は 8,096 です)。
Leaf_Rows_Per_Page = 8096 / (Leaf_Row_Size + 2)
インデックス行は複数のページにまたがらないため、計算結果の端数は切り捨ててください。上の式の 2 という値は、ページのスロット配列内にある行の入力用です。
7. 指定した FILL FACTOR に基づいて、1 ページあたりの予約済みの空き行数を次の式で計算します。
Free_Rows_Per_Page = 8096 x ((100 - Fill_Factor) / 100) / (Leaf_Row_Size + 2)
計算で使用する FILL FACTOR は、パーセンテージではなく整数値です。行は複数のページにまたがらないため、計算結果の端数は切り捨ててください。FILL FACTOR の値が大きくなるほど、各ページに格納できるデータの量が多くなり、ページ数は少なくなります。上の式の 2 という値は、ページのスロット配列内にある行の入力用です。
8. 次の式で、すべての行を格納するために必要なページ数を計算します。
Num_Leaf_Pages = Num_Rows / (Leaf_Rows_Per_Page - Free_Rows_Per_Page)
算出したページ数の端数は切り上げてください。
9. 次の式で、インデックスのサイズを計算します (1 ページあたりの総バイト数は 8,192 です)。
Leaf_Space_Used* = 8192 x *Num_Leaf_Pages

手順 3. 計算した値を合計します。

次の式で、上記の 2 つの手順から取得した値を合計します。

非クラスタ化インデックス サイズ (バイト) = Leaf_Space_Used + Index_Space_used

この計算では、次の要素は考慮されていません。

  • パーティション分割
    パーティション分割による領域のオーバーヘッドは最小限に抑えられますが、計算が複雑になります。これは、計算に含めるほど重要なことではありません。
  • アロケーション ページ
    ヒープに割り当てられたページの追跡に使用される IAM ページは少なくとも 1 ページありますが、領域のオーバーヘッドは最小限に抑えられ、使用される IAM ページ数を正確に計算できるアルゴリズムはありません。
  • ラージ オブジェクト (LOB) の値
    LOB データ型の varchar(max)、varbinary(max)、nvarchar(max)、text、ntext、xml、および image の値を格納するために使用される領域を正確に特定するアルゴリズムは複雑です。LOB データ型の値で使用される領域の計算は、想定される LOB 値の平均サイズを合計し、Num_Rows で乗算し、非クラスタ化インデックスの合計サイズに加算するだけで十分です。

参照

概念

クラスタ化インデックスの設計ガイドライン
インデックスの作成 (データベース エンジン)
非クラスタ化インデックスのデザイン ガイドライン
テーブル サイズの見積もり
クラスタ化インデックスのサイズの見積もり
ヒープ サイズの見積もり

その他の技術情報

データベース サイズの見積もり

ヘルプおよび情報

SQL Server 2005 の参考資料の入手