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

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

重要
SQL Server 2012 では、既定で最大 15,000 のパーティションをサポートします。 以前のバージョンでは、パーティションの数は既定で 1,000 に制限されていました。 x86 ベースのシステムでは、パーティション数が 1,000 を超えるテーブルまたはインデックスを作成できますが、サポートされていません。

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

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

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

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

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

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

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

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

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

• パーティション関数
  テーブルまたはインデックスの行を、パーティション分割列と呼ばれる特定の列の値に基づいて、一連のパーティションにマップする方法を定義するデータベース オブジェクト。 つまり、テーブルのパーティションの数とパーティションの境界の定義方法は、パーティション関数によって定義されます。 たとえば、販売注文データを格納するテーブルの場合、販売日などの 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) ステートメント、およびその他の処理においてメモリ不足によるエラーが発生する場合があります。 16 GB の RAM を搭載したシステムでメモリを集中的に使用するプロセスが多数実行される場合は、多数のパーティションで実行される操作でメモリが不足する可能性があります。 したがって、メモリを 16 GB よりも大きくするほど、パフォーマンスとメモリの問題が少なくなります。

SQL Server でパーティション インデックスを作成するパフォーマンスは、メモリにより制限される場合があります。 テーブルに既にクラスター化インデックスが適用されている場合、パーティション インデックスがベース テーブルまたはクラスター化インデックスに固定されていないとメモリによる制限を特に受けます。

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

SQL Server でパーティション インデックスを作成するパフォーマンスは、メモリにより制限される場合があります。 固定されていないインデックスの場合は、特に影響を受けます。 固定されていないインデックスをパーティションが 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 億で、A、B、および C の列があるとします。 シナリオ 1 では、テーブルが列 A で 1,000 個のパーティションに分かれています。 シナリオ 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 を使用したパーティション テーブルとパーティション インデックス

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

• パーティション テーブルの一括読み込み

• プロジェクト REAL: データ ライフ サイクル - パーティション分割

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

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