次の方法で共有

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

  • [アーティクル]

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance

SQL Server、Azure SQL Database、および Azure SQL Managed Instance では、テーブルとインデックスのパーティション分割がサポートされています。 パーティション テーブルとパーティション インデックスのデータは、データベース内の複数のファイル グループに分散したり、単一のファイル グループに格納したりできるように、複数の単位に分割されます。 ファイル グループに複数のファイルが存在する場合、プロポーショナル フィル アルゴリズムを使用してデータをファイル全体に分散させます。 行のグループが各パーティションにマップされるように、データは行方向にパーティション分割されます。 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 のデータ型を使って、1 か月以上のパーティションを使用する場合、RIGHT 範囲では、月の最初の日がその月の 2 日目以降の日と同じパーティションに保持されます。 この機能は、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 の下限として機能します。

パーティション 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 つのファイル グループを使用することをお勧めします。 非パーティション オブジェクトに適用されるのと同じファイルとファイル グループのデザインに関する規則が、パーティション分割されたオブジェクトにも適用されます。

Note

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

SQL Server と Azure SQL Managed Instance のファイル グループを作成するサンプルコードについては、「ALTER DATABASE (Transact-SQL) File および Filegroup オプション」を参照してください。

パーティション分割列

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

  • パーティション関数に関与する計算列は、明示的に PERSISTED として作成されている必要があります。
    • パーティション列として使用できる列は 1 つだけであるため、場合によっては、複数の列と計算列を連結すると便利です。
  • timestamp 型を除き、インデックスキー列として使用できるすべてのデータ型の列をパーティション分割列として使用できます。
  • 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 または filegroup が指定されないまま、テーブルがパーティション分割された場合、インデックスは、基になるテーブルと同じパーティション分割列を使用して、同じパーティション構造に配置されます。 既存のインデックスのパーティション分割方法を変更するには、DROP_EXISTING 句を指定した CREATE INDEX を使用します。 これにより、非パーティション インデックスのパーティション分割、パーティション インデックスの非パーティション化、またはインデックスのパーティション構成の変更を行うことができます。

固定されたインデックス

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

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

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

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

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

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

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

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

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

パーティションの削除

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

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

制限事項

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

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

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

データベース エンジンは、テーブルまたはインデックスごとに最大 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) サーバー構成オプションを増やすことができます。 詳細については、「index create memory サーバー構成オプションの構成」を参照してください。 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 では、テーブルが列 A で 1,000 個のパーティションに分割されます。
  • シナリオ 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 を使用します。

関連するコンテンツ

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