データベースエンジンチューニングアドバイザー (DTA) の推奨事項を使用したパフォーマンスの強化

[アーティクル]
05/30/2023

適用対象:SQL Server

データウェアハウスと分析ワークロードのパフォーマンスは列ストア インデックスで大幅に上がります。特に、大きなテーブルをスキャンするクエリで効果を発揮します。 行ストア (B+-tree) インデックスは、比較的少量のデータにアクセスし、特定の値または値範囲を検索するクエリで最も効果を発揮します。行ストアインデックスは行を並べ替えた上で提示するので、クエリ実行プランの並べ替えコストの削減にもなります。そのため、行ストアインデックスと列ストアインデックスをどのように組み合わせるかという選択はアプリケーションのワークロードに依存します。

データベースエンジンチューニングアドバイザー (DTA) は、SQL Server 2016 以降、所与のデータベースワークロードを分析することで行ストアインデックスと列ストアインデックスの最適な組み合わせを推奨できるようになりました。

ワークロードパフォーマンスに関する DTA 推奨の長所を示すために、実際のお客様のワークロードで実験しました。お客様のワークロードごとに、DTA に個々のクエリとクエリの完全ワークロードを分析させました。 3 つの選択肢を考慮します。

列ストアのみ: DTA を使用せず、すべてのテーブルに関して列ストアインデックスのみを作成します。
DTA (行ストアのみ): 行ストアインデックスのみを推奨するオプションを指定して DTA を実行します。
DTA (行ストア + 列ストア): 行ストアインデックスと列ストアインデックスの両方を推奨するオプションを指定して DTA を実行します。

この場合、推奨されたインデックスを実装しました。クエリまたはワークロードを複数回実行し、その平均 CPU 時間をミリ秒単位で報告します。次の図は、2 つの異なる顧客データベースを対象にワークロードの CPU 時間をミリ秒単位で描画したものです。 y 軸 (CPU 時間) では対数スケールが使用されています。

Screenshot of a bar graph showing DTA columnstore rowstore performance.

物理的設計の混在が必要: Customer 1 Query 1 に相当する最初のバーセットの DTA (行ストア + 列ストア) から、 4 つの列ストアインデックスと 6 つの行ストアインデックスのセットが推奨されます。結果的に、列ストアインデックスのみや DTA (行ストアのみ) と比較し、CPU 時間が 2.5 ～ 4 分の 1 に短縮されます。これは、クエリが 1 つの場合でも、行ストアインデックスと列ストアインデックスが混在する物理的設計の長所を示しています。

行ストアインデックス推奨の効果: 2 つ目と 3 つ目のバーセット (Customer 1 Query 2 と Customer 2 Query 1 に相当) では、適切な行ストアインデックスから効果を得られる選択的フィルター述語がクエリに与えられるケースです。いずれのクエリでも、DTA (行ストアのみ) と DTA (行ストア + 列ストア) から、行ストアインデックスのみが推奨されることがわかります。以上の例からは、列ストアインデックスを推奨するオプションで DTA を呼び出すときであっても、そのコストに基づく手法により、ワークロードに対して実際に効果がある場合にのみ、列ストアインデックスが推奨されることもわかります。

列ストアインデックス推奨の効果: Customer 2 Query 2 に対応する 4 つ目のバーセットでは、列ストアインデックスの効果がありえる大きなテーブルがスキャンされます。 DTA (行ストアのみ) が生成する推奨では、列ストアインデックスが存在するときと比較し、CPU 時間が増えます。 DTA (行ストア + 列ストア) は、列ストアのみのオプションのクエリ実行パフォーマンスに匹敵する列ストアインデックスを推奨します。

複数のクエリによるワークロードの推奨の効果: Customer 2 の完全なワークロードに相当する最後のバーセットは、ワークロードの複数のクエリを分析し、ワークロード全体の実行コストを改善できる行ストアと列ストアの適切なセットを推奨する DTA の能力を例示するものです。 DTA (行ストア + columnstore) から、4 つの列ストアインデックスと数十の行ストアインデックスが推奨されます。結果的に、列ストアインデックスのみを作成するオプションと比較し、ワークロードを 1 桁以上改善します。DTA (行ストアのみ) と比較した場合、4 ～ 5 倍の改善になります。

まとめると、上の例により、SQL Server Database Engine でサポートされる行ストアと列ストアの両方を適宜活用し、ワークロードの CPU 時間を大幅に短縮できるインデックスの組み合わせを推奨する DTA の能力が実証されることになります。