• 戻る（SQL Server）
  • SQL Server パーティション分割
  • （分散・冗長化）RAID / SQL Server のファイルの配置 / SQL Server のファイル・グループ
  • （運用関連）SQL Server の管理 > ログ > プロファイラ、トレース / SQL Server データ圧縮 / SQL Server のバックアップ

主に SQL Server パーティション分割の効果について説明する。

• 構成

  • 「パーティション分割」は、テーブル上の特定の列を「パーティション分割列」として
    指定し、この列の範囲をキーにして、行データを特定の
    「ファイル・グループ」にマップされる
    「パーティション」に振り分ける機能である。

  • なお、「パーティション分割」後の、テーブル・インデックスを、
    「パーティション テーブル」と「パーティション インデックス」と呼ぶ。

    • 「パーティション テーブル」と「パーティション インデックス」は
      1 つの論理エンティティとして扱われる。
    • 標準的なテーブル・インデックスの設計とクエリに関連する、
      すべてのプロパティと機能がサポートされる。
    • これにより、エンティティ全体のデータの整合性を維持しながら、
      グループ化されたデータ サブセットに対するアクセス性能の向上、
      管理の効率化を図ることができる。

  • なお、SQL Server では、1 つのテーブルに最大 1000 個の
    「パーティション」を作成できる。

• 効果

  • 主に運用系性能（インデックスのデフラグ・再構築、データのアーカイブ）の向上が可能。
  • 一部データ アクセス性能（並列クエリ、
    スキャン局所化、テーブル結合、ロック局所化）も改善する。
  • インスタンスが分割できるような場合は、インスタンス分割でも良い
    ---> シャーディング（Elastic Scale, Elastic Database Pool）。

補足（最新化：上限とエディション）:

• パーティション数の上限は、SQL Server 2012 以降 15,000 個
  （SQL Server 2008 R2 までは 1,000 個）。
• SQL Server 2016 SP1 以降、Standard Edition でも利用可能
  （それ以前は Enterprise / Developer 限定）。

補足（誤解されやすい点：単体では速くならない）: パーティション分割は
「インデックスの代わり」ではない。

単一テーブルへの検索を速くしたいだけなら、
SQL Server のインデックスを見直すほうが
効果が大きく、副作用も小さい。
本ページ自身が「主に運用系性能の向上」と書いているとおり、
パーティション分割の主目的は以下である。

目的	内容
アーカイブ / パージ	SWITCH によるメタデータ操作だけで、大量データを瞬時に切り離す
メンテナンスの局所化	パーティション単位でインデックス再構築・統計更新・バックアップ
階層化	古いパーティションだけ圧縮する／読み取り専用にする
ロックの局所化	LOCK_ESCALATION = AUTO でテーブル全体を止めない

逆に、後述の「検索性能が劣化するケース」のとおり、
パーティション分割列を検索条件に含めないクエリはむしろ遅くなる。

主に、日付などの論理的にグループ化された
データ サブセットを管理するのに適切であるかどうかによって決定される。

「パーティション関数」により指定される。

以下のデータ型を除くインデックス キーとして使用できるデータ型の列を使用できる。

• timestamp 型
• ntext 型
• text 型
• image 型
• xml 型
• varchar(max) 型
• nvarchar(max) 型
• varbinary(max) 型
• CLR ユーザ定義データ型
• 別名データ（エイリアス データ）型

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
  AS RANGE RIGHT FOR VALUES ( [ boundary_value [ ,...n ] ] );

上記の「パーティション関数」（partition_function_name）では、
指定の型（input_parameter_type）の「パーティション分割列」に
格納された値に基づき「パーティション分割」を行う。

なお、RANGE には LEFT より、RIGHT を指定することが推奨される。

CREATE PARTITION FUNCTION partition_function_name ( int )
  AS RANGE RIGHT FOR VALUES ( 100, 200, 300 );

と指定した場合、この「パーティション関数」によって、

• パーティション 1 : 列の値 < 100
• パーティション 2 : 100 <= 列の値 < 200
• パーティション 3 : 200 <= 列の値 < 300
• パーティション 4 : 300 <= 列の値

の 4 つの「パーティション」に分割される。

