パーティション・テーブルにおけるパフォーマンスの考察

  • Article

マイクロソフトの植田です。

今回はパーティション分割とパフォーマンスに関する話題をご紹介したいと思います。

https://blogs.msdn.com/sqlcat/archive/2005/11/30/498415.aspx

注：下記内容に関する詳細の確認をご希望される場合は上記のブログを参照いただけますようお願いします。

本ドキュメントは以下の方を対象としております。

l  開発者、テストエンジニア、データベース・アドミニストレータ

l  データベース、および、Microsoft SQL Serverについて基本的な知識をお持ちの方

パーティション・テーブルにおける並列度、パフォーマンスの考察

質問：SQL Server 2005でパーティション分割されたテーブルに対してクエリを行ったとき、どの程度の並列度が期待でき、パフォーマンスにどのように影響するのでしょうか。

答え：まず背景として、SQL Server 2005のパーティション分割は管理、および、可用性の観点で大きな機能改善がされています。管理面ではメタ・データのスイッチ・イン／スイッチ・アウトのみでパーティション分割されたデータの挿入、削除が可能になりました（挿入／削除先のテーブルがスライディング・ウィンドウ・シナリオの要求を満たしている場合。スライディング・ウィンドウ・シナリオについてはBooks Onlineの以下の記述が参考になります「データのサブセットを管理するためのパーティション設計」）。可用性においては、インデックス構築、並列処理、および、ファイルグループ含まれる個々のファイル単位でのリストアが行えるようになりました（詳細は以下のblog「SQL Server 2005 Online Piecemeal Restore シナリオによる可用性向上」をご参照ください。

では、テーブルをパーティション分割することによって、どのようにパフォーマンスが影響を受けるか見ていきましょう。パーティショニングを使っているか否かにかかわらず、並列実行プランの選択はCPU数、クエリのコスト、利用可能なメモリと現在の業務負荷によって決定されます。ここで述べられる以下の考察は、想定される環境が並列実行を行う条件を満たしているときのみ有効です（例えばCPUが複数存在し、十分な容量のメモリを搭載している等）。

あるクエリが一つのパーティションのみ使用する場合、例えば一つのパーティションを除いて他は全て排除されるとき、SQL Server 2005の内部パーティション並列機構はsp_configureの最大並列度（Maximum Degree of Parallelism: MAXDOP）の値を最大として、複数のスレッドを使用して行の取得処理を並列化します。MAXDOPはたいていデフォルトの値0に設定されており、その場合、MAXDOPは総CPU数と等しくなります。クエリが2つ以上のパーティションにまたがる時、一つのパーティションにつき、スレッドを一つだけ用いて値が取得されます。

もしパーティションの数がMAXDOP以下であれば、データ取得処理は、スレッドがCXPACKET待ちになるまで、複数のスレッドで並列化されるでしょう。パーティションの数がMAXDOPよりも多い場合、SQL Server 2005のオンデマンド並列化は、あるスレッドが一つのパーティションにおいて処理を完結した後、次のパーティションについての処理が順次自動的に開始されることを意味します。例えば、16パーティション存在していてMAXDOPが8の場合、最初のスレッドがパーティション1-8に対して処理を行います。最初に処理を完了したスレッドは、次はパーティション9において処理を開始し、その次のスレッドはパーティション10、といった具合に処理が行われます。

8個以上のたくさんのCPUを持った大規模なマシンで、クエリ・パフォーマンスが最低となるケースは2つのパーティションにまたがる一つの「SELECT」文を実行するケースです（テーブル1参照）。行を取得するサブ・シーケンスの手順においてはMAXDOPの値が適応されていますが（テーブル2の緑のハイライトの部分参照）、一つのパーティションに対して一つのスレッドのみが行を取得するのに使用されます（テーブル2の黄色でハイライトされた、パーティション80、81、および、実行スレッド数列参照）。

テーブル1：2週間分のデータを取得

SELECT

       SUM(Sales_Qty) as Sales_Qty,

       SUM(Sale_Amt) as Sales_Amount

FROM SalesDB.dbo.Tbl_Fact_ Sales –- Partitioned by week

WHERE date_id between '20050703' and '20050716'

テーブル2：プロファイル統計：MAXDOP = 12

Rows

Executes

StmtText

1

1

SELECT SUM([Sales_Qty]) [Sales_Qty],SUM([Sale_Amt]) [Sales_Amount] FROM [SalesDB].[dbo].[Tbl_Fact_Sales] WHERE [date_id]>=@1 AND [date_id]<=@2

0

0

  |--Compute Scalar(DEFINE:([Expr1002]=CASE WHEN [globalagg1008]=(0) THEN NULL ELSE [globalagg1010] END, [Expr1003]=CASE WHEN [globalagg1012]=(0) THEN NULL ELSE [globalagg1014] END))

1

1

       |--Stream Aggregate(DEFINE:([globalagg1008]=SUM([partialagg1007]), [globalagg1010]=SUM([partialagg1009]), [globalagg1012]=SUM([partialagg1011]), [globalagg1014]=SUM([partialagg1013])))

2

1

            |--Parallelism(Gather Streams)

2

12

                 |--Stream Aggregate(DEFINE:([partialagg1007]=COUNT_BIG([SalesDB].[dbo].[Tbl_Fact_Sales].[Sales_Qty] as [ss].[Sales_Qty]), [partialagg1009]=SUM([SalesDB].[dbo].[Tbl_Fact_Sales].[Sales_Qty] as [ss].[Sales_Qty]), [partialagg1011]=COUNT_BIG([SalesDB].[dbo].[Tbl_Fact_Sales].[Sale_Amt] as [ss].[Sale_Amt]), [partialagg1013]=SUM([SalesDB].[dbo].[Tbl_Fact_Sales].[Sale_Amt] as [ss].[Sale_Amt])))

