「[[マイクロソフト系技術情報 Wiki>http://techinfoofmicrosofttech.osscons.jp/]]」は、「[[Open棟梁Project>https://github.com/OpenTouryoProject/]]」,「[[OSSコンソーシアム .NET開発基盤部会>https://www.osscons.jp/dotNetDevelopmentInfrastructure/]]」によって運営されています。

-[[戻る>SQL Server]]

* 目次 [#v841dae8]
#contents

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

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

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

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

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

*パーティションの設計指針(分割指針) [#ce44fefb]
主に、日付などの論理的にグループ化された~
データ サブセットを管理するのに適切であるかどうかによって決定される。

**「パーティション分割列」 [#i717ecca]

***範囲 [#kfdc5274]
「[[パーティション関数>#q05a8055]]」により指定される。

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

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

**「パーティション関数」 [#q05a8055]
***定義 [#s01e5519]

 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を指定することが推奨される。

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

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

*パーティションの作成と構成の確認 [#i5c8fafe]

**パーティションの作成手順 [#s0b7cb39]

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

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

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

※ 「パーティション構成」で使用できる「パーティション関数」は1つのみ。~
※ 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

***「パーティション構成」、「[[ファイル・グループ>#e68a346c]]」、「パーティション番号」の関係を確認 [#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]
[[ファイル・グループ>#e68a346c]]の性能向上に加え、
-「[[簡易ストライピング>SQL Server のファイル・グループ#le542327]]」と、
-「[[並列クエリ>SQL Server のファイル・グループ#z5226928]]」が

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

**検索性能の向上 [#kda881e3]
「パーティション」毎に

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

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

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

***スキップ・スキャン、ロック局所化 [#b50cffcf]
-「パーティション分割列」を検索条件に追加した場合、
--スキップ スキャンの効果により、範囲スキャン検索の性能が向上する。
--ロック局所化の効果により、更新処理の同時実行性が向上する。

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

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

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

#ref(SkipScan.png,left,nowrap,スキップ・スキャン)

***併置結合によるテーブル結合 [#xc94981e]
-「併置結合」は、同じ「パーティション構成」の2つのテーブルを、~
「パーティション分割列」をキーにして結合する際に発生する~
(結合に使用する「パーティション分割列」に「パーティション インデックス」を付与しておく)。

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

***検索性能が劣化するケース [#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/]]
>パーティショニングによって読み取り数(アクセスされたパーティション数)が変わってくる(大きくなる)ケースがある。~
ただし、パーティションキーを含めた検索では、読み取り数は大きくならないとなっている。

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

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

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

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

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

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

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

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

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

--次に(アーカイブのために)~
新規作成する「パーティション」で使用する、~
新規「[[ファイル・グループ>#e68a346c]]」を追加。

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

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

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

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

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

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

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

※ 詳細は、[[自習書>#y2c7a50a]]を参照。

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

-「パーティション番号」を指定した、デフラグや再構築
--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]

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

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

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

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

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

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

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

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

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

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

-SQL Server(のGUI)は、~
既定で「非クラスタ化インデックス」の非キー列を、~
[[「付加列インデックス」の付加列>SQL Server のインデックス#oe7890bb]]として「[[パーティション分割列>#i717ecca]]」を追加する。

***メモリの制限 [#y7ca83d1]
「パーティション テーブル」上にインデックスを作成する際、
-並べ替えテーブルは、始めに「パーティション」毎に、メモリ上に作成される。
-次に、「パーティション」毎に、「[[ファイル・グループ>#e68a346c]]」のファイル上に作成される。~
(SORT_IN_TEMPDBオプションが指定されている場合は tempdbのファイル )

-「パーティション テーブル」に「固定」された「パーティション インデックス」の作成を実行する場合、
--並べ替えテーブルは、メモリ上に一つずつ作成されるのでメモリの消費を抑えることができる。

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

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

---例えば、100個の「パーティション」から構成される~
「パーティション テーブル」に「固定」されない各種インデックスを作成するには、~
4,000ページを同時に並べ替えることができる32MBのメモリを消費する。
 
--また、SQL Serverがマルチプロセッサ(マルチコア)の「並列処理」によって~
「パーティション テーブル」に「固定」されない各種インデックスの作成を実行する場合、~
メモリの要件がさらに高くなる場合がある。

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

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

---並列インデックス操作の構成~
http://msdn.microsoft.com/ja-jp/library/ms189329.aspx
>※ MAXDOPインデックス オプションを使用して、~
「並列処理」のスレッドを減らすことができる。

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

-DROP PARTITION FUNCTION (Transact-SQL)~
https://msdn.microsoft.com/ja-jp/library/ms187759.aspx
>パーティション関数を削除できるのは、対象となるパーティション関数が、現在どのパーティション構成でも使用されていない場合のみです。~
パーティション関数が、いずれかのパーティション構成で使用されている場合、DROP PARTITION FUNCTION ではエラーが返されます。

調べてみると、

-パーティション テーブルとパーティション インデックスの変更~
https://technet.microsoft.com/ja-jp/library/ms175864(v=sql.105).aspx~
パーティション テーブルから非パーティション テーブルへの変換
--(1)・・・テーブルを構成するパーティションが 1 つだけになるように、~
パーティション テーブルのパーティション関数を変更します。
---ALTER PARTITION FUNCTION (Transact-SQL)~
https://technet.microsoft.com/ja-jp/library/ms186307(v=sql.105).aspx
--(2)テーブルにパーティション分割されたクラスタ化インデックスが適用されている場合は、~
インデックスを削除し、非パーティション インデックスとして再構築しても同じ結果を得られます。
---CREATE INDEX (Transact-SQL)~
https://technet.microsoft.com/ja-jp/library/ms188783(v=sql.105).aspx

とあるので、

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

*参考 [#t3bf5fd9]

**[[ファイル・グループ>SQL Server のファイル・グループ]] [#e68a346c]

**パーティション インデックス [#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

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

トップ   編集 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS