パーティション テーブルとパーティション インデックス

適用対象: はいSQL Server (サポートされているすべてのバージョン) はいAzure SQL データベース

SQL Server では、テーブルおよびインデックスのパーティション分割をサポートします。 パーティション テーブルとパーティション インデックスのデータは、必要に応じてデータベース内の複数のファイル グループに分散できるように、複数の単位に分割されます。 行のグループが各パーティションにマップされるように、データは行方向にパーティション分割されます。 1 つのインデックスまたはテーブルのすべてのパーティションは、同じデータベース内に存在する必要があります。 データに対するクエリまたは更新の実行時は、テーブルやインデックスが 1 つの論理エンティティとして扱われます。 SQL Server 2016 (13.x) SP1 より前では、パーティション テーブルとパーティション インデックスは、SQL Server のすべてのエディションで使用できるわけではありません。 SQL Server の各エディションでサポートされる機能の一覧については、「Editions and Supported Features for SQL Server 2016」 (SQL Server 2016 のエディションとサポートされる機能) を参照してください。

重要

SQL Server では、既定で最大 15,000 個のパーティションをサポートします。 SQL Server 2012 (11.x) 以前のバージョンでは、パーティションの数は既定で 1,000 に制限されていました。

パーティション分割の利点

大きなテーブルやインデックスをパーティション分割することで、次のような管理上およびパフォーマンス上の利点が得られます。

  • データ コレクション全体の整合性を保ちながら、データ サブセットの転送やアクセスを迅速かつ効率的に行うことができるようになります。 たとえば、OLTP システムから OLAP システムへのデータの読み込みなどの操作は、データがパーティション分割されていない場合は数分から数時間かかりますが、数秒で実行されるようになります。

  • 1 つまたは複数のパーティションでのメンテナンス操作をより迅速に実行できます。 テーブル全体ではなく、これらのデータ サブセットのみを対象にできるので、操作がより効率化されます。 たとえば、1 つまたは複数のパーティションでデータを圧縮するか、インデックスの 1 つまたは複数のパーティションを再構築するかを選択できます。

  • 頻繁に実行するクエリの種類とハードウェア構成に基づいて、クエリのパフォーマンスを改善できる場合があります。 たとえば、クエリ オプティマイザーで 2 つ以上のパーティション テーブル間の等結合クエリを行う場合、そのパーティション分割列が、テーブルが結合される列と同じであれば、処理がより高速になります。 詳細については、下の「クエリ」をご覧ください。

SQL Server により I/O 操作用にデータの並べ替えが実行される場合、まずパーティションでデータが並べ替えられます。 データの並べ替えのパフォーマンスを向上させるには、RAID を構成して複数のディスク間でパーティションのデータ ファイルをストライプします。 この方法を使用すると、 SQL Server では今までどおりデータがパーティションで並べ替えられますが、すべてのドライブの各パーティションに同時にアクセスできるようになります。

さらに、テーブル全体ではなくパーティション レベルでのロックのエスカレーションを有効にしてパフォーマンスを向上させることができます。 これにより、テーブルでのロックの競合を減らすことができます。 パーティションへのロックのエスカレーションを有効にしてロックの競合を減らすには、ALTER TABLE ステートメントの LOCK_ESCALATION オプションを AUTO に設定します。

ヒント

テーブルやインデックスのパーティションは、1 つのファイル グループ (たとえば PRIMARY ファイル グループ) に配置することも、複数のファイル グループに配置することもできます。 階層化されたストレージを使用する場合は、複数のファイル グループを使用することで、特定のパーティションを特定のストレージ階層に割り当てることができます。 パーティション分割のその他の利点もすべて、使用するファイル グループの数や特定のファイル グループのパーティション配置に関係なく利用できます。

コンポーネントおよび概念

テーブルおよびインデックスのパーティション分割に関連する用語を次に示します。

パーティション関数

テーブルまたはインデックスの行を、パーティション分割列と呼ばれる特定の列の値に基づいて、一連のパーティションにマップする方法を定義するデータベース オブジェクト。 パーティション分割列の各値は、パーティション値を返すパーティション関数への入力です。 パーティション関数によって、テーブルに含まれるパーティションの数とパーティションの境界が定義されます。 たとえば、販売注文データを格納するテーブルの場合、販売日などの datetime 列に基づいて、月別の 12 のパーティションに分割できます。

パーティション構成

パーティション関数のパーティションを一連のファイル グループにマップするデータベース オブジェクト。 パーティションを別々のファイル グループに配置する主な理由は、パーティションのバックアップ操作を個別に実行できるようにすることです。 これは、バックアップを個別のファイル グループで実行できるからです。