補足（なぜ RANGE RIGHT が推奨されるのか）: 境界値が
右側（次の）パーティションに含まれるため、
日付での分割で境界を直感的に書けるからである。

-- RANGE RIGHT なら「その月の 1 日」を境界に書ける
CREATE PARTITION FUNCTION pf_Monthly (date)
AS RANGE RIGHT FOR VALUES ('2026-01-01', '2026-02-01', '2026-03-01');
-- P2 = 2026-01-01 00:00:00.000 以上 2026-02-01 未満

RANGE LEFT だと「1 月の最後の瞬間」を境界にする必要があり、
datetime の精度（3.33ms 刻み）まで意識しなければならず事故が起きやすい。

双方の関係は以下のようになる。

• 「パーティション」は「ファイル・グループ」を跨ぐことはできない。
• 1 つの「パーティション」を、1 つの「ファイル・グループ」にマップする。
• いくつかの「パーティション」を、1 つの「ファイル・グループ」にマップする。
• すべての「パーティション」を、1 つの「ファイル・グループ」にマップする。

「パーティション関数」の「パーティション分割」で指定された「パーティション」と、
「ファイル・グループ」のマップを指定して、
「パーティション構成」を定義する。

パーティション構成の定義：

CREATE PARTITION SCHEME partition_schema_name
  AS PARTITION partition_function_name
    TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] );

上記の「パーティション構成」の定義では、

• 「パーティション関数」で分割した「パーティション」を、
• リストに指定した「ファイル・グループ」の順にマップする。

※ 「パーティション構成」で使用できる「パーティション関数」は 1 つのみ。
※ 1 つの「パーティション関数」は、複数の「パーティション構成」で使用できる。

「パーティション構成」と「パーティション分割列」を指定し、
「パーティション テーブル」を作成する。

パーティション テーブルの定義：

CREATE TABLE table_name(
<column_definition>[ ,...n ])
  ON partition_schema_name (div_column_name);

「パーティション構成」に「パーティション分割列」を指定し、
「パーティション テーブル」に「固定」された、
「パーティション インデックス」を作成する。

パーティション インデックスの定義：

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
  ON table_name (column_name)
    ON partition_schema_name (div_column_name);

移行メモ（正誤）: 元ページのパーティション インデックスの構文は
ON table_name (column_name); と行末にセミコロンが入っており、
続く ON partition_schema_name ... が構文上つながらない状態だった。
セミコロンを削除して修正した。

SELECT f.name, r.value, *
  FROM sys.partition_range_values r
    INNER JOIN sys.partition_functions f
      ON r.function_id = f.function_id

SELECT
  ps.name As [パーティション構成名],
  ds.name As [ファイル グループ名],
  dds.destination_id As [パーティション番号],
  *
FROM
  sys.destination_data_spaces dds
    INNER JOIN sys.partition_schemes ps
      ON dds.partition_scheme_id = ps.data_space_id
    INNER JOIN sys.data_spaces ds
      ON dds.data_space_id = ds.data_space_id
ORDER BY
  partition_scheme_id

• partition_function_name：「パーティション関数」
• expression：値（＝パーティション分割列を指定する）

SELECT
  *, $PARTITION.partition_function_name(expression) As [パーティション番号]
FROM
  table_name

若しくは、

SELECT
  $PARTITION.partition_function_name(expression) As [ﾊﾟｰﾃｨｼｮﾝ番号] ,
  COUNT(*) As [行数]
FROM
  table_name
GROUP BY
  $PARTITION.partition_function_name(expression)

補足（行数はメタデータから取れる）: 上記の COUNT(*) は
テーブル全体をスキャンするため、大規模テーブルでは重い。
パーティションごとの行数だけなら、カタログ ビューから即座に取得できる。

SELECT
    OBJECT_NAME(p.object_id) AS table_name,
    i.name                   AS index_name,
    p.partition_number, p.rows,
    fg.name                  AS filegroup_name,
    p.data_compression_desc
FROM sys.partitions AS p
JOIN sys.indexes AS i
  ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units AS au
  ON au.container_id = p.hobt_id
JOIN sys.filegroups AS fg
  ON fg.data_space_id = au.data_space_id
WHERE p.object_id = OBJECT_ID('dbo.table_name')
  AND i.index_id IN (0, 1)
ORDER BY p.partition_number;

ファイル・グループの性能向上

• 「簡易ストライピング」
• 「並列クエリ」

に加え、以下の性能向上を図ることができる。

「パーティション」毎に

• スキップ スキャン操作の効果により、スキャンを局所化（検索処理の高速化）

• （エスカレーション時）ロックを局所化（更新処理の同時実行性の向上）

• 併置結合によるテーブル結合を実行（テーブル結合処理の高速化）
  「パーティション インデックス」を「パーティション テーブル」に
  「固定」する必要がある。

• 「パーティション分割列」を検索条件に追加した場合、

  • スキップ スキャンの効果により、範囲スキャン検索の性能が向上する。
  • ロック局所化の効果により、更新処理の同時実行性が向上する。

• 備考
  スキップ スキャン、ロック局所化は「適切に設計された」
  OLTP アプリケーションを大きく性能向上させるものではない。

  • スキップ スキャン
    「パーティション インデックス」を「パーティション テーブル」に
    「固定」する必要はない。

  • ロック局所化

    • データベース エンジンが、ロック エスカレーションが必要であると判断する場合、
      行ロック・キー範囲ロックをページ ロックではなく、
      テーブル ロックに直接エスカレートする。
    • 同様に、ページ ロックは常にテーブル ロックにエスカレートされる。
    • しかし、SQL Server 2008 から、「パーティション テーブル」のロックについては、
      テーブル レベルではなく、ヒープまたは B ツリー（HoBT）レベルの
      ロック エスカレーションに留めることで、
      ロック待ちを少なくし、同時実行性を向上できるようになった。

補足（HoBT レベルのエスカレーションは既定ではない）: この機能を使うには、
テーブルに明示的な設定が必要である。

ALTER TABLE dbo.PartitionedTable SET (LOCK_ESCALATION = AUTO);

既定は TABLE のままなので、パーティション分割しただけでは
ロック局所化の恩恵は受けられない
（SQL Server のロックのエスカレーション参照）。

補足（「スキップ スキャン」は現在「パーティションの除外」）: 現在の
ドキュメントでは **partition elimination（パーティションの除外）**と呼ばれる。
実行プランの演算子のプロパティに
Actual Partition Count / Partitions Accessed が表示され、
実際にいくつのパーティションが読まれたかを確認できる
（実行プランのグラフィカル表示）。

• 「併置結合」は、同じ「パーティション構成」の 2 つのテーブルを、
  「パーティション分割列」をキーにして結合する際に発生する
  （結合に使用する「パーティション分割列」に
  「パーティション インデックス」を付与しておく）。

• この場合にオプティマイザが生成する「併置結合」の実行プランは、
  「パーティション」毎、「並列処理」で結合されるため、
  メモリを節約し、処理時間が短縮される。

パーティション分割によって、クエリ性能が劣化するケースもあるもよう。

• パーティショニング実施時にパーティションキーの有無による検索効率の違い - SE の雑記

  パーティショニングによって読み取り数（アクセスされたパーティション数）が
  変わってくる（大きくなる）ケースがある。
  ただし、パーティションキーを含めた検索では、読み取り数は大きくならないとなっている。

補足（なぜ劣化するのか）: パーティション分割すると、
インデックスもパーティションごとの B ツリーに分割される。
このため、パーティション分割列を検索条件に含まないクエリでは、
全パーティションの B ツリーをそれぞれ辿る必要があり、
分割していない場合より読み取り数が増える。

「日付でパーティション分割したが、
主要な検索は顧客 ID で行われる」といった設計は典型的な失敗例。
主要クエリの WHERE にパーティション分割列が入るかを
必ず先に確認すること。

• パーティション テーブルとパーティション インデックスに対するクエリ処理の機能強化
  https://learn.microsoft.com/ja-jp/sql/relational-databases/partitions/partitioned-tables-and-indexes

  パーティション テーブルとパーティション インデックスに対するクエリの実行プランは、
  Transact-SQL の SET SHOWPLAN_XML または SET STATISTICS XML を使用するか、
  SSMSのグラフィカル実行プラン出力を使用して調べることができる。

