パーティション テーブルとパーティション インデックスに対するクエリ処理の機能強化

新しいパーティション対応のシーク操作

SQL Server 2008 では、パーティション テーブルの内部表現が変更されており、クエリ プロセッサに対してテーブルは、先頭列に PartitionID を持つ複数列インデックスとして表されます。PartitionID は、特定の行を含むパーティションの ID を表すために内部的に使用される非表示の計算列です。たとえば、T(a, b, c) として定義されているテーブル T が列 a でパーティション分割され、そのテーブルの列 b にクラスタ化インデックスがあるとします。SQL Server 2008 では、このパーティション テーブルは内部的に、スキーマが T(PartitionID, a, b, c) で複合キー (PartitionID, b) にクラスタ化インデックスがある非パーティション テーブルとして扱われます。これにより、クエリ オプティマイザは、パーティション テーブルまたはパーティション インデックスの PartitionID に基づいてシーク操作を実行できます。

パーティションの解消は、このシーク操作で行われるようになりました。

また、クエリ オプティマイザが拡張され、ある条件を指定したシーク操作またはスキャン操作を PartitionID (論理的な先頭列) およびその他のインデックス キー列に基づいて実行し、続いて 2 番目のレベルのシークを実行できます。このシークは最初のレベルのシーク操作の条件を満たす値ごとに、別の条件を指定した 1 つ以上の追加の列に基づいて実行できます。つまり、スキップ スキャンと呼ばれるこの操作によって、クエリ オプティマイザは、ある条件に基づいてシーク操作またはスキャン操作を実行してアクセス対象のパーティションを特定し、その操作内で 2 番目のレベルのインデックスのシーク操作を実行し、特定済みのパーティションのうち別の条件を満たすパーティションから行を返すことができます。たとえば、次のクエリについて考えてみます。

SELECT * FROM T WHERE a < 10 and b = 2;

この例では、T(a, b, c) として定義されているテーブル T が列 a でパーティション分割され、そのテーブルの列 b にクラスタ化インデックスがあるとします。テーブル T のパーティション境界は、次のパーティション関数によって定義されます。

CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (3, 7, 10);

クエリを解決するために、クエリ プロセッサによって最初のレベルのシーク操作が実行され、条件 T.a < 10 を満たす行を含むすべてのパーティションが検索されます。これにより、アクセス対象のパーティションが特定されます。特定された各パーティション内で、プロセッサによって列 b のクラスタ化インデックスに対する 2 番目のレベルの Seek が実行され、条件 T.b = 2 および T.a < 10 を満たす行が検索されます。

次の図は、スキップ スキャン操作を論理的に表したものです。列 a および b にデータが格納されているテーブル T を示しています。パーティションには 1 ～ 4 の番号が割り当てられており、パーティション境界は破線の縦線で表示されています。パーティションに対する最初のレベルのシーク操作 (図には示されていません) で、パーティション 1、2、および 3 が、テーブルに対して定義されたパーティション分割および列 a の述語で暗黙的に指定された Seek 条件を満たすことが特定されています。つまり、条件 T.a < 10 を満たします。スキップ スキャン操作の 2 番目のレベルの Seek 部分でスキャンされるパスは、曲線で表示されています。基本的に、スキップ スキャン操作によって、前の各パーティションで条件 b = 2 を満たす行がシークされます。スキップ スキャン操作の総コストは、3 つの個別のインデックス シークの総コストと同じです。

クエリ実行プランのパーティション分割情報の表示

パーティション テーブルとパーティション インデックスに対するクエリの実行プランは、Transact-SQL SET ステートメントの SET SHOWPLAN_XML または SET STATISTICS XML を使用するか、SQL Server Management Studio のグラフィカル実行プラン出力を使用して調べることができます。たとえば、コンパイル時の実行プランを表示するにはクエリ エディタのツール バーの [推定実行プランの表示] をクリックし、実行時のプランを表示するには [実際の実行プランを含める] をクリックします。

これらのツールを使用すると、次の情報を確認できます。

• パーティション テーブルまたはパーティション インデックスにアクセスする Scan、Seek、Insert、Update、Merge、Delete などの操作。

• クエリによってアクセスされるパーティション。たとえば、実行時の実行プランでは、アクセスされるパーティションの総数やアクセスされる連続したパーティションの範囲を確認できます。

