マイクロソフト系技術情報 Wiki」は、「Open棟梁Project」,「OSSコンソーシアム .NET開発基盤部会」によって運営されています。

目次

概要

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

  • 構成
    • 「パーティション分割」は、テーブル上の特定の列を「パーティション分割列」として指定し、この列の範囲をキーにして、
      行データを特定の「ファイル グループ」にマップされる「パーティション」に振り分ける機能である。
  • なお、「パーティション分割」後の、テーブル・インデックスを、
    「パーティション テーブル」と「パーティション インデックス」と呼ぶ。
    • 「パーティション テーブル」と「パーティション インデックス」は1つの論理エンティティとして扱われる。
    • 標準的なテーブル・インデックスの設計とクエリに関連する、すべてのプロパティと機能がサポートされる。
    • これにより、エンティティ全体のデータの整合性を維持しながら、
      グループ化されたデータ サブセットに対するアクセス性能の向上、管理の効率化を図ることができる。
  • なお、SQL Serverでは、1つのテーブルに最大1000個の「パーティション」を作成できる。
  • 効果
    • 主に運用系性能(インデックスのデフラグ・再構築、データのアーカイブ)の向上が可能。
    • 一部データ アクセス性能(並列クエリ、スキャン局所化、テーブル結合、ロック局所化)も改善する。
    • インスタンスが分割できるような場合は、インスタンス分割でも良い ---> シャーディング

パーティションの設計指針(分割指針)

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

「パーティション分割列」

範囲

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

データ型

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

  • 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つの「パーティション」に分割される。

「パーティション」と「ファイル グループ」

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

  • 「パーティション」は「ファイル グループ」を跨ぐことはできない。
  • 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);

「パーティション構成」の確認方法

「パーティション関数」の名前と境界の確認

 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)

性能の向上ポイント

ファイル・グループの性能向上に加え、

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

検索性能の向上

「パーティション」毎に

  • スキップ スキャン操作の効果により、スキャンを局所化(検索処理の高速化)
  • (エスカレーション時)ロックを局所化(更新処理の同時実行性の向上)
  • 併置結合によるテーブル結合を実行(テーブル結合処理の高速化)
    「パーティション インデックス」を「パーティション テーブル」に「固定」する必要がある。

スキップ・スキャン、ロック局所化

  • 「パーティション分割列」を検索条件に追加した場合、
    • スキップ スキャンの効果により、範囲スキャン検索の性能が向上する。
    • ロック局所化の効果により、更新処理の同時実行性が向上する。
  • 備考
    • スキップ スキャン、ロック局所化は「適切に設計された」OLTPアプリケーションを大きく性能向上させるものではない。
  • スキップ スキャン
    • テーブル スキャンでは「パーティション インデックス」を「パーティション テーブル」に「固定」する必要はない。
  • ロック局所化
    • データベース エンジンが、ロック エスカレーション が必要であると判断する場合、
      行ロック・キー範囲ロックをページ ロックではなく、テーブル ロックに直接エスカレートする。
    • 同様に、ページ ロックは常にテーブル ロックにエスカレートされる。
    • しかし、SQL Server 2008から、「パーティション テーブル」のロックについては、
      テーブル レベルではなく、ヒープまたは B ツリー(HoBT)レベルのロック エスカレーションに
      留めることで、ロック待ちを少なくし、同時実効性を向上できるようになった。
スキップ・スキャン

併置結合によるテーブル結合

  • 「併置結合」は、同じ「パーティション構成」の2つのテーブルを、
    「パーティション分割列」をキーにして結合する際に発生する
    (結合に使用する「パーティション分割列」に「パーティション インデックス」を付与しておく)。
  • この場合にオプティマイザが生成する「併置結合」の実行プランは、
    「パーティション」毎、「並列処理」で結合されるため、メモリを節約し、処理時間が短縮される。

検索性能が劣化するケース

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

  • パーティション テーブルとパーティション インデックスに対するクエリ処理の機能強化
    http://msdn.microsoft.com/ja-jp/library/ms345599.aspx

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

運用性能の向上

メンテナンス

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

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

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