メンテナンス

• テーブル データのアーカイブ
• インデックスの断片化の局所化、再構築・最適化

が、「パーティション」毎に可能となり、

• 保守・運用中のデータ管理タスクの性能が向上したり、より簡単になったりする。
• これらの機能は、24 時間止められないシステムなどで特に有効となる。

適切に「パーティション分割」を行えば、下記のような、
古いデータを順次アーカイブする「スライディング ウインドウ」と呼ばれる操作が可能である。

• スイッチ機能を使用（例：稼働テーブルからアーカイブ・テーブルへスイッチ）。
• スイッチ機能は、内部的なポインタ変更のみで完了するので、高速な処理が可能である。

「スライディング ウインドウ」操作は、次の手順で行われる。

• 「データベース スキーマ」に

  • 稼動テーブル・アーカイブ テーブルの
    ・「パーティション テーブル」
    ・「パーティション インデックス」
    を同じ「パーティション構成」で作成。

  • 次に（アーカイブのために）
    新規作成する「パーティション」で使用する、
    新規「ファイル・グループ」を追加。

• 稼動テーブル・アーカイブ テーブルに

  • 新規「パーティション」で使用する「ファイル・グループ」を指定。
  • 「パーティション」境界を追加し、新規「パーティション」を分割作成。

• 稼動テーブルからアーカイブ テーブルに、
  最も古い「パーティション」のデータをスイッチ。

  • 稼動テーブルへ、スイッチした「パーティション」が対象となる
    データ挿入を禁止する制約を追加。
  • 「パーティション」が増えてきたら、
    「パーティション」境界を消去して「パーティション」をマージする。

以下に、「スライディング ウインドウ」操作の注意点を纏める。

• スイッチ操作は、同じ「ファイル・グループ」に属した「パーティション」同士で行う。

  • なお、スイッチ機能は、同じ「ファイル・グループ」内でのみ有効になるので、
    稼動テーブルと、アーカイブ テーブルの「パーティション」と
    「ファイル・グループ」の対応を、まったく同じにするか、
    双方とも 1 つの「ファイル・グループ」のみで「パーティション分割」する。

  • ただし、後者の 1 つの「ファイル・グループ」では
    「段階的リストア」などを実現できないので、
    基本的に、稼動テーブルとアーカイブ テーブルの「パーティション」と
    「ファイル・グループ」の対応を "まったく" 同じにし、
    複数の「ファイル・グループ」で実装することを推奨する。

• スイッチ元とスイッチ先の「パーティション テーブル」は、
  同じデータ圧縮設定にしておく。

• スイッチ元とスイッチ先の「パーティション インデックス」を
  「パーティション テーブル」に「固定」しておく。

※ 詳細は、自習書を参照。

補足（SWITCH の前提条件）: ALTER TABLE ... SWITCH は
メタデータ操作のみで完了するため一瞬で終わるが、
成立には多数の前提条件がある。主なもの：

条件	内容
同一ファイル グループ	上記のとおり
スキーマの一致	列の定義・照合順序・NULL 許容・IDENTITY まで一致
インデックスの一致	同じインデックスが同じ構成で存在すること
圧縮設定の一致	SQL Server データ圧縮
制約	移動先が空であること、および範囲を保証する CHECK 制約
外部キー	移動元テーブルを参照する外部キーがないこと

スイッチ先を空のステージング テーブルにして、
そこから TRUNCATE するのが定番のパージ手順。
なお、SQL Server 2016 以降は
TRUNCATE TABLE ... WITH (PARTITIONS (n)) で
パーティションを直接切り捨てることもできる。

• 並列クエリは、クエリ実行だけでなく、
  デフラグや再構築のインデックス操作にも適用される。

• この場合、「パーティション インデックス」を
  「パーティション テーブル」に「固定」しておく。

• 「パーティション番号」を指定した、デフラグや再構築

  • ALTER INDEX ステートメントの REBUILD、REORGANIZE に
    「パーティション番号」を指定して使用する。
  • REBUILD は DBCC DBREINDEX と、
    REORGANIZE は DBCC INDEXDEFRAG と同じ機能。
  • しかし、DBCC DBREINDEX については
    「パーティション番号」を指定する引数が存在しない。

