適用対象:
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
SQL Server、Azure SQL Database、および Azure SQL Managed Instance では、テーブルとインデックスのパーティション分割がサポートされています。 パーティション テーブルとパーティション インデックスのデータは、データベース内の複数のファイル グループに分散されるか、1 つのファイル グループに格納される可能性がある単位に分割されます。 ファイル グループに複数のファイルが存在する場合、データは比例分散アルゴリズムを使用してファイル全体に分散されます。 行のグループが各パーティションにマップされるように、データは行方向にパーティション分割されます。 1 つのインデックスまたはテーブルのすべてのパーティションは、同じデータベース内に存在する必要があります。 データに対するクエリまたは更新の実行時は、テーブルやインデックスが 1 つの論理エンティティとして扱われます。
SQL Server 2016 (13.x) SP1 より前のバージョンでは、パーティション分割されたテーブルおよびインデックスは、すべてのエディションの SQL Server でサポートされていたわけではありませんでした。 SQL Server の各エディションでサポートされる機能の一覧については、「SQL Server 2022 の各エディションとサポートされている機能」を参照してください。 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 範囲です。
たとえば、次のパーティション関数では、テーブルまたはインデックスを、datetime 列の値が表す月ごとに 12 のパーティションに分割します。 RIGHT 範囲が使用されており、境界値が各パーティションの下限値として機能することを意味します。 datetime または datetime2 データ型の列に基づいてテーブルをパーティション分割する場合、RIGHT 範囲を使用すると扱いやすくなることがよくあります。これは、午前 0 時の値を持つ行が、同じ日のそれ以降の値を持つ行と同じパーティションに格納されるためです。 同様に、date のデータ型を使用し、月単位以上でパーティションを分割する場合でも、RIGHT 範囲を使用すれば、その月の 1 日が同じ月の他の日付と同じパーティションに含まれます。 この機能は、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 のいずれの場合も、最も左のパーティションの下限値はそのデータ型の最小値になり、最も右のパーティションの上限値はそのデータ型の最大値になります。
CREATE PARTITION FUNCTION で LEFT および RIGHT パーティション関数のその他の例を見つけます。
パーティション構成
パーティション構成は、パーティション関数の各パーティションを 1 つまたは複数のファイルグループに割り当てるデータベース オブジェクトです。
CREATE PARTITION SCHEME でパーティション構成を作成する構文の例を見つけます。
ファイル グループ
パーティションを複数のファイル グループに配置する主な理由は、パーティションのバックアップと復元操作を個別に実行できるようにすることです。 これは、バックアップを個別のファイル グループで実行できるからです。 階層型ストレージを使用する場合、複数のファイルグループを使用すると、特定のパーティションを特定のストレージ階層に割り当てることができます。たとえば、古くてアクセス頻度の低いパーティションを、速度が遅く低コストのストレージに配置できます。 パーティション分割のその他の利点もすべて、使用するファイル グループの数や特定のファイル グループのパーティション配置に関係なく利用できます。
パーティション テーブルのファイルとファイル グループを管理すると、時間の経過とともに管理タスクが大幅に複雑になる可能性があります。 バックアップや復元手順で複数のファイル グループを使用してもメリットがない場合は、すべてのパーティションに対して 1 つのファイル グループを使用することをお勧めします。 ファイルとファイル グループの設計に関する規則は、パーティション分割されたオブジェクトにも、パーティション分割されていないオブジェクトにも適用されます。
注
パーティション分割は、Azure SQL データベースで完全にはサポートされていません。 Azure SQL Database では PRIMARY ファイル グループのみがサポートされているため、すべてのパーティションを PRIMARY ファイル グループに配置する必要があります。
SQL Server と Azure SQL Managed Instance のファイル グループを作成するサンプルコードについては、ALTER DATABASE (Transact-SQL) のファイルおよびファイル グループのオプションに関する記事を参照してください。
パーティション分割列
パーティション関数が、テーブルまたはインデックスをパーティション分割するために使用するテーブルまたはインデックスの列。 パーティション列を選択する際には、次の点を考慮する必要があります。
- パーティション関数に使用する計算列は、明示的に PERSISTED として作成する必要があります。
- パーティション列として使用できるのは 1 列のみのため、場合によっては、複数の列を計算列で連結する方法が有効です。
- インデックス キー列として使用可能なすべてのデータ型の列をパーティション列として使用できますが、timestamp は除きます。
- ntext、text、image、xml、varchar(max)、nvarchar(max)、varbinary(max) などのラージ オブジェクト (LOB) データ型の列は指定できません。
- また、Microsoft .NET Framework 共通言語ランタイム (CLR) ユーザー定義型の列およびエイリアス データ型の列も指定できません。
オブジェクトをパーティション分割するには、 CREATE TABLE、 ALTER TABLE、CREATE INDEX ステートメントでパーティション構成とパーティション分割列 を 指定します。
非クラスター化インデックスを作成するときに、partition_scheme_name またはファイル グループが指定されておらず、テーブルがパーティション分割されている場合、そのインデックスは基になるテーブルと同じパーティション構成および同じパーティション列を使用して配置されます。 既存のインデックスのパーティション分割方法を変更するには、DROP_EXISTING 句を指定して、CREATE INDEX を実行します。 これにより、パーティション分割されていないインデックスをパーティション分割したり、パーティションインデックスを非パーティション化したり、インデックスのパーティション構成を変更することができます。
固定されたインデックス
対応するテーブルと同じパーティション構成に基づいて構築されたインデックス。 テーブルとインデックスが固定されている状態にある場合、データベース エンジンは、両者のパーティション構造を保ったまま、テーブルの内外のパーティションをすばやく効率的に切り替えることができます。 ベース テーブルに固定させるために、インデックスを同じ名前のパーティション関数に加える必要はありません。 ただし、インデックスとベース テーブルのパーティション関数が次の点で基本的に同じでなければなりません。
- パーティション関数の引数に同じデータ型が含まれている。
- 同数のパーティションが定義されている。
- パーティションに同じ境界値が定義されている。
クラスター化インデックスのパーティション分割
クラスター化インデックスをパーティション分割するときは、クラスター化キーにパーティション分割列を含める必要があります。 一意でないクラスター化インデックスをパーティション分割する際、パーティション列がクラスタリング キーに明示的に指定されていない場合、データベース エンジンは既定でパーティション列をクラスター化インデックス キーの一覧に追加します。 クラスター化インデックスが一意の場合は、クラスター化インデックス キーにパーティション分割列が含まれていることを明示的に指定する必要があります。 クラスター化インデックスとインデックス アーキテクチャの詳細については、「クラスター化インデックスのデザイン ガイドライン」を参照してください。
非クラスター化インデックスのパーティション分割
一意の非クラスター化インデックスをパーティション分割するときは、インデックス キーにパーティション分割列を含める必要があります。 一意でないクラスター化インデックスをパーティション分割する際、パーティション列がクラスタリング キーに明示的に指定されていない場合、データベース エンジンは既定でパーティション列をクラスター化インデックス キーの一覧に追加します。 パーティション列がすでにインデックスに含まれている場合、データベース エンジンはその列をインデックスに追加しません。 非クラスター化インデックスとインデックス アーキテクチャの詳細については、「非クラスター化インデックスのデザイン ガイドライン」を参照してください。
非整列インデックス
非整列インデックスは、対応するテーブルとは異なる方法でパーティション分割されます。 つまり、インデックスは、ベース テーブルとは別のファイル グループまたはファイル グループのセットに配置される、別のパーティション構成が設定されます。 非整列パーティション インデックスの設計は、次の場合に役立ちます。
- ベース テーブルがパーティション分割されていない。
- インデックス キーが一意であり、テーブルのパーティション列を含んでいない。
- 異なる結合列を使用して多くのテーブルが併置されている結合にベース テーブルを加える。
パーティションの解消
クエリ オプティマイザーがクエリのフィルター条件を満たすために、関連するパーティションのみにアクセスするときに使用されるプロセス。
パーティションの削除および関連する概念の詳細については、「パーティション テーブルとパーティション インデックスに対するクエリ処理の機能強化」を参照してください。
制限事項
パーティション関数および構成のスコープは、それが作成されたデータベースに制限されます。 データベース内では、パーティション関数は他の関数とは別の名前空間に配置されます。
パーティション分割されたテーブル内で、いずれかの行のパーティション列に NULL を格納されている場合、これらの行は最も左のパーティションに配置されます。 ただし、最初の境界値として NULL 値が指定され、パーティション関数の定義で RANGE RIGHT が指定されている場合、左端のパーティションは空のままとなり、2 番目のパーティションに NULL 値が配置されます。
パフォーマンスに関するガイドライン
データベース エンジンは、1 つのテーブルまたはインデックスにつき最大 15,000 個のパーティションをサポートします。 ただし、1,000 を超えるパーティションを使用すると、メモリ使用量、パーティション インデックスの操作、DBCC コマンド、およびクエリに影響が生じます。 このセクションでは、1,000 個を超えるパーティションを使用した場合のパフォーマンスへの影響と、必要に応じた回避策について説明します。
1 つのパーティション分割されたテーブルまたはインデックスに最大 15,000 個のパーティションを使用できるため、長期間のデータを単一のテーブルに格納できます。 ただし、データの保持期間は必要最小限とし、パフォーマンスとパーティション数とのバランスをとる必要があります。
メモリ使用量とガイドライン
使用するパーティション数が多い場合は、16 GB 以上の RAM を使用することをお勧めします。 システムに十分なメモリがない場合は、メモリ不足のため、データ操作言語 (DML) ステートメント、データ定義言語 (DDL) ステートメント、およびその他の操作が失敗する可能性があります。 多数のメモリ集中型プロセスを実行する RAM が 16 GB のシステムでは、多数のパーティションで実行される操作でメモリが不足する可能性があります。 したがって、メモリを 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) の次数が多いと、メモリの要件も高くなるためです。 たとえば、データベース エンジンが並列処理の次数を 4 に設定すると、100 個のパーティションから構成される固定されないパーティション インデックスは、同時に 4 基のプロセッサで 4,000 ページをソートできるだけのメモリ、つまり合計 16,000 ページ分のメモリが必要になります。 パーティション インデックスが固定されている場合、4 基のプロセッサで 40 ページを並べ替えるため、メモリの要件は 160 (4 * 40) ページまで下がります。 MAXDOP インデックス オプションを使用して、手動で並列処理の次数を減らすことができます。
DBCC コマンド
パーティション数が多い場合、DBCC CHECKDB や DBCC CHECKTABLE などの DBCC コマンドの実行にかかる時間は、パーティション数が増えるほど長くなります。
クエリ
テーブルまたはインデックスをパーティション分割した後では、パーティションの解消を使用するクエリは、パーティション数が多くなると、それに応じてパフォーマンスが向上する可能性があります。 パーティションの解消を使用しないクエリの場合、その実行にかかる時間は、パーティション数が増えるほど長くなります。
たとえば、テーブルの行数が 10 億で、 A、 B、および Cの列があるとします。
- シナリオ 1 では、テーブルが列
Aで 1,000 個のパーティションに分割されます - シナリオ 2 では、テーブルが列
Aで 10,000 個のパーティションに分割されます
列 A でフィルタリングする WHERE 句を持つテーブルでのクエリは、パーティションの解消を実行し、1 つのパーティションをスキャンします。 パーティション内でスキャンする行が少ないほど、シナリオ 2 でも同じクエリの実行速度が速くなる可能性があります。 列 B でフィルタリングする WHERE 句を持つクエリは、すべてのパーティションをスキャンします。 スキャンするパーティションが少ないため、シナリオ 1 ではシナリオ 2 よりもクエリの実行速度が速くなる可能性があります。
パーティション分割列以外の列で TOP や MAX/MIN などの演算子を使用するクエリでは、すべてのパーティションを評価する必要があるため、パーティション分割のパフォーマンスが低下する可能性があります。
同様に、単一行シークまたは小さい範囲のスキャンを実行するクエリは、パーティション分割されたテーブルに対して、パーティション分割列が含まれていない場合よりも長い時間がかかります。これは、パーティションがある場合と同じ数のシークまたはスキャンを実行する必要があるためです。 このため、こうしたクエリが一般的に使用される OLTP システムにおいては、パーティション分割によってパフォーマンスが向上することはほとんどありません。
2 つ以上のパーティション テーブル間での等結合を行うクエリを頻繁に実行する場合、それらのテーブルのパーティション分割列は、テーブルの結合先の列と同じにする必要があります。 また、等結合するテーブルまたはテーブルのインデックスを併置する必要があります。 つまり、これらのテーブルでは、同じ名前のパーティション関数または名前は異なるが実質的には同じ関数のいずれかが使用されることになります。後者の場合、関数には次のような性質があります。
- パーティション分割に使用するパラメーターの数が同数で、対応するパラメーターのデータ型が同じです。
- 同数のパーティションが定義されている
- パーティションに同じ境界値が定義されている
このような性質により、パーティション自体を結合できるので、クエリ オプティマイザーでは結合をより高速に処理できます。 クエリが結合フィールドに併置されていないテーブルまたはパーティション分割されていない 2 つのテーブルを結合する場合、パーティションが存在すると、クエリ処理が高速化されるのではなく、実際に速度が低下する可能性があります。
一部のクエリで $PARTITION を使用すると便利な場合があります。 詳細については、 $PARTITIONを参照してください。
パーティション テーブルとパーティション インデックスの並列クエリ実行戦略、その他のベスト プラクティスなど、クエリ処理でのパーティション処理の詳細については、「 パーティション テーブルとインデックスに対するクエリ処理の機能強化」を参照してください。
パーティション インデックス操作中の統計計算での動作の変更
Azure SQL Database、Azure SQL Managed Instance、および SQL Server 2012 (11.x) 以降では、パーティション インデックスが作成または再構築された場合、テーブル内のすべての行をスキャンして統計を作成することはできません。 代わりに、クエリ オプティマイザーが既定のサンプリング アルゴリズムを使用して統計を生成します。
パーティション インデックスを持つデータベースを 2012 (11.x) より前のバージョンの SQL Server からアップグレードすると、これらのインデックスのヒストグラム データに違いがあることに気付く場合があります。 この動作の変更は、クエリのパフォーマンスに影響する可能性があります。 テーブル内のすべての行をスキャンしてパーティション インデックスの統計を作成するには、FULLSCAN 句で CREATE STATISTICS または UPDATE STATISTICS を使用します。
関連するコンテンツ
- パーティション テーブルとパーティション インデックスの作成
- $PARTITION (Transact-SQL)
- Azure SQL Database によるスケールアウト
- 専用 SQL プールでのテーブルのパーティション分割
- SQL Server と Azure SQL のインデックスのアーキテクチャとデザイン ガイド
- SQL Server 2008 を使用したパーティション テーブルとパーティション インデックス
- 自動スライディング ウィンドウを実装する方法
- パーティション テーブルの一括読み込み
- パーティション テーブルとパーティション インデックスに対するクエリ処理の機能強化
- 大規模なリレーショナル データ ウェアハウスを構築するためのベスト プラクティストップ 10