SE の雑記

SQL Server では実行プランを表示した際に各演算子 (イテレーター) に下図のようなコストラベルが出力されます。

クエリのチューニングを実施する際には、このコストラベルの数値が高い操作についての改善を行うことが多いかと思いますが、コストラベルだけでは判断できない情報もあります。

実行プランのコストラベル

SQL Server のクエリ実行時のコストですが、実行プランを生成するためにオプティマイザが算出するコストが一般的に思い浮かべる情報ではないでしょうか。

推定実行プラン / 実際の実行プランを表示した際に出力されるコストラベルですが、この値は推定コストに基づく値となります。
複数のステートメントを実行した際のバッチ相対のコストについても、実際の操作ベースのコストとは異なる可能性があります。

実行プランのコストはハードウェアの構成やデータサイズによっても値が異なってきます。

• SQL Relationship between Subtree Cost and Performance Time

そのため、プランに表示されるコストは、SQL Server を実行している環境上での参考の値として捉え、コストの数値にこだわりすぎずに、実行プラン全体を確認することが重要です。

冒頭に記載した画像ですが、二つのクエリを実行して、実行プランは同じ形となっており、バッチ相対のコストも 50% ずつですので、このコストラベルだけで比較すると、同様の処理負荷のように見えてしまうのではないでしょうか。

しかし、実際には処理時間や I/O の発生状況は異なります。

• クエリ 1
• CPU 時間 = 5201 ミリ秒、経過時間 = 715 ミリ秒
• テーブル ‘PartitionTest’。スキャン数 12、論理読み取り数 142573、物理読み取り数 0
• クエリ 2
• CPU 時間 = 5969 ミリ秒、経過時間 = 825 ミリ秒
• テーブル ‘PartitionTest’。スキャン数 12、論理読み取り数 242183、物理読み取り数 0

クエリ 1 とクエリ 2 の CPU 時間 / 経過時間については数 100 ミリ秒の差のため、誤差と判断できるかもしれませんが、論理読み取り数については、倍近くの差が出ていることが確認できます。

複数のクエリの実行効率を確認する際には、実行プランのバッチ相対 / コストラベルからではどの部分に差が出ているかの判断ができない可能性があります。

今回のケースであれば「読み取った行数」の項目を確認することで差が出ていることが確認できますので、コストラベルでなく、演算子がアクセスしたデータ数に注目して比較を行う必要があります。

今回のクエリでは、実行プランの形は同じですが、「読み取った行数」に差が出ています。
この差が出た理由についても解説しておきたいと思います。

検索時のデータ型の一致の重要性

今回、クエリ 1 / 2 では次のような検索を実施しています。

クエリ 1 : WHERE DataFlag = CAST(0 as bit)
クエリ 2 : WHERE DataFlag = 0

DataFlag は bit 型のデータとなっており 0 or 1 の項目です。

この列はパーティショニングに使用している列であり、0 / 1 の値で論理的に格納領域が分かれるように設定を行っています。

クエリ 1 は、明示的に bit 型にキャストをし、クエリ 2 は整数型の定数で検索を行っています。

一見、CAST の有無によって検索効率は変わらないように見えますが、前述のとおり、アクセスされるデータ数には大きく差が出ています。

クエリ 1 : OBJECT:([wordpress].[dbo].[PartitionTest].[CIX_Partitiontest]), SEEK:([PtnId1000]=(1)),  WHERE:([wordpress].[dbo].[PartitionTest].[DataFlag]=(0)) ORDERED FORWARD
クエリ 2 : OBJECT:([wordpress].[dbo].[PartitionTest].[CIX_Partitiontest]),  WHERE:([wordpress].[dbo].[PartitionTest].[DataFlag]=(0))

どちらも DataFlag が検索述語として使用されていますが、クエリ 1 の場合は「PtnId1000」に対しての Seek が行われています。

これは、特定のパーティションのみがアクセスされているということを示します。

クエリ 2 については、通常の Scan が行われており、特定のパーティションのみにアクセスされるということが行われていません。

今回は bit 型で試していますが、datetime2 のような日付型でもミリ秒の精度の指定のレベルで、検索条件のデータ型を明示的に datetime(x) でキャストをしないと似たような現象が発生するケースがあります。

検索句を記述する際、「CAST(DataFlag AS bit)」のように、実際のデータ列をキャストしてしまうと、インデックスの設定が難しくなる (SARGAbility が低下する) ことは、一般的に知られているかと思います。

今回のように「DataFlag = 0」ではなく、「DataFlag = CAST(0 AS bit)」のように、定数 (または変数) を使用した検索で、明示的に型を合わせることで、パフォーマンスが向上するというようケースもあることは重要かと思います。

(DataFlag を tinyint に設定した場合は発生していなかったので、データ型の優先順位 ではなく、整数型定数を使用した場合に、bit ではなく数値型として認識されているような気がします)

パーティショニングをしているテーブルで Partition Elimination がどうして行われないのか悩んでしまったのですが、パーティションで使用しているデータ型によっては、データ型を明示的に一致させることで、Partition Elimination が行われ検索効率が向上するというパターンでしたとさ。