• 参考

  • インデックスの再編成と再構築
    https://learn.microsoft.com/ja-jp/sql/relational-databases/indexes/reorganize-and-rebuild-indexes
  • 並列インデックス操作
    https://learn.microsoft.com/ja-jp/sql/relational-databases/indexes/configure-parallel-index-operations

• パーティション分割したテーブルは、「パーティション テーブル」と呼ぶ。
• パーティション分割は、テーブルだけでなく、インデックスにも適用できる。
• パーティション分割したインデックスは、「パーティション インデックス」と呼ぶ。
• 「パーティション インデックス」は、必ずしも「パーティション テーブル」を必要としない。
• 「パーティション テーブル」と同一の「パーティション構成」で、
  「パーティション インデックス」を実装できる。

• 「パーティション テーブル」と同一の「パーティション構成」で、
  「パーティション インデックス」を実装することを、
  「パーティション インデックス」を「パーティション テーブル」に「固定」する、と言う。
• SQL Server Management Studio は、既定で、
  「パーティション インデックス」を「パーティション テーブル」に
  「固定」する動作をとる。

補足（用語）: 「固定」は英語の **aligned（配置が一致した）**の訳。
公式ドキュメントでは
**「配置されたインデックス」/「配置されていないインデックス」**と
訳されることもある。

• 一意性制約
  「パーティション分割列」を含んでいるユニーク インデックス（一意性制約）

  • 「パーティション分割列」を含まない「パーティション インデックス」では、
    複数の「パーティション」間に跨る一意性を保証できないため、
    「パーティション インデックス」を「パーティション テーブル」に
    「固定」する必要がある。

  • 「パーティション分割列」をユニーク インデックス（一意性制約）に
    含めることができない場合、代用として DML トリガを使用することで
    ユニーク インデックス（一意性制約）を保証する必要がある。

補足（トリガによる一意性保証は避けたい）: DML トリガでの代用は、
同時実行下で競合を取りこぼす危険があり、性能も落ちる
（SQL Server のトリガ）。
実務では、

• 非固定（non-aligned）の一意インデックスを作る
  （テーブルとは別の構成にすれば全体の一意性を保証できる。
  ただし SWITCH が使えなくなる）
• 主キーにパーティション分割列を含める
  （複合主キーにする設計を最初から選ぶ）

のいずれかを取るのが一般的。
「一意性の保証」と「SWITCH の利用」はトレードオフになる。

• スライディング ウインドウ
  「パーティション インデックス」を「パーティション テーブル」に「固定」
  することにより、「データのアーカイブ（スライディング ウインドウ）」が可能になる。

• 併置結合
  「パーティション インデックス」を「パーティション テーブル」に「固定」
  することにより、「併置結合によるテーブル結合」が可能になる。

インデックスや、「パーティション インデックス」は、
ベースの「パーティション テーブル」の「パーティション構成」から独立して実装できる。

「パーティション インデックス」の作成についての考慮事項について、以下に纏める。

• ユニーク インデックス（一意性制約）でない
  「クラスタ化インデックス」を「パーティション分割」する場合、
  クラスタ化キーに「パーティション分割列」を指定しないことも可能である。

• SQL Server（の GUI）は、
  既定でクラスタ化キーの一覧に「パーティション分割列」を追加する。

• ユニーク インデックス（一意性制約）でない
  「非クラスタ化インデックス」を「パーティション分割」する場合、
  キーに「パーティション分割列」を指定しないことも可能である。

• SQL Server（の GUI）は、
  既定で「非クラスタ化インデックス」の非キー列を、
  「付加列インデックス」の付加列として
  「パーティション分割列」を追加する。

「パーティション テーブル」上にインデックスを作成する際、

• 並べ替えテーブルは、始めに「パーティション」毎に、メモリ上に作成される。

• 次に、「パーティション」毎に、「ファイル・グループ」のファイル上に作成される。
  （SORT_IN_TEMPDB オプションが指定されている場合は tempdb のファイル）

