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

SQL Server では、テーブルとインデックスのパーティション分割がサポートされています。 パーティション テーブルとパーティション インデックスのデータは、データベース内の複数のファイル グループに分散できるように、複数の単位に分割されます。 データは水平方向にパーティション分割されるため、行のグループは個々のパーティションにマップされます。 1 つのインデックスまたはテーブルのすべてのパーティションは、同じデータベースに存在する必要があります。 データに対してクエリまたは更新が実行されると、テーブルまたはインデックスは単一の論理エンティティとして扱われます。 パーティション テーブルとパーティション インデックスは、MicrosoftSQL Server のすべてのエディションで使用できるわけではありません。 SQL Server の各エディションでサポートされる機能の一覧については、「SQL Server 2014 の各エディションでサポートされる機能」を参照してください。

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

大きなテーブルまたはインデックスをパーティション分割すると、管理性とパフォーマンスに次のような利点があります。

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

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

• 頻繁に実行するクエリの種類とハードウェア構成に基づいて、クエリのパフォーマンスを向上させることができます。 たとえば、パーティション自体を結合できるため、テーブル内のパーティション分割列が同じである場合、クエリ オプティマイザーは複数のパーティション テーブル間の等結合クエリをより高速に処理できます。

  SQL Server は、I/O 操作に対してデータの並べ替えを実行すると、最初にパーティションごとにデータを並べ替えます。 SQL Server は一度に 1 つのドライブにアクセスするため、パフォーマンスが低下する可能性があります。 データの並べ替えのパフォーマンスを向上させるには、RAID を設定して、パーティションのデータ ファイルを複数のディスクにストライピングします。 この方法では、SQL Server は引き続きパーティションごとにデータを並べ替えますが、各パーティションのすべてのドライブに同時にアクセスできます。

  さらに、テーブル全体ではなくパーティション レベルでロックエスカレーションを有効にすることで、パフォーマンスを向上させることができます。 これにより、テーブルのロックの競合を減らすことができます。

コンポーネントと概念

次の用語は、テーブルとインデックスのパーティション分割に適用されます。

Partition 関数
パーティション分割列と呼ばれる特定の列の値に基づいて、テーブルまたはインデックスの行を一連のパーティションにマップする方法を定義するデータベース オブジェクト。 つまり、パーティション関数は、テーブルに含まれるパーティションの数と、パーティションの境界を定義する方法を定義します。 たとえば、販売注文データを含むテーブルの場合、販売日などの datetime 列に基づいて、テーブルを 12 個 (月単位) のパーティションにパーティション分割できます。

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

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

インデックスの配置
対応するテーブルと同じパーティション構成に基づいて構築されたインデックス。 テーブルとそのインデックスが配置されている場合、SQL Server では、テーブルとそのインデックスの両方のパーティション構造を維持しながら、パーティションをすばやく効率的に切り替えることができます。 インデックスをベース テーブルに配置するために、同じ名前付きパーティション関数に参加する必要はありません。 ただし、インデックスとベース テーブルのパーティション関数は基本的に同じである必要があります。その 1) パーティション関数の引数は同じデータ型を持ち、2) 同じ数のパーティションを定義し、3) パーティションに対して同じ境界値を定義します。

非整列インデックス
対応するテーブルとは独立してパーティション分割されたインデックス。 つまり、インデックスのパーティション構成が異なるか、ベース テーブルとは別のファイル グループに配置されます。 非整列パーティション インデックスの設計は、次の場合に役立ちます。

• ベース テーブルがパーティション分割されていません。

• インデックス キーは一意であり、テーブルのパーティション分割列は含まれません。

• 異なる結合列を使用して、より多くのテーブルを含む併置結合にベース テーブルを参加させる必要があります。

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

パフォーマンス ガイドライン

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

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

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

メモリの制限は、SQL Server でパーティション インデックスを構築するパフォーマンスまたは機能に影響を与える可能性があります。 これは特に、テーブルにクラスター化インデックスが既に適用されている場合、インデックスがベース テーブルと一致しない場合、またはクラスター化インデックスと一致していない場合に発生します。

パーティション化されたインデックスの操作

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