• シーク操作またはスキャン操作でスキップ スキャン操作が使用され、1 つ以上のパーティションからデータが取得されるタイミング。

実行プランの表示に関する詳細については、「実行プランの操作方法に関するトピック」を参照してください。

パーティション情報に関する機能強化

プラン表示のその他のメソッドを使用したパーティション情報の表示

パーティション分割されたヒープの実行プランの解釈

SQL Server 2008 では、パーティション分割されたヒープがパーティション ID の論理インデックスとして扱われます。パーティション分割されたヒープのパーティションの解消は、実行プランでは、SEEK 述語がパーティション ID に対して指定されている Table Scan 操作として表されます。次の例は、プラン表示情報を示しています。

|-- Table Scan (OBJECT: ([db].[dbo].[T]), SEEK: ([PtnId1001]=[Expr1011]) ORDERED FORWARD)

併置結合の実行プランの解釈

結合の併置は、同じパーティション関数または同等のパーティション関数を使用して 2 つのテーブルをパーティション分割し、結合の両側のパーティション列をクエリの結合条件に指定すると発生します。クエリ オプティマイザでは、同じパーティション ID を持つ各テーブルのパーティションが個別に結合されるプランを生成できます。併置結合では必要なメモリが少なくなり、処理時間が短縮されることがあるので、この結合は非併置結合よりも処理が高速になる場合があります。オプティマイザでは、コストの推定に基づいて、非併置プランまたは併置プランが選択されます。

併置プランでは、Nested Loops 結合は内側の 1 つ以上の結合テーブルまたはインデックス パーティションを読み取ります。Constant Scan 操作内の数値は、パーティション番号を表します。

パーティション テーブルまたはパーティション インデックスに対して併置結合の並行プランが生成された場合、結合操作 Constant Scan と Nested Loops の間に Parallelism 操作が現れます。その場合、結合外部にある複数のスレッドがそれぞれ異なるパーティションに対して読み取りや操作を行います。

次の図は、併置結合の並列クエリ プランを示しています。

パーティション分割されたオブジェクトの並列クエリの実行方法

クエリ プロセッサは、パーティション分割されたオブジェクトから選択するクエリに対して並列実行を使用します。クエリ プロセッサでは、実行方法の一環として、クエリに必要なテーブル パーティションと各パーティションに割り当てるスレッドの数を決定します。ほとんどの場合、クエリ プロセッサは、各パーティションに同数またはほぼ同数のスレッドを割り当て、パーティション全体でクエリを並列実行します。以降、スレッドの割り当てについてさらに詳しく説明します。

スレッドの数がパーティションの数よりも少ない場合、クエリ プロセッサが各スレッドを別々のパーティションに割り当てると、最初に、スレッドが割り当てられていないパーティションが 1 つ以上残ります。パーティションでスレッドの実行が完了すると、クエリ プロセッサは、各パーティションに 1 つのスレッドが割り当てられるまで、そのスレッドを次のパーティションに割り当てます。これが当てはまるのは、クエリ プロセッサがスレッドを他のパーティションに再割り当てする場合に限ります。

スレッドの数とパーティションの数が同じ場合、クエリ プロセッサは各パーティションに 1 つのスレッドを割り当てます。スレッドの終了時に、そのスレッドが別のパーティションに再割り当てされることはありません。

スレッドの数がパーティションの数よりも多い場合、クエリ プロセッサは各パーティションに同じ数のスレッドを割り当てます。スレッドの数がパーティションの数で割り切れないと、クエリ プロセッサは、使用できるすべてのスレッドを使用するために、一部のパーティションにスレッドを 1 つ多く割り当てます。パーティションが 1 つしかない場合は、すべてのスレッドがそのパーティションに割り当てられることに注意してください。次の図では、4 個のパーティションと 14 個のスレッドがあります。各パーティションには 3 つのスレッドが割り当てられ、さらに 2 つのパーティションには追加のスレッドが割り当てられて、合計で 14 スレッドが割り当てられています。スレッドは、終了時に別のパーティションに再割り当てされることはありません。

