パーティション分割列以外の列で TOP、MAX、または MIN 集計句を実行すると、SQL Server のパフォーマンスが低下しました

この記事は、列に対して TOP、 MAX 、または集計句を実行するときに SQL Server のパフォーマンスが低下する問題 MIN 回避するのに役立ちます。

元の製品バージョン: SQL Server
元の KB 番号: 2965553

現象

Microsoft SQL Server にパーティション テーブルがあるとします。 テーブルの列に対して TOP、 MAX 、または MIN 集計句を実行すると、パフォーマンスが低下する可能性があります。

Note

この問題は、パーティション分割列でのみ発生しません。

回避策

この問題を回避するには、各パーティションの TOP N 要素を収集するクエリを作成します。 次に、その要素のコレクションから TOP N 要素を見つけます。

たとえば、4 つのパーティションを持つテーブル T1 があり、パーティション関数が PF1。 テーブルは列PCOLでパーティション分割され、T1.c1のインデックスidx_c1。 次のクエリを実行すると、パフォーマンスの問題が発生する可能性があります。

SELECT TOP 3 T1.c1, T1.c2

FROM dbo.T1

ORDER BY T1.c1

この問題を回避するには、次の手順に従ってください。

1. 特定のパーティション <partition_number>の上位 3 つの要素を見つけます。

   SELECT TOP 3 T1.c1, T1.c2
    FROM dbo.T1
    WHERE $PARTITION.PF1(PCOL) = < **partition_number** > AS A(c1, c2)
    ORDER BY T1.c1;
   

2. 4 つすべてのパーティションの上位 3 つの要素を見つけます。

   SELECT TOP 3 A.c1, A.c2
   FROM (VALUES((1),(2),(3),(4)) AS P( partition_number )
    CROSS APPLY ( SELECT TOP 3 (T1.c1, T2.c2) 
    FROM dbo.T1
    WHERE $PARTITION.PF1(T1.PCOL) = P.partition_number 
    ORDER BY T1.c1 ) AS A
   ORDER BY A.c1
   

3. 残念ながら、テーブルが再パーティション分割されている場合は、新しい数のパーティションを使用するためにこれらのクエリを書き直す必要があります。 ただし、 sys.partitionsからパーティションの数を取得することもできます。 したがって、パーティションの定数リストを使用する代わりに、次の SQL スクリプトを使用できます。

   SELECT TOP 3 A.c1, A.c2
   FROM sys.partitions AS P
    CROSS APPLY ( SELECT TOP 3 T1.c1, T2.c2)
    FROM dbo.T1
    WHERE $PARTITION.PF1(T1.col1) = P.partition_number 
    ORDER BY T1.c1 ) AS A
    WHERE P.object_id = OBJECT_ID('dbo.T1') 
    AND P.index_id = INDEXPROPERTY( OBJECTID('dbo.T1'), 'idx_c1', 'INDEXID')
    ORDER BY a;
   

   Note

   この記事では、例として ORDER BY 句で TOP N を使用します。 MAX 句と MIN 句にも同様の問題があります。 そのため、順序を昇順または降順に設定して、TOP 1 クエリに変換することで回避できます。

詳細

パーティション分割されていないテーブルでインデックス付き列の TOP N 行を照会する場合、通常、クエリのパフォーマンスは良好です。 これは、クエリ プランがインデックスをスキャンして、上位 n 個の要素が何であるかを判断するためです。

ただし、パーティション テーブルの場合、インデックスもパーティション分割される可能性があるため、現在はそうではありません。 つまり、インデックスに対してクエリを実行するだけでは、上位 N 個の要素を特定することはできません。 これらの要素は、すべてのパーティションに分散できます。 たとえば、2 つのパーティション P0 と P1 が 0 でパーティション分割されたテーブル "a" がある次の場合を考えてみます。

パーティション	Key	値
P0	-2	1
P0	-1	1
P0	0	12
P1	1	1
P1	2	1
P1	3	15

各インデックスはパーティション分割されているため、SQL Server では、インデックスを同時にスキャンして最大値を特定することはできません。 代わりに、テーブルの各要素をスキャンして最大値を決定します。 数百万行のテーブルでは、このプロセスは非効率的な場合があります。