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

  • [アーティクル]

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

SQL Server、データベースのAzure SQL、テーブルとインデックスのパーティション分割をサポートAzure SQL Managed Instance。 パーティション テーブルとインデックスのデータは、データベース内の複数のファイル グループに分散したり、1 つのファイル グループに格納したりできる単位に分割されます。 ファイル グループに複数のファイルが存在する場合、 比例フィル アルゴリズムを使用してファイル間でデータが分散されます。 行のグループが各パーティションにマップされるように、データは行方向にパーティション分割されます。 1 つのインデックスまたはテーブルのすべてのパーティションは、同じデータベース内に存在する必要があります。 データに対するクエリまたは更新の実行時は、テーブルやインデックスが 1 つの論理エンティティとして扱われます。

SQL Server 2016 (13.x) SP1 より前では、パーティション テーブルとパーティション インデックスは、SQL Server のすべてのエディションで使用できるわけではありません。 SQL Serverのエディションでサポートされている機能の一覧については、「SQL Server 2016 のエディションとサポートされる機能」を参照してください。 パーティション テーブルとパーティション インデックスは、Azure SQL Database とAzure SQL Managed Instanceのすべてのサービス レベルで使用できます。

テーブルパーティション分割は、Azure Synapse Analytics の専用 SQL プールでも使用でき、構文の違いがいくつかあります。 詳細については、「 専用 SQL プールでのテーブルのパーティション分割」を参照してください。

重要

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

パーティション分割のメリット

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

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

  • 1 つ以上のパーティションに対してメンテナンス操作またはデータ保持操作をより迅速に実行できます。 テーブル全体ではなく、これらのデータ サブセットのみを対象にできるので、操作がより効率化されます。 たとえば、1 つ以上のパーティションのデータを圧縮したり、インデックスの 1 つ以上のパーティションを再構築したり、単一のパーティション内のデータを切り捨てたりすることができます。 また、個々のパーティションを 1 つのテーブルからアーカイブ テーブルに切り替えることができます。

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

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

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

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

パーティション関数

パーティション関数は、パーティション分割列と呼ばれる特定の列の値に基づいて、テーブルまたはインデックスの行を一連のパーティションにマップ する方法を定義するデータベース オブジェクトです。 パーティション分割列の各値は、パーティション値を返すパーティション関数への入力です。

パーティション関数によって、テーブルに含まれるパーティションの数とパーティションの境界が定義されます。 たとえば、販売注文データを含むテーブルを指定すると、販売日などの datetime 列に基づいて、テーブルを 12 (月単位) のパーティションにパーティション分割できます。

範囲の種類 (LEFT または RIGHT) は、パーティション関数の境界値を結果のパーティションに格納する方法を指定します。

  • LEFT 範囲は、間隔値がデータベース エンジンによって左から右への昇順で並べ替えられるときに、境界値が境界値間隔の左側に属することを指定します。 言い換えると、境界の最大値はパーティション内に含まれます。
  • RIGHT 範囲は、間隔値がデータベース エンジンによって左から右の昇順で並べ替えられるときに、境界値が境界値間隔の右側に属することを指定します。 つまり、境界の最小値が各パーティションに含まれます。

LEFT または RIGHT が指定されていない場合は、LEFT 範囲が既定値です。

たとえば、次のパーティション関数は、テーブルまたはインデックスを 12 個のパーティションにパーティション分割します。 これは、datetime 列の 1 年分の値の月ごとに 1 つずつです。 RIGHT 範囲が使用され、境界値が各パーティションの下限値として機能することを示します。 right 範囲は、datetime または datetime2 データ型の列に基づいてテーブルをパーティション分割する場合に使用する方が簡単です。値が午前 0 時の行は、同じ日に後の値を持つ行と同じパーティションに格納されるためです。 同様に、 日付 のデータ型を使用し、1 か月以上のパーティションを使用する場合、RIGHT 範囲は月の最初の日をその月の後の日と同じパーティションに保持します。 これにより、1 日分のデータに対してクエリを実行するときのパーティションの正確な 削除 が可能になります。

CREATE PARTITION FUNCTION [myDateRangePF1] (datetime)  
AS RANGE RIGHT FOR VALUES ('2022-02-01', '2022-03-01', '2022-04-01',  
               '2022-05-01', '2022-06-01', '2022-07-01', '2022-08-01',   
               '2022-09-01', '2022-10-01', '2022-11-01', '2022-12-01');

次の表は、パーティション分割列 datecol で、このパーティション関数を使用するテーブルまたはインデックスがどのようにパーティション分割されるかを示します。 2 月 1 日は 関数で定義された最初の境界ポイントであるため、パーティション 2 の下限として機能します。

Partition 1 2 ... 11 12
値 datecol<2022-02-01 12:00AM datecol>= 2022-02-01 12:00AM AND datecol<2022-03-01 12:00AM datecol>= 2022-11-01 12:00AM AND col1<2022-12-01 12:00AM datecol>= 2022-12-01 12:00AM