上の例では簡単にスレッドを割り当てる方法を示していますが、実際の方法はより複雑で、クエリの実行中に発生する他の変数を使用します。たとえば、テーブルがパーティション分割され、その列 A にクラスタ化インデックスが設定されている場合、クエリで述語句 WHERE A IN (13, 17, 25) を使用すると、クエリ プロセッサは、1 つ以上のスレッドを、各テーブル パーティションではなくスレッドの 3 つの各シーク値 (A=13、A=17、および A=25) に割り当てます。これらの値を含むパーティションでクエリを実行すれば十分です。これらのシーク述語がすべて同じテーブル パーティションに含まれる場合は、すべてのスレッドが同じテーブル パーティションに割り当てられます。

別の例を挙げると、テーブルの列 A に境界点 (10、20、30) が設定された 4 つのパーティション、列 B のインデックス、クエリに述語句 WHERE B IN (50, 100, 150) が含まれるとします。テーブル パーティションは A の値に基づいているため、B の値はどのテーブル パーティションにも存在する可能性があります。したがって、クエリ プロセッサは、4 つの各テーブル パーティションで、B の 3 つの値 (50、100、150) を検索します。クエリ プロセッサは、これらの 12 個の各クエリ スキャンを並列実行できるように、スレッドを均等に割り当てます。

ベスト プラクティス

大きなパーティション テーブルおよびパーティション インデックスの大量のデータにアクセスするクエリのパフォーマンスを向上するために推奨するベスト プラクティスを次に示します。

• 各パーティションを多くのディスクにわたってストライピングします。

• 可能な場合は、アクセス頻度の高いパーティションまたはすべてのパーティションを保持できる十分なメイン メモリがあるサーバーを使用して、I/O コストを軽減します。

• クエリ対象のデータがメモリ内に収まらない場合は、テーブルおよびインデックスを圧縮します。これにより、I/O コストを軽減します。

• 高速なプロセッサおよびできるだけ多くのプロセッサ コアを搭載したサーバーを使用して、並列クエリ処理機能を活用します。

• サーバーに十分な I/O コントローラの帯域幅があることを確認します。

• すべての大きなパーティション テーブルにクラスタ化インデックスを作成して、B ツリーのスキャンの最適化を活用します。

• パーティション テーブルへのデータの一括読み込みを行う場合は、ホワイト ペーパー「パーティション テーブルに一括データを読み込む」に示す推奨事項に従います。

例

次の例では、7 つのパーティションを持つ 1 つのテーブルを含むテスト データベースを作成します。この例でクエリを実行し、コンパイル時と実行時の両方のプランのパーティション分割情報を表示する場合は、前に説明したツールを使用します。

注意
この例では、100 万以上の行をテーブルに挿入します。この例を実行すると、ハードウェアによっては数分かかる場合があります。この例を実行する前に、1.5 GB を超えるディスク領域が確保されていることを確認してください。

USE master;
GO
IF DB_ID (N'db_sales_test') IS NOT NULL
    DROP DATABASE db_sales_test;
GO
CREATE DATABASE db_sales_test;
GO
USE db_sales_test;
GO
CREATE PARTITION FUNCTION [pf_range_fact](int) AS RANGE RIGHT FOR VALUES 
(20080801, 20080901, 20081001, 20081101, 20081201, 20090101);
GO
CREATE PARTITION SCHEME [ps_fact_sales] AS PARTITION [pf_range_fact] 
ALL TO ([PRIMARY]);
GO
CREATE TABLE fact_sales(date_id int, product_id int, store_id int, 
    quantity int, unit_price numeric(7,2), other_data char(1000))
ON ps_fact_sales(date_id);
GO
CREATE CLUSTERED INDEX ci ON fact_sales(date_id);
GO
PRINT 'Loading...';
SET NOCOUNT ON;
DECLARE @i int;
SET @i = 1;
WHILE (@i<1000000)
BEGIN
    INSERT INTO fact_sales VALUES(20080800 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
GO
DECLARE @i int;
SET @i = 1;
WHILE (@i<10000)
BEGIN
    INSERT INTO fact_sales VALUES(20080900 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
PRINT 'Done.';
GO
-- Two-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080802 AND 20080902
GROUP BY date_id ;
GO
SET STATISTICS XML OFF;
GO
-- Single-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080801 AND 20080831
GROUP BY date_id;
GO
SET STATISTICS XML OFF;
GO

関連項目

参照

概念