注意

Azure SQL Database では、プライマリ ファイル グループのみがサポートされています。

パーティション分割列

パーティション関数が、テーブルまたはインデックスをパーティション分割するために使用するテーブルまたはインデックスの列。 パーティション関数に参加する計算列は、明示的に PERSISTED とマークされている必要があります。 timestamp 型を除き、インデックス列として使用できるすべてのデータ型をパーティション分割列として使用できます。 ntexttextimagexmlvarchar(max)nvarchar(max) 、または varbinary(max) データ型を指定することはできません。 また、Microsoft .NET Framework 共通言語ランタイム (CLR) ユーザー定義型の列とエイリアス データ型の列を指定することはできません。

固定されたインデックス

対応するテーブルと同じパーティション構成に基づいて構築されたインデックス。 テーブルとインデックスが固定されている状態では、両者のパーティション構造を保ったまま SQL Server がパーティションをすばやく効率的に切り替えることができます。 ベース テーブルに固定させるために、インデックスを同じ名前のパーティション関数に加える必要はありません。 ただし、インデックスとベース テーブルのパーティション関数が次の点で基本的に同じでなければなりません。

  1. パーティション関数の引数に同じデータ型が含まれている。
  2. 同数のパーティションが定義されている。
  3. パーティションに同じ境界値が定義されている。

クラスター化インデックスのパーティション分割

クラスター化インデックスをパーティション分割するときは、クラスター化キーにパーティション分割列を含める必要があります。 一意でないクラスター化インデックスをパーティション分割するとき、クラスター化キーでパーティション分割列を明示的に指定しない場合は、SQL Server の既定動作によりクラスター化インデックスのキーの一覧にパーティション分割列が追加されます。 クラスター化インデックスが一意である場合、クラスター化インデックス キーにパーティション分割列を含めるように明示的に指定する必要があります。 クラスター化インデックスとインデックス アーキテクチャの詳細については、「クラスター化インデックスのデザイン ガイドライン」を参照してください。

非クラスター化インデックスのパーティション分割

一意の非クラスター化インデックスをパーティション分割するときは、インデックス キーにパーティション分割列を含める必要があります。 一意でない非クラスター化インデックスをパーティション分割するときは、ベース テーブルにインデックスを固定するため、SQL Server の既定動作によりパーティション分割列がインデックスの非キー (付加) 列として追加されます。 既にパーティション分割列がインデックスに存在している場合、SQL Server は追加を行いません。 非クラスター化インデックスとインデックス アーキテクチャの詳細については、「非クラスター化インデックスのデザイン ガイドライン」を参照してください。

固定されていないインデックス

対応するパーティション テーブルから個別に分割されたインデックス。 つまり、インデックスのパーティション構成が異なっているか、インデックスがベース テーブルとは別のファイル グループに配置されています。 次のような場合は、配置されていないパーティション インデックスを設計すると便利です。

  • ベース テーブルがパーティション分割されていない。
  • インデックス キーが一意であり、テーブルのパーティション分割列を含んでいない。
  • 異なる結合列を使用して多くのテーブルが併置されている結合にベース テーブルを加える。

パーティションの解消

クエリ オプティマイザーがクエリのフィルター条件を満たすために、関連するパーティションのみにアクセスするときに使用されるプロセス。

パフォーマンスに関するガイドライン

新しいパーティション数の制限が 15,000 になったことは、メモリ、パーティション インデックス操作、DBCC コマンド、およびクエリに影響します。 ここでは、パーティション数が 1,000 を超えた場合のパフォーマンスへの影響について説明し、必要に応じた回避策を示します。 パーティション数の上限が 15,000 になると、データを保存できる期間が長くなります。 ただし、データの保持期間は必要最小限とし、パフォーマンスとパーティション数とのバランスをとる必要があります。

プロセッサのコアとパーティションの数に関するガイドライン

並列操作でのパフォーマンスを最大化するには、プロセッサのコアと同じ数 (SQL Server で利用できる並列処理プロセッサの最大数である 64 個まで) のパーティションを使用することをお勧めします。

メモリ使用量とガイドライン

使用するパーティション数が多い場合は、16 GB 以上の RAM を使用することをお勧めします。 システムに十分なメモリがない場合は、データ操作言語 (DML) ステートメント、データ定義言語 (DDL) ステートメント、およびその他の処理においてメモリ不足によるエラーが発生する場合があります。 16 GB の RAM を搭載したシステムでメモリを集中的に使用するプロセスが多数実行される場合は、多数のパーティションで実行される操作でメモリが不足する可能性があります。 したがって、メモリを 16 GB よりも大きくするほど、パフォーマンスとメモリの問題が少なくなります。

SQL Server でパーティション インデックスを作成するパフォーマンスは、メモリにより制限される場合があります。 テーブルに既にクラスター化インデックスが適用されている場合、パーティション インデックスがベース テーブルまたはクラスター化インデックスに固定されていないとメモリによる制限を特に受けます。 この場合、index create memory サーバー構成オプションを増やすと便利な場合があります。 詳細については、「index create memory サーバー構成オプションの構成」を参照してください。

パーティション インデックス操作

固定されていないインデックスをパーティションが 1, 000 個以上あるテーブルに作成または再構築することは可能ですが、サポートされていません。 このような操作を行うと、操作中にパフォーマンスが低下したりメモリが過度に消費される可能性があります。

固定されたインデックスの作成および再構築にかかる時間は、パーティション数が増えるにつれて長くなります。 パフォーマンスおよびメモリの問題を回避するために、インデックスの作成および再構築の複数のコマンドを同時に実行しないことをお勧めします。

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 インデックス オプションを使用して、手動で並列処理の次数を減らすことができます。

DBCC コマンド

パーティション数が多い場合、DBCC コマンドの実行にかかる時間は、パーティション数が増えるほど長くなります。

クエリ

パーティションの解消を使用するクエリは、パーティション数が多くなると、それに応じてパフォーマンスが向上する可能性があります。 パーティションの解消を使用しないクエリの場合、その実行にかかる時間は、パーティション数が増えるほど長くなります。

たとえば、テーブルの行数が 10 億で、 AB、および Cの列があるとします。

  • シナリオ 1 では、テーブルが列 Aで 1,000 個のパーティションに分割されます
  • シナリオ 2 では、テーブルが列 Aで 10,000 個のパーティションに分割されます 列 A でフィルタリングする WHERE 句を持つテーブルでのクエリは、パーティションの解消を実行し、1 つのパーティションをスキャンします。 シナリオ 2 の場合は、パーティション内でスキャンする行数が少ないので、同じクエリがより高速に実行される可能性があります。 列 B でフィルタリングする WHERE 句を持つクエリは、すべてのパーティションをスキャンします。 シナリオ 1 の場合は、スキャンするパーティション数が少ないので、同じクエリがシナリオ 2 より高速に実行される可能性があります。

パーティション分割列以外の列に対して TOP や MAX/MIN のような演算子を使用するクエリは、すべてのパーティションを評価する必要があるため、パーティション分割によってパフォーマンスが低下する可能性があります。

2 つ以上のパーティション テーブル間での等結合を行うクエリを頻繁に実行する場合、それらのテーブルのパーティション分割列は、テーブルの結合先の列と同じにする必要があります。 また、等結合するテーブルまたはテーブルのインデックスを併置する必要があります。 つまり、これらのテーブルでは、同じ名前のパーティション関数または名前は異なるが実質的には同じ関数のいずれかが使用されることになります。後者の場合、関数には次のような性質があります。

  • パーティション分割に使用するパラメーターの数が同数で、対応するパラメーターのデータ型が同じです。
  • 同数のパーティションが定義されている
  • パーティションに同じ境界値が定義されている このような性質により、パーティション自体を結合できるので、クエリ オプティマイザーでは結合をより高速に処理できます。 クエリで、併置されていないか、または結合フィールドでパーティション分割されていない 2 つのテーブルを結合すると、パーティションが存在することが原因で、クエリ処理のパフォーマンスは向上せず、低下することがあります。

クエリ処理でのパーティション処理の詳細については、「パーティション テーブルとパーティション インデックスに対するクエリ処理の機能強化」を参照してください。

パーティション インデックス操作中の統計計算での動作の変更

SQL Server 2012 (11.x)以降では、パーティション インデックスが作成または再構築された場合、テーブル内のすべての行をスキャンして統計を作成することはできません。 代わりに、クエリ オプティマイザーが既定のサンプリング アルゴリズムを使用して統計を生成します。 パーティション インデックスでデータベースをアップグレードした後で、これらのインデックスのヒストグラム データに違いが見つかる場合があります。 この動作の変更はクエリ パフォーマンスに影響しない可能性があります。 テーブル内のすべての行をスキャンしてパーティション インデックスの統計を作成するには、FULLSCAN 句で CREATE STATISTICS または UPDATE STATISTICS を使用します。

タスク トピック
パーティション関数とパーティション構成の作成方法、およびそれらをテーブルおよびインデックスに適用する方法について説明します。 パーティション テーブルとパーティション インデックスの作成

次のホワイトペーパーには、パーティション テーブルおよびパーティション インデックスの戦略と有用な実装について記述されています。