データのアーカイブ(スライディング ウインドウ)

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

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

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

  • 「データベース スキーマ」に
    • 稼動テーブル・アーカイブ テーブルの
      「パーティション テーブル」、「パーティション インデックス」を
      同じ「パーティション構成」で作成。
    • 次に新規作成する「パーティション」で使用する、新規「ファイル グループ」を追加。
  • 稼動テーブル・アーカイブ テーブルに
    • 新規「パーティション」で使用する「ファイル グループ」を指定。
    • 「パーティション」境界を追加し、新規「パーティション」を分割作成。
  • 稼動テーブルからアーカイブテーブルに、最も古い「パーティション」のデータをスイッチ。
    • 稼動テーブルへ、スイッチした「パーティション」が対象となるデータ挿入を禁止する制約を追加。
    • 「パーティション」が増えてきたら、「パーティション」境界を消去して「パーティション」をマージする。

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

  • スイッチ操作は、同じ「ファイル グループ」に属した「パーティション」同士で行う。
  • なお、スイッチ機能は、同じ「ファイル グループ」内でのみ有効になるので、
    稼動テーブルと、アーカイブ テーブルの「パーティション」と「ファイル グループ」の対応を、
    まったく同じにするか、双方とも1つの「ファイル グループ」のみで「パーティション分割」する。
  • ただし、後者の1つの「ファイル グループ」では「段階的リストア」などを実現できないので、
    基本的に、稼動テーブルとアーカイブ テーブルの「パーティション」と「ファイル グループ」の対応を
    "まったく"同じにし、複数の「ファイル グループ」で実装することを推奨する。
  • スイッチ元と スイッチ先の「パーティション テーブル」は、同じデータ圧縮設定にしておく。
  • スイッチ元と スイッチ先の「パーティション インデックス」を「パーティション テーブル」に「固定」しておく。

インデックスの断片化の局所化、デフラグや再構築の局所化と高速化

  • 並列クエリは、クエリ実行だけでなく、デフラグや再構築のインデックス操作にも適用される。
  • この場合、「パーティション インデックス」を「パーティション テーブル」に「固定」しておく。
  • 「パーティション番号」を指定した、デフラグや再構築
    • ALTER INDEXステートメントのREBUILD、REORGANIZEに「パーティション番号」を指定して使用する。
    • REBUILDはDBCC DBREINDEXと、REORGANIZEはDBCC INDEXDEFRAGと同じ機能。
    • しかし、DBCC DBREINDEXについては「パーティション番号」を指定する引数が存在しない。

「パーティション インデックス」

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

「パーティション インデックス」を「パーティション テーブル」に「固定」

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

「パーティション インデックス」の「固定」が必要なケース

  • 一意性制約
    「パーティション分割列」を含んでいるユニーク インデックス(一意性制約)
  • 「パーティション分割列」を含まない「パーティション インデックス」では、
    複数の「パーティション」間に跨る一意性を保証できないため、
    「パーティション インデックス」を「パーティション テーブル」に「固定」する必要がある 。
  • 「パーティション分割列」をユニーク インデックス(一意性制約)に含めることができない場合、
    代用としてDMLトリガを使用することでユニーク インデックス(一意性制約)を保証する必要がある。
  • 併置結合
    「パーティション インデックス」を「パーティション テーブル」に「固定」
    することにより、「併置結合によるテーブル結合」が可能になる。

「パーティション インデックス」の「固定」が不要なケース

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

考慮事項

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

一意でないクラスタ化インデックス

  • ユニーク インデックス(一意性制約)でない
    「クラスタ化インデックス」を「パーティション分割」する場合、
    クラスタ化キーに「パーティション分割列」を指定しないことも可能である。
  • SQL Server(のGUI)は、
    既定でクラスタ化キーの一覧に「パーティション分割列」を追加する。

一意でない非クラスタ化インデックス

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

メモリの制限

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

  • 並べ替えテーブルは、始めに「パーティション」毎に、メモリ上に作成される。
  • 次に、「パーティション」毎に、「ファイル グループ」のファイル上に作成される。
    (SORT_IN_TEMPDBオプションが指定されている場合は tempdbのファイル )
  • 「パーティション テーブル」に「固定」された「パーティション インデックス」の作成を実行する場合、
    • 並べ替えテーブルは、メモリ上に一つずつ作成されるのでメモリの消費を抑えることができる。
  • しかし、「パーティション テーブル」に「固定」されない各種インデックスの作成を実行する場合、
  • 並べ替えテーブルは複数同時に作成されるのでメモリの消費が多くなる 。
  • 例えば、100個の「パーティション」から構成される
    「パーティション テーブル」に「固定」されない各種インデックスを作成するには、
    4,000ページを同時に並べ替えることができる32MBのメモリを消費する。
  • また、SQL Serverがマルチプロセッサ(マルチコア)の「並列処理」によって
    「パーティション テーブル」に「固定」されない各種インデックスの作成を実行する場合、
    メモリの要件がさらに高くなる場合がある。
  • 例えば、同時に4つのスレッドで、100個のパーティションから構成される
    「パーティション テーブル」に「固定」されない各種インデックスを作成するには、
    4,000ページ × 4スレッド = 16,000ページ分の、128MBのメモリを消費する。
  • メモリを確保できれば、インデックス作成は成功するが、
    場合によってバッファ キャッシュの枯渇や、ページングの発生などに起因して、
    インデックス作成の性能が低下する場合がある。

パーティションの削除

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

  • DROP PARTITION FUNCTION (Transact-SQL)
    https://msdn.microsoft.com/ja-jp/library/ms187759.aspx

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

調べてみると、

とあるので、

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

参考

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

クエリ処理の機能強化

自習書シリーズ


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


添付ファイル: fileSkipScan.png 1180件 [詳細]

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2018-12-11 (火) 17:32:06 (313d)