パーティションの数が増えると、アラインインデックスの作成と再構築の実行に時間がかかる場合があります。 パフォーマンスとメモリの問題が発生する可能性があるため、複数のインデックス作成コマンドと再構築コマンドを同時に実行しないことをお勧めします。

SQL Server は、パーティション インデックスを作成するための並べ替えを実行するときに、まずパーティションごとに 1 つの並べ替えテーブルを作成します。 その後、SORT_IN_TEMPDBインデックス オプションが指定されている場合は、各パーティションのそれぞれのファイル グループまたは tempdbで並べ替えテーブルがビルドされます。 各並べ替えテーブルには、ビルドするメモリの最小量が必要です。 ベース テーブルに合わせてパーティション インデックスを作成する場合、並べ替えテーブルは一度に 1 つずつ作成され、メモリが少なくなります。 ただし、配置されていないパーティション インデックスを作成する場合、並べ替えテーブルは同時に作成されます。 その結果、これらの同時並べ替えを処理するのに十分なメモリが必要です。 パーティションの数が多いほど、必要なメモリが増えます。 各パーティションの並べ替えテーブルごとの最小サイズは 40 ページで、1 ページあたり 8 KB です。 たとえば、パーティションが 100 個の非アライメント パーティション インデックスでは、4,000 (40 * 100) ページを同時に順次並べ替えるのに十分なメモリが必要です。 このメモリが使用可能な場合、ビルド操作は成功しますが、パフォーマンスが低下する可能性があります。 このメモリを使用できない場合、ビルド操作は失敗します。 また、100 個のパーティションを持つアラインされたパーティション インデックスでは、並べ替えが同時に実行されないため、40 ページを並べ替えるのに十分なメモリのみが必要です。

アラインインデックスと非アラインインデックスの両方で、SQL Server がマルチプロセッサ コンピューター上のビルド操作に並列処理の程度を適用している場合、メモリ要件が大きくなる可能性があります。 これは、並列処理の次数が大きいほど、メモリ要件が大きくなるためです。 たとえば、SQL Server が並列処理の次数を 4 に設定した場合、パーティションが 100 個の非アライメント パーティション インデックスでは、4 つのプロセッサが同時に 4,000 ページ、つまり 16,000 ページを並べ替えるのに十分なメモリが必要です。 パーティション インデックスがアラインされている場合、メモリ要件は、40 ページまたは 160 (4 * 40) ページを並べ替える 4 つのプロセッサに減ります。 MAXDOP インデックス オプションを使用すると、並列処理の次数を手動で減らすことができます。

DBCC コマンド

パーティションの数が多い場合、DBCC コマンドはパーティションの数が増えるにつれて実行に時間がかかる場合があります。

クエリ

パーティションの削除を使用するクエリでは、パーティションの数が多いほどパフォーマンスが同等または向上する可能性があります。 パーティションの削除を使用しないクエリは、パーティションの数が増えるにつれて実行に時間がかかる場合があります。

たとえば、テーブルに 1 億個の行と列 A、 B、 Cがあるとします。 シナリオ 1 では、テーブルは列 Aで 1000 個のパーティションに分割されます。 シナリオ 2 では、テーブルは列 Aで 10,000 個のパーティションに分割されます。 列 A に対して WHERE 句をフィルター処理するテーブルに対するクエリは、パーティションの削除を実行し、1 つのパーティションをスキャンします。 パーティション内でスキャンする行が少ないほど、シナリオ 2 でも同じクエリの実行速度が速くなる可能性があります。 列 B で WHERE 句をフィルター処理するクエリでは、すべてのパーティションがスキャンされます。 スキャンするパーティションが少ないため、シナリオ 1 ではシナリオ 2 よりもクエリの実行速度が速くなる可能性があります。

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

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

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

関連タスク

関連コンテンツ

パーティション テーブルとインデックスの戦略と実装に関する次のホワイト ペーパーが役立つ場合があります。

• SQL Server 2008 を使用したパーティション テーブルとインデックスの戦略

• 自動スライディング ウィンドウを実装する方法

• パーティショニングされたテーブルへの一括ロード

• パーティション テーブルとパーティション インデックスに対するクエリ処理の機能強化

• 大規模なリレーショナル データ ウェアハウスを構築するためのベスト プラクティストップ 10