• 「パーティション テーブル」に「固定」された「パーティション インデックス」の
  作成を実行する場合、

  • 並べ替えテーブルは、メモリ上に一つずつ作成されるので
    メモリの消費を抑えることができる。

• しかし、「パーティション テーブル」に「固定」されない各種インデックスの
  作成を実行する場合、

  • 並べ替えテーブルは複数同時に作成されるのでメモリの消費が多くなる。

    • 例えば、100 個の「パーティション」から構成される
      「パーティション テーブル」に「固定」されない各種インデックスを作成するには、
      4,000 ページを同時に並べ替えることができる 32MB のメモリを消費する。

  • また、SQL Server がマルチプロセッサ（マルチコア）の「並列処理」によって
    「パーティション テーブル」に「固定」されない各種インデックスの作成を
    実行する場合、メモリの要件がさらに高くなる場合がある。

    • 例えば、同時に 4 つのスレッドで、100 個のパーティションから構成される
      「パーティション テーブル」に「固定」されない各種インデックスを作成するには、
      4,000 ページ × 4 スレッド ＝ 16,000 ページ分の、128MB のメモリを消費する。

    • メモリを確保できれば、インデックス作成は成功するが、
      場合によってバッファ キャッシュの枯渇や、ページングの発生などに起因して、
      インデックス作成の性能が低下する場合がある。

    • 並列インデックス操作の構成
      https://learn.microsoft.com/ja-jp/sql/relational-databases/indexes/configure-parallel-index-operations

      ※ MAXDOP インデックス オプションを使用して、
      「並列処理」のスレッドを減らすことができる。

パーティショニング後、PARTITION FUNCTION の削除はできないようです。

• DROP PARTITION FUNCTION (Transact-SQL)
  https://learn.microsoft.com/ja-jp/sql/t-sql/statements/drop-partition-function-transact-sql

  パーティション関数を削除できるのは、対象となるパーティション関数が、
  現在どのパーティション構成でも使用されていない場合のみです。
  パーティション関数が、いずれかのパーティション構成で使用されている場合、
  DROP PARTITION FUNCTION ではエラーが返されます。

調べてみると、

• パーティション テーブルとパーティション インデックスの変更
  https://learn.microsoft.com/ja-jp/sql/relational-databases/partitions/modify-a-partition-function\ パーティション テーブルから非パーティション テーブルへの変換
  • （1）・・・テーブルを構成するパーティションが 1 つだけになるように、
    パーティション テーブルのパーティション関数を変更します。
    • ALTER PARTITION FUNCTION (Transact-SQL)
      https://learn.microsoft.com/ja-jp/sql/t-sql/statements/alter-partition-function-transact-sql
  • （2）テーブルにパーティション分割されたクラスタ化インデックスが
    適用されている場合は、インデックスを削除し、
    非パーティション インデックスとして再構築しても同じ結果を得られます。
    • CREATE INDEX (Transact-SQL)
      https://learn.microsoft.com/ja-jp/sql/t-sql/statements/create-index-transact-sql

とあるので、

上記（1）or（2）でのみ、削除可能なもよう。

補足（順序）: 依存関係があるため、削除は
テーブル／インデックス → パーティション構成 → パーティション関数
の順にしか行えない。
「パーティション構成が残っていて関数を消せない」というのが
元ページのハマりどころで、sys.partition_schemes を確認して
先に DROP PARTITION SCHEME する必要がある。

SQL Server のファイル・グループ

• パーティション テーブルとパーティション インデックス | Microsoft Learn
  https://learn.microsoft.com/ja-jp/sql/relational-databases/partitions/partitioned-tables-and-indexes
• CREATE INDEX (Transact-SQL)
  https://learn.microsoft.com/ja-jp/sql/t-sql/statements/create-index-transact-sql

• SQL Server 2012 自習書シリーズ No.19 データ パーティション入門 - HTML 版 - SQLQuality
  http://www.sqlquality.com/Self2012/Self2012_DP/Text/mokuji.html

• 自習書シリーズ | SQL Server 2008 R2 / SQL Server 2012
  （データベース パーティション入門 / データ パーティション入門）

Tags: 移行, データアクセス, SQL Server