20577235

12

                      |--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1006]) PARTITION ID:([PtnIds1006]))

2

12

                           |--Parallelism(Distribute Streams, Demand Partitioning)

2

1

                           | |--Constant Scan(VALUES:(((80)),((81))))

20577235

2

                           |--Index Seek(OBJECT:([SalesDB].[dbo].[Tbl_Fact_Sales].[IX_Tbl_Fact_Sales_SKDteItmStrIDSalQtySalAmtDiscMkd] AS [ss]),

 SEEK:([ss].[SK_Date_ID] >= (20050703) AND [ss].[SK_Date_ID] <= (20050716)) ORDERED FORWARD PARTITION ID:([PtnIds1006]))

例えば、月ごとにパーティション分割されている、テラバイトサイズのセールス・テーブルがあったとします。よくあるクエリ・パターンは今月のデータを先月、または、去年の同月のデータと比較することでしょう。以下、月ごとにパーティション分割した場合と週ごとにパーティション分割した場合における、クエリと並列度の関係を示します。

テーブル3：月ごとにパーティション分割

WHERE句

パーティション数

取得の並列度

単一SELECT文

SELECT …. WHERE DateCol BETWEEN ’10/1/2005’ and ‘11/30/2005’

2

1パーティションあたり1スレッド

SELECT

UNION SELECT

[UNION SELECT]

Select …. Where DateCol between ’10/1/2005’ and ‘10/31/2005

UNION

Select …. Where DateCol between ’11/1/2005’ and ‘11/30/2005

１SELECT文あたり１つ

パーティションあたりMAXDOP個のスレッド

テーブル4：週ごとにパーティション分割：11月1-15日までの売り上げ

WHERE句

パーティション数

取得の並列度

単一SELECT文

SELECT SUM(Sales) from WKSales

WHERE DateCol BETWEEN ’11/1/2005’ and ‘11/15/2005’

3

1パーティションあたり1スレッド

SELECT

UNION SELECT

[UNION SELECT]

SELECT SUM(Sales) from WKSales

WHERE DateCol BETWEEN ’11/1/2005’ and ‘11/5/2005’

UNION

SELECT SUM(Sales) from WKSales

WHERE DateCol BETWEEN ’11/6/2005’ and ‘11/12/2005’

UNION

SELECT SUM(Sales) from WKSales

WHERE DateCol BETWEEN ’11/13/2005’ and ‘11/15/2005’

１SELECT文あたり１つ

パーティションあたりMAXDOP個のスレッド

一つの大きなモノリシックなテーブルにおいては、管理性、および、可用性に関しては課題があるかも知れませんが、よいパフォーマンスが得られるケースがあります。例えば、2つのパーティションにまたがった、1TBのテーブルにクエリを発行する場合、それぞれのパーティションから行を取得するために、一つのパーティションに１つのみスレッドを割り当てることができます。対照的に、モノリシックな1TBのテーブルから行を取得する場合は、MAXDOP数分のスレッドを割り当てることができます（テーブル5参照）。

 

テーブル5：モノリシックなBigSalesTable

WHERE句

パーティション数

取得の並列度

単一SELECT文

SELECT …. WHERE DateCol BETWEEN ’10/1/2005’ and ‘11/30/2005’

N/A

MAXDOP

ベスト・プラクティス

上記ではパーティション分割、および、並列処理はデータ・ウェアハウスの業務、バッチ処理、および、レポーティングに最も適したパフォーマンスを発揮します（全てのデータ･ウェアハウスの環境において全てのクエリを並列化することがいつも望ましいとは限りませんが）。特に、並列化はシステムが同時に2,3のクエリを発行していて、それらのクエリの実行時間を最短にして出来る限り多くのリソースを利用可能にしたいとき最も効果的です。もし、そのデータ・ウェアハウスがすでに多くの処理を同時並行で行っている場合、複数の単一スレッド・クエリによって利用可能なリソースをすでに消費してしまっている恐れがあるため、並列化はスループットの向上や応答時間の短縮に寄与しないでしょう。同様の理由で、同時並行で多くの処理が行われるOLTP業務では、パフォーマンス向上のために並列化することは望まないでしょう。

パーティショニングの粒度（例えば、日にち単位、週単位、月単位）を決定する時は、ユーザーのよく使うクエリ・パターンを考慮してください。8CPU以上の大規模なマルチ・プロセッサ・マシンで最も高い並列パフォーマンスを得るためには、クエリの並列化を最大限にするのが望ましいでしょう、例えば最低でもパーティションごとにMAXDOP数にしてください。テーブル3、および、テーブル4で示しているように、単一パーティションごとにクエリを発行して、その集合（ユニオン）を要求するSQL文に書き換えることにより、パフォーマンスは向上します。

結論として、SQL Server 2005 におけるパーティション分割されたテーブルに対する並列問い合わせで最もよいパフォーマンスを得る方法は、単一のパーティションそれぞれに「SELECT」文を発行する（内部パーティション並列化を得る）、または、一つの「SELECT」文に対して多くのパーティションを設定する（パーティションごとにスレッドを一つ割り当てる）ことです。

コミュニティにおけるマイクロソフト社員による発言やコメントは、マイクロソフトの正式な見解またはコメントではありません。