SQL Server パーティション分割のバックアップ差分(No.6)

追加された行はこの色です。
削除された行はこの色です。
[[Open棟梁Project>http://opentouryo.osscons.jp/]] - [[マイクロソフト系技術情報 Wiki>http://techinfoofmicrosofttech.osscons.jp/]]

-[[戻る>SQL Server]]

* 目次 [#v841dae8]
#contents

*概要 [#f24a83fb]
主にSQL Server パーティション分割のOLTPにおける効果について説明する。
主にSQL Server パーティション分割の効果について説明する。

*パーティション分割とインデックス [#m1dcc7a2]
パーティション分割は、テーブルだけでなく、インデックスにも適用できる。
-パーティション分割したテーブルは、「パーティション テーブル」と呼ぶ。
-パーティション分割したインデックスは、「パーティション インデックス」と呼ぶ。
-「パーティション分割」は、テーブル上の特定の列を「パーティション分割列」として指定し、この列の範囲をキーにして、~
行データを特定の「ファイル グループ」にマップされる「パーティション」に振り分ける機能である。

-「パーティション インデックス」は、必ずしも「パーティション テーブル」を必要としない。
-「パーティション テーブル」と同一の「パーティション構成」で、「パーティション インデックス」を実装することを、~
「パーティション インデックス」を「パーティション テーブル」に「固定」する。と言う。
-なお、「パーティション分割」後の、テーブル・インデックスを、~
「パーティション テーブル」と「パーティション インデックス」と呼ぶ。
--「パーティション テーブル」と「パーティション インデックス」は１つの論理エンティティとして扱われる。
--標準的なテーブル・インデックスの設計とクエリに関連する、すべてのプロパティと機能がサポートされる。
--これにより、エンティティ全体のデータの整合性を維持しながら、~
グループ化されたデータ サブセットに対するアクセス性能の向上、管理の効率化を図ることができる。

-CREATE INDEX (Transact-SQL)~
http://msdn.microsoft.com/ja-jp/library/ms188783.aspx
-なお、SQL Serverでは、1つのテーブルに最大1000個の「パーティション」を作成できる。

*性能の向上ポイント [#b9c469bd]
-並列クエリなど、I/Oスレッドを増大させることによって~
初回検索時の大幅な性能向上が期待できるが、これは、基本的に
--適切に設計されたOLTP向けの性能向上を期待するものではない（OLAP向け）。
--I/Oネックにならない様にファイル・グループやRAIDでI/O性能を向上させておく必要がある。
*パーティションの設計指針（分割指針） [#ce44fefb]
主に、日付などの論理的にグループ化された~
データ サブセットを管理するのに適切であるかどうかによって決定される。

-スキップ スキャン操作、ロック局所化は「適切に設計された」~
OLTPアプリケーションを大きく性能向上させるものではない。
**「パーティション分割列」 [#i717ecca]
***範囲 [#kfdc5274]
「パーティション関数」により指定される。

-下記の機能に起因してOLTPにおける検索性能が向上することは期待できる。~
24時間止められないシステムなどで特に有効となる機能である。
***データ型 [#tdaf1863]
以下のデータ型を除くインデックス キーとして使用できるデータ型の列を使用できる。

--データのアーカイブが迅速にできる（スライディング ウインドウ）。~
「パーティション テーブル」に「固定」された、「パーティション インデックス」も対象となる。
-timestamp型
-ntext型
-text型
-image型
-xml型
-varchar型
-nvarchar型
-varbinary型
-CLRユーザ定義データ型
-別名データ型

--インデックスの断片化の局所化、デフラグや再構築の局所化と高速が可能。~
並列クエリは、クエリ実行およびインデックス操作にも適用される。~
バックアップ・リストアについては、バックアップ デバイス数と同数のスレッドが使用される。
**「パーティション関数」 [#q05a8055]
***定義 [#s01e5519]

-その他、パーティション分割は必須ではないが、~
ファイル・グループ単位の部分バックアップや、段階的リストアが可能である。~
 CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type ) 
   AS RANGE RIGHT FOR VALUES ( [ boundary_value [ ,...n ] ] ); 

*検索性能の向上 [#kda881e3]
上記の「パーティション関数」（partition_function_name）では、~
指定の型（input_parameter_type）の「パーティション分割列」に~
格納された値に基づき「パーティション分割」を行う。~

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

***例 [#h34e9ecf]

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

**「パーティション」と「ファイル グループ」 [#v607bef2]
双方の関係は以下のようになる。
-「パーティション」は「ファイル グループ」を跨ぐことはできない。
-１つの「パーティション」を、１つの「ファイル グループ」にマップする。
-いくつかの「パーティション」を、１つの「ファイル グループ」にマップする。
-すべての「パーティション」を、１つの「ファイル グループ」にマップする。

*パーティションの作成手順 [#s0b7cb39]
**「パーティション構成」の定義する [#o35d89e1]
「パーティション関数」の「パーティション分割」で指定された「パーティション」と、~
「ファイル グループ」のマップを指定して、「パーティション構成」を定義する。

パーティション構成の定義：
 CREATE PARTITION SCHEME partition_schema_name
   AS PARTITION partition_function_name
     TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] );

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

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

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

パーティション テーブルの定義：
 CREATE TABLE table_name(
 <column_definition>[ ,...n ]) 
   ON partition_schema_name (div_column_name); 

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

パーティション インデックスの定義：
  CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON table_name (column_name);
      ON partition_schema_name (div_column_name);

*「パーティション構成」の確認方法 [#a9c90e01]
**「パーティション関数」の名前と境界の確認 [#d531908c]
  SELECT f.name, r.value, *
    FROM sys.partition_range_values r
      INNER JOIN sys.partition_functions f
        ON r.function_id = f.function_id

**「パーティション構成」、「ファイル グループ」、「パーティション番号」の関係を確認 [#gced59b7]
 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

**データが格納された「パーティション番号」を確認 [#r307e080]
-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)


*性能の向上ポイント [#b9c469bd]
[[ファイル・グループ>SQL Server のファイル・グループ#xdd38c8c]]の性能向上に加え、
-「[[簡易ストライピング>SQL Server のファイル・グループ#le542327]]」と、
-「[[並列クエリ>SQL Server のファイル・グループ#z5226928]]」が

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

**検索性能の向上 [#kda881e3]
「パーティション」毎に
- ワーカ スレッドを起動（並列クエリ）

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

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

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

**並列クエリの次数に影響を与えるオプション [#x582f183]
「並列クエリ」の次数に以下のオプションが影響を与える。
|affinity mask|CPUのアフィニティ マスク|
|max degree of parallelism 21|「並列クエリ」の最大次数|
|MAX_DOP|「並列クエリ」の最大次数|
|MAXDOP|「並列クエリ」の最大次数|
|「パーティション」数|「並列クエリ」の次数|
***スキップ・スキャン、ロック局所化 [#b50cffcf]
-「パーティション分割列」を検索条件に追加した場合、
--スキップ スキャンの効果により、範囲スキャン検索の性能が向上する。
--ロック局所化の効果により、更新処理の同時実行性が向上する。

これらのオプションの優先順は、以下のようになる。~
affinity mask > MAX_DOP > MAXDOP > max degree of parallelism~
-備考
--スキップ スキャン、ロック局所化は「適切に設計された」OLTPアプリケーションを大きく性能向上させるものではない。

なお、複数のユーザが、クエリを実行する場合、~
プランの次数は、その処理開始時点の各CPUの処理量を考慮する。~
（具体的な"しきい値"は、Microsoftから公開されていない）。
--スキップ スキャン
---テーブル スキャンでは「パーティション インデックス」を「パーティション テーブル」に「固定」する必要はない。

**スキップ・スキャン、ロック局所化 [#b50cffcf]
「パーティション分割列」を検索条件に追加した場合、
-スキップ スキャン操作の効果により、インデックスの範囲スキャン検索の性能が向上する。
-ロックを局所化の効果により、更新処理の同時実行性が向上する。
--ロック局所化
---データベース エンジンが、ロック エスカレーション が必要であると判断する場合、~
行ロック・キー範囲ロックをページ ロックではなく、テーブル ロックに直接エスカレートする。
---同様に、ページ ロックは常にテーブル ロックにエスカレートされる。
---しかし、SQL Server 2008から、「パーティション テーブル」のロックについては、~
テーブル レベルではなく、ヒープまたは B ツリー（HoBT）レベルのロック エスカレーションに~
留めることで、ロック待ちを少なくし、同時実効性を向上できるようになった。

＃「パーティション テーブル」に「固定」された、「パーティション インデックス」である必要はない。
#ref(SkipScan.png,left,nowrap,スキップ・スキャン)

**検索性能が劣化するケース [#w082fffa]
***併置結合によるテーブル結合 [#xc94981e]
-「併置結合」は、同じ「パーティション構成」の２つのテーブルを、~
「パーティション分割列」をキーにして結合する際に発生する~
（結合に使用する「パーティション分割列」に「パーティション インデックス」を付与しておく）。

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

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

-パーティショニング実施時にパーティションキーの有無による検索効率の違い ? SE の雑記~
http://engineermemo.wordpress.com/2012/04/10/%E3%83%91%E3%83%BC%E3%83%86%E3%82%A3%E3%82%B7%E3%83%A7%E3%83%8B%E3%83%B3%E3%82%B0%E5%AE%9F%E6%96%BD%E6%99%82%E3%81%AB%E3%83%91%E3%83%BC%E3%83%86%E3%82%A3%E3%82%B7%E3%83%A7%E3%83%B3%E3%82%AD%E3%83%BC/
-[[パーティショニング実施時にパーティションキーの有無による検索効率の違い ? SE の雑記>http://engineermemo.wordpress.com/2012/04/10/%E3%83%91%E3%83%BC%E3%83%86%E3%82%A3%E3%82%B7%E3%83%A7%E3%83%8B%E3%83%B3%E3%82%B0%E5%AE%9F%E6%96%BD%E6%99%82%E3%81%AB%E3%83%91%E3%83%BC%E3%83%86%E3%82%A3%E3%82%B7%E3%83%A7%E3%83%B3%E3%82%AD%E3%83%BC/]]
>パーティショニングによって読み取り数（アクセスされたパーティション数）が変わってくる（大きくなる）ケースがある。~
ただし、パーティションキーを含めた検索では、読み取り数は大きくならないとなっている。

-パーティション テーブルとパーティション インデックスに対するクエリ処理の機能強化~
http://msdn.microsoft.com/ja-jp/library/ms345599.aspx
>パーティション テーブルとパーティション インデックスに対するクエリの実行プランは、~
Transact-SQL の SET SHOWPLAN_XML または SET STATISTICS XMLを使用するか、~
SQL Server Management Studio のグラフィカル実行プラン出力を使用して調べることができる。

**運用性能の向上 [#w0f4710a]
メンテナンス
-テーブル データのアーカイブ
-インデックスの断片化の局所化、再構築・最適化

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

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

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

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

「スライディング ウインドウ」操作は、次の手順で行われる。
-「データベース スキーマ」に
--稼動テーブル・アーカイブ テーブルの~
「パーティション テーブル」、「パーティション インデックス」を~
同じ「パーティション構成」で作成。
--次に新規作成する「パーティション」で使用する、新規「ファイル グループ」を追加。

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

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

以下に、「スライディング ウインドウ」操作の注意点を纏める。
-スイッチ操作は、同じ「ファイル グループ」に属した「パーティション」同士で行う。

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

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

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

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

***インデックスの断片化の局所化、デフラグや再構築の局所化と高速化 [#m25ec4d1]
-並列クエリは、クエリ実行だけでなく、デフラグや再構築のインデックス操作にも適用される。~
-この場合、「パーティション インデックス」を「パーティション テーブル」に「固定」しておく。

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

-参考
--インデックスの再編成と再構築~
http://msdn.microsoft.com/ja-jp/library/ms189858.aspx
--並列インデックス操作~
http://msdn.microsoft.com/ja-jp/library/ms191292.aspx
--並列インデックス操作の構成~
https://msdn.microsoft.com/ja-jp/library/ms189329.aspx

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

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

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

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

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

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

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

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

**「パーティション インデックス」の「固定」が不要なケース [#cab1f007]
インデックスや、「パーティション インデックス」は、~
ベースの「パーティション テーブル」の「パーティション構成」から独立して実装できる。


*参考 [#t3bf5fd9]
**パーティション インデックス [#h30d881e]
-パーティション インデックスの専用ガイドライン~
http://msdn.microsoft.com/ja-jp/library/ms187526.aspx
-CREATE INDEX (Transact-SQL)~
http://msdn.microsoft.com/ja-jp/library/ms188783.aspx

**クエリ処理の機能強化 [#wd5596fc]
-パーティション テーブルとパーティション インデックスに対するクエリ処理の機能強化~
https://technet.microsoft.com/ja-jp/library/ms345599.aspx

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

-自習書シリーズ | SQL Server 2008 R2~
https://www.microsoft.com/ja-jp/server-cloud/local/sqlserver/2008/r2/technology/self-learning.aspx

--データベース パーティション入門（製品版対応）
---Doc (4.10 MB)~
http://download.microsoft.com/download/D/1/D/D1D2FBEC-8D48-4325-B51C-FD63CC3DE835/SQL08_R2_SelfLearning20_DP.docx
---サンプル スクリプト (96.4 KB)~
http://download.microsoft.com/download/D/1/D/D1D2FBEC-8D48-4325-B51C-FD63CC3DE835/SQL08_R2_SelfLearning20_DP.exe

-自習書シリーズ - Microsoft SQL Server 2012~
https://www.microsoft.com/ja-jp/server-cloud/local/sqlserver/2012/technology/self-learning.aspx

--データ パーティション入門
---Docx (15.2 MB)~
http://download.microsoft.com/download/3/C/5/3C5DD4B2-809D-4F80-8E6B-6E9C87AD00BC/SQL11_SelfLearning19_DP.docx
---PDF (2.51 MB)~
http://download.microsoft.com/download/5/A/2/5A29D047-2D83-4ACD-919F-4614847E8392/SQL11_SelfLearning19_DP.pdf
---サンプル スクリプト (96.3 KB)~
http://download.microsoft.com/download/3/C/5/3C5DD4B2-809D-4F80-8E6B-6E9C87AD00BC/SQL11_SelfLearning19_DP.exe
SQL Server パーティション分割 のバックアップ差分(No.6)

SQL Server パーティション分割のバックアップ差分(No.6)
