パーティション インデックスの専用ガイドライン
パーティション インデックスはベース テーブルとは独立して実装できますが、一般的にはパーティション テーブルをデザインしてインデックスを作成する方法が適切です。その方法を実行すると、SQL Server により、ベース テーブルと同一のパーティション構成およびパーティション分割列を使用してインデックスのパーティション分割が自動的に実行されます。その結果、インデックスは本質的にベース テーブルと同一の状態でパーティション分割されます。この状態をインデックスがベース テーブルに固定されるといいます。
インデックスを作成するときに、異なるパーティション構成を指定したりインデックスを保存するファイル グループを別に指定したりした場合、SQL Server によりインデックスがテーブルに固定されることはありません。
パーティションを追加することでインデックスが拡張すること、またはパーティションの切り替えが頻繁に発生することが見込まれる場合、インデックスをパーティション テーブルに固定することが非常に重要です。詳細については、「データのサブセットを管理するためのパーティションの設計」を参照してください。テーブルとインデックスが固定されている状態では、両者のパーティション構造を保ったまま SQL Server がパーティションをすばやく効率的に切り替えることができます。
.gif "注意") 注意 |
|---|
ベース テーブルに固定させるために、インデックスを同じ名前のパーティション関数に加える必要はありません。ただし、インデックスとベース テーブルのパーティション関数は、本質的に同一 (つまり、1) 引数のデータ型が同一、2) 定義されるパーティションの数が同一、3) 定義されるパーティションの境界値が同一) である必要があります。 |
データベース エンジン チューニング アドバイザの [チューニング オプション] タブには、推奨の新しいインデックスをベース テーブルに固定するように指定するための [固定パーティション分割] 設定が用意されています。[固定パーティション分割を保持する] 設定にも同じ機能がありますが、この設定は、既存の固定されていないインデックスを削除するためにも使用できます。詳細については、「[データベース エンジン チューニング アドバイザ] ([チューニング オプション] タブ)」を参照してください。データベース エンジン チューニング アドバイザは、パフォーマンスを向上させるためのインデックスを知るために使用するのが一般的ですが、その際固定されているインデックスと固定されていないインデックスが混在して推奨される場合もあります。詳細については、「データベース エンジン チューニング アドバイザの概要」を参照してください。
次のような場合、パーティション インデックスをベース テーブルとは独立して (固定しないで) デザインすると便利です。
ベース テーブルがパーティション分割されていない。
インデックス キーが一意であり、テーブルのパーティション分割列を含んでいない。
異なる結合列を使用して多くのテーブルが併置されている結合にベース テーブルを加える。
| 注意 |
|---|
パーティションの切り替えを有効にするには、テーブルのすべてのインデックスを固定する必要があります。 |
パーティション インデックスを作成するときは、次のことを考慮してください。
一意インデックスのパーティション分割
一意の (クラスタ化または非クラスタ化) インデックスをパーティション分割するときは、一意インデックス キーで使用している列の中からパーティション分割列を選択する必要があります。
| 注意 |
|---|
この制約により、SQL Server により 1 つのパーティションのみを調査すれば、新しいキー値がテーブルの既存の値と重複していないことを確認できます。 |
パーティション分割列を一意キーに含めることができない場合、代用として DML トリガを使用することで一意性を保証する必要があります。
クラスタ化インデックスのパーティション分割
クラスタ化インデックスをパーティション分割するときは、クラスタ化キーにパーティション分割列を含める必要があります。一意でないクラスタ化インデックスをパーティション分割するとき、クラスタ化キーでパーティション分割列を明示的に指定しない場合は、SQL Server の既定動作によりクラスタ化インデックスのキーの一覧にパーティション分割列が追加されます。クラスタ化インデックスが一意である場合、クラスタ化インデックス キーにパーティション分割列を含めるように明示的に指定する必要があります。
非クラスタ化インデックスのパーティション分割
一意の非クラスタ化インデックスをパーティション分割するときは、インデックス キーにパーティション分割列を含める必要があります。一意でない非クラスタ化インデックスをパーティション分割するときは、ベース テーブルにインデックスを固定するため、SQL Server の既定動作によりパーティション分割列がインデックスの非キー (付加) 列として追加されます。既にパーティション分割列がインデックスに存在している場合、SQL Server は追加を行いません。
メモリの制限とパーティション インデックス
SQL Server でパーティション インデックスを作成するパフォーマンスは、メモリにより制限される場合があります。テーブルに既にクラスタ化インデックスが適用されている場合、パーティション インデックスがベース テーブルまたはクラスタ化インデックスに固定されていないとメモリによる制限を特に受けます。
SQL Server でパーティション インデックスを作成するための並べ替えを実行するとき、最初にパーティションごとに 1 つの並べ替えテーブルが作成されます。次に、各パーティションのそれぞれのファイル グループ、または SORT_IN_TEMPDB インデックス オプションが指定されている場合は tempdb で並べ替えテーブルが作成されます。
1 つの並べ替えテーブルを作成するために最低限必要なメモリの量が決まっています。ベース テーブルに固定するパーティション インデックスを作成すると、並べ替えテーブルは一度に 1 つずつ作成されるのでメモリの消費を抑えることができます。しかし、固定されないパーティション インデックスを作成すると、複数の並べ替えテーブルが同時に作成されます。
そのため、このように同時に並べ替えを行うには十分なメモリが必要です。パーティションの数が多いと、必要なメモリも増えます。1 つの並べ替えテーブル、つまりパーティションあたり最低必要なサイズは 40 ページ (1 ページは 8 KB) です。たとえば、100 個のパーティションから構成される固定されないパーティション インデックスは、同時に 4,000 (40 * 100) ページを同時に並べ替えることができるメモリが必要です。これだけのメモリを使用できれば、作成操作は成功しますがパフォーマンスが低下する場合があります。これだけのメモリを使用できない場合、作成操作は失敗します。一方、100 個のパーティションから構成される固定されたパーティション インデックスは、複数の並べ替えが同時に行われることがないので、40 ページを並べ替えることができるメモリがあれば十分です。
固定されたインデックス、固定されないインデックスを問わず、SQL Server がマルチプロセッサ コンピュータで 2 次以上の並列処理によって作成操作を実行している場合、メモリの要件がさらに高くなる場合もあります。これは並列処理の次数が多いと、メモリの要件も高くなるためです。たとえば、SQL Server の並列処理の次数が 4 に設定されている場合、100 個のパーティションから構成される固定されないパーティション インデックスは、同時に 4 基のプロセッサで 4,000 ページを並べ替えるために 16,000 ページ分のメモリが必要です。パーティション インデックスが固定されている場合、4 基のプロセッサで 40 ページを並べ替えるため、メモリの要件は 160 (4 * 40) ページまで下がります。MAXDOP インデックス オプションを使用して、手動で並列処理の次数を減らすことができます。詳細については、「並列インデックス操作の構成」を参照してください。
インデックスを作成するときの SQL Server による並べ替え操作の方法の詳細については、「tempdb とインデックスの作成」を参照してください。