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

適用対象:yesSQL Server (サポートされているすべてのバージョン) YesAzure SQL Database YesAzure 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 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 つのパーティション内のデータを切り捨てたりすることができます。 また、個々のパーティションを 1 つのテーブルからアーカイブ テーブルに切り替えることができます。

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

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

コンポーネントと概念

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

パーティション関数

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

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

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

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

LEFT または RIGHT が指定されていない場合は、LEFT 範囲がデフォルトです。

たとえば、次のパーティション関数では、テーブルまたはインデックスを 12 個のパーティションにパーティション分割します。 datetime 列の年分の値の月ごとに 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 の両方で、左端のパーティションはデータ型の最小値を下限とし、右端のパーティションはデータ型の最大値を上限として持っています。

CREATE PARTITION FUNCTION (Transact-SQL) で、LEFT および RIGHT パーティション関数の他の例を見つけます。

パーティション構成

パーティション構成は、パーティション関数のパーティションを 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 として明示的に作成する必要があります。
  • インデックス キー列として使用するために有効なすべてのデータ型の列は、 タイムスタンプを除き、パーティション分割列として使用できます。
  • ntexttextimagexmlvarchar(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またはファイル グループが指定されておらず、テーブルがパーティション分割されている場合、インデックスは、基になるテーブルと同じパーティション分割列を使用して、同じパーティション構成に配置されます。 既存のインデックスのパーティション分割方法を変更するには、DROP_EXISTING 句で CREATE INDEX を 使用します。 これにより、パーティション分割されていないインデックスのパーティション分割、パーティション分割されていないインデックスの作成、またはインデックスのパーティション構成の変更を行うことができます。

固定されたインデックス

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

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

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

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

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

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

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

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

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

パーティションの解消

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

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

制限事項

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

  • パーティションテーブル内の行にパーティション分割列に NULL がある場合、これらの行は左端のパーティションに配置されます。 ただし、最初の境界値として NULL を指定し、パーティション関数定義で RANGE RIGHT を指定すると、左端のパーティションは空のままになり、NUL は 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 CHECKDBDBCC CHECKTABLE などの DBCC コマンドは、パーティションの数が増えるにつれて実行に時間がかかる可能性があります。

クエリ

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

たとえば、テーブルの行数が 10 億で、 AB、および 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 を使用します。

次のステップ

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