RANGE LEFT と RANGE RIGHT の両方で、左端のパーティションのデータ型の最小値が下限として設定され、右端のパーティションのデータ型の最大値が上限になります。

LEFT および RIGHT パーティション関数のその他の例については、「 CREATE PARTITION FUNCTION (Transact-SQL)」を参照してください。

パーティション構成

パーティション構成は、パーティション関数のパーティションを 1 つのファイル グループまたは複数のファイル グループにマップするデータベース オブジェクトです。

CREATE PARTITION SCHEME (Transact-SQL) でパーティション 構成を作成する構文の例を見つけます。

ファイル グループ

パーティションを複数のファイル グループに配置する主な理由は、パーティションのバックアップと復元操作を個別に実行できるようにすることです。 これは、バックアップを個別のファイル グループで実行できるからです。 階層化ストレージを使用する場合、複数のファイル グループを使用すると、特定のパーティションを特定のストレージ層に割り当てることができます。たとえば、古いパーティションとアクセス頻度の低いパーティションを、低速でコストの低いストレージに配置できます。 パーティション分割のその他の利点もすべて、使用するファイル グループの数や特定のファイル グループのパーティション配置に関係なく利用できます。

パーティション テーブルのファイルとファイル グループを管理すると、時間の経過と同時に管理タスクが大幅に複雑になる可能性があります。 バックアップと復元の手順で複数のファイル グループを使用してもメリットがない場合は、すべてのパーティションに対して 1 つのファイル グループを使用することをお勧めします。 ファイルとファイル グループを設計するためのルールは、パーティション分割されていないオブジェクトに適用されるのと同じです。

注意

パーティション分割は、Azure SQL Database で完全にサポートされています。 Azure SQL Database ではファイル グループのみがPRIMARYサポートされているため、すべてのパーティションをファイル グループに配置するPRIMARY必要があります。

ALTER DATABASE (Transact-SQL) ファイルとファイル グループのオプションでSQL ServerとAzure SQL Managed Instanceのファイル グループを作成するコード例を見つけます。

パーティション分割列

パーティション関数が、テーブルまたはインデックスをパーティション分割するために使用するテーブルまたはインデックスの列。 パーティション分割列を選択する場合は、次の考慮事項が適用されます。

  • パーティション関数に含まれる計算列は、PERSISTED として明示的に作成する必要があります。
    • パーティション列として使用できる列は 1 つだけなので、複数の列と計算列を連結すると便利な場合があります。
  • インデックス キー列として使用するために有効なすべてのデータ型の列は、 タイムスタンプを除くパーティション分割列として使用できます。
  • ntext、text、image、xml、varchar(max)、nvarchar(max)、varbinary(max)など、ラージ オブジェクト (LOB) データ型の列を指定できません。
  • Microsoft .NET Framework共通言語ランタイム (CLR) のユーザー定義型と別名データ型の列を指定することはできません。

オブジェクトをパーティション分割するには、CREATE TABLE (Transact-SQL)、ALTER TABLE (Transact-SQL)、CREATE INDEX (Transact-SQL) ステートメントでパーティション構成とパーティション分割列を指定します。

非クラスター化インデックスを作成するときに、partition_scheme_nameまたはファイル グループが指定されておらず、テーブルがパーティション分割されている場合、インデックスは、基になるテーブルと同じパーティション分割列を使用して、同じパーティション構成に配置されます。 既存のインデックスのパーティション分割方法を変更するには、create INDEX と DROP_EXISTING 句を使用します。 これにより、パーティション分割されていないインデックスをパーティション分割したり、パーティションインデックスを非パーティション化したり、インデックスのパーティション構成を変更することができます。

固定されたインデックス

対応するテーブルと同じパーティション構成に基づいて構築されたインデックス。 テーブルとそのインデックスが配置されている場合、データベース エンジンは、テーブルとそのインデックスの両方のパーティション構造を維持しながら、テーブルの内外のパーティションを迅速かつ効率的に切り替えることができます。 インデックスをベース テーブルに配置するために、同じ名前付き パーティション関数 に参加する必要はありません。 ただし、インデックスとベース テーブルのパーティション関数が次の点で基本的に同じでなければなりません。

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

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

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

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

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

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

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

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

パーティションの解消

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

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

制限事項

  • パーティション関数および構成のスコープは、それが作成されたデータベースに制限されます。 データベース内では、パーティション関数は他の関数とは別の名前空間に配置されます。

  • パーティションテーブル内の行にパーティション分割列に NULL がある場合、これらの行は左端のパーティションに配置されます。 ただし、NULL が最初の境界値として指定され、パーティション関数定義で RANGE RIGHT が指定されている場合、左端のパーティションは空のままになり、NULL は 2 番目のパーティションに配置されます。

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

データベース エンジンは、テーブルまたはインデックスごとに最大 15,000 個のパーティションをサポートします。 ただし、1,000 を超えるパーティションを使用すると、メモリ、パーティション インデックス操作、DBCC コマンド、クエリに影響します。 このセクションでは、1,000 を超えるパーティションを使用した場合のパフォーマンスへの影響について説明し、必要に応じて回避策を提供します。

パーティション テーブルまたはインデックスごとに最大 15,000 個のパーティションを使用できるため、1 つのテーブルに長期間データを格納できます。 ただし、必要な期間だけデータを保持し、パフォーマンスとパーティション数のバランスを維持する必要があります。

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

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

メモリの制限は、データベース エンジンがパーティション インデックスを作成するパフォーマンスまたは機能に影響を与える可能性があります。 これは特に、テーブルにクラスター化インデックスが既に存在する場合、インデックスがベース テーブルに合わせられない場合、またはクラスター化インデックスと一致しない場合に当てられます。

SQL ServerとAzure SQL Managed Instanceでは、サーバー構成オプションをindex create memory (KB)増やすことができます。 詳細については、「 インデックス作成メモリサーバー構成オプションの構成」を参照してください。 Azure SQL Database の場合は、より多くのメモリを割り当てるために、Azure portal内のデータベースのサービス レベル目標を一時的または永続的に増やすことを検討してください。

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

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

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

データベース エンジンは、パーティション インデックスを作成するための並べ替えを実行するときに、まずパーティションごとに 1 つの並べ替えテーブルを作成します。 次に、各パーティションのそれぞれのファイル グループ、または SORT_IN_TEMPDB インデックス オプションが指定されている場合は tempdb で並べ替えテーブルが作成されます。 1 つの並べ替えテーブルを作成するために最低限必要なメモリの量が決まっています。 ベース テーブルに固定するパーティション インデックスを作成すると、並べ替えテーブルは一度に 1 つずつ作成されるのでメモリの消費を抑えることができます。 しかし、固定されないパーティション インデックスを作成すると、複数の並べ替えテーブルが同時に作成されます。 そのため、このように同時に並べ替えを行うには十分なメモリが必要です。 パーティションの数が多いと、必要なメモリも増えます。 1 つの並べ替えテーブル、つまりパーティションあたり最低必要なサイズは 40 ページ (1 ページは 8 KB) です。 たとえば、100 個のパーティションから構成される固定されないパーティション インデックスは、同時に 4,000 (40 * 100) ページを同時に並べ替えることができるメモリが必要です。 これだけのメモリを使用できれば、作成操作は成功しますがパフォーマンスが低下する場合があります。 これだけのメモリを使用できない場合、作成操作は失敗します。 一方、100 個のパーティションから構成される固定されたパーティション インデックスは、複数の並べ替えが同時に行われることがないので、40 ページを並べ替えることができるメモリがあれば十分です。

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

DBCC コマンド

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

クエリ

テーブルまたはインデックスをパーティション分割した後、パーティションの削除を使用するクエリは、多数のパーティションで同等または改善されたパフォーマンスを持つことができます。 パーティションの解消を使用しないクエリの場合、その実行にかかる時間は、パーティション数が増えるほど長くなります。

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

  • シナリオ 1 では、テーブルは列 に 1,000 個のパーティションに分割されます A。
  • シナリオ 2 では、テーブルが列 Aで 10,000 個のパーティションに分割されます

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

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

同様に、単一行シークまたは小さい範囲のスキャンを実行するクエリは、パーティションがある場合と同じ数のシークまたはスキャンを実行する必要があるため、クエリ述語にパーティション分割列が含まれていない場合、パーティションテーブル以外のテーブルに対する場合よりも時間がかかります。 このため、パーティション分割によって、このようなクエリが一般的な OLTP システムのパフォーマンスが向上することはめったにありません。

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

  • パーティション分割に使用するパラメーターの数が同数で、対応するパラメーターのデータ型が同じです。
  • 同数のパーティションが定義されている
  • パーティションに同じ境界値が定義されている

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

一部のクエリで を使用 $PARTITION すると便利な場合があります。 詳細については、「 $PARTITION (Transact-SQL)」を参照してください。

パーティション テーブルとインデックスの並列クエリ実行戦略や、その他のベスト プラクティスなど、クエリ処理でのパーティション処理の詳細については、「 パーティション テーブルとインデックスのクエリ処理の機能強化」を参照してください。

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

Azure SQL Database、Azure SQL Managed Instance、および SQL Server 2012 (11.x) 以降では、パーティション インデックスの作成時または再構築時にテーブル内のすべての行をスキャンしても統計は作成されません。 代わりに、クエリ オプティマイザーが既定のサンプリング アルゴリズムを使用して統計を生成します。

パーティション インデックスを持つデータベースを 2012 (11.x) より低いバージョンのSQL Serverからアップグレードすると、これらのインデックスのヒストグラム データに違いがある場合があります。 この動作の変更は、クエリのパフォーマンスに影響する可能性があります。 テーブル内のすべての行をスキャンしてパーティション インデックスの統計を作成するには、FULLSCAN 句で CREATE STATISTICS または UPDATE STATISTICS を使用します。

次のステップ

パーティション テーブルとインデックス戦略の詳細については、次の記事を参照してください。