都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

都内でサラリーマンやってます。SQL Server を中心とした (2023年からは Azure も。) マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。英語の勉強のため、英語の

実行プランの読み方をまとめてみる - その11 ( Where 句の条件の型が列の型と異なる場合の実行プランを読んでみる ) -

 実行プランの読み方は情報も少ないので、内容の理解に苦しんでいる方も多いかもしれません。過去にはいくつか投稿しているので、そちらも参考にしてください。
ryuchan.hatenablog.com
ryuchan.hatenablog.com
ryuchan.hatenablog.com
ryuchan.hatenablog.com
ryuchan.hatenablog.com
ryuchan.hatenablog.com
ryuchan.hatenablog.com
ryuchan.hatenablog.com
ryuchan.hatenablog.com
ryuchan.hatenablog.com

 さて、過去の投稿を参考にしつつ、今回投稿する記事も参考にして、実行プランをスラスラ読めるようにしましょう。早速下記のクエリを実行して見ましょう。
※データベースは、ここからダウンロードしてください。
※クエリはわざと不適切な状態にしています。

-- OrderDate 型は datetime なので型が間違っている。
DECLARE @min_date datetime2
DECLARE @max_date datetime2

SET @min_date = '2011-05-31 00:00:00.000'
SET @max_date = '2011-07-31 00:00:00.000'

SET STATISTICS PROFILE ON

SELECT 
    SalesOrderID,
    RevisionNumber,
    OrderDate
FROM 
    -- OrderDate のインデックスを作成した。
    Sales.SalesOrderHeader WITH (INDEX(IX_SalesOrderHeader_OrderDate))
WHERE 
    OrderDate > @min_date
AND OrderDate < @max_date

SET STATISTICS PROFILE OFF

 実行プランは下図の通りです。
f:id:koogucc11:20180819234905p:plain

 Computer Scalar、Concatenation, Merge Interval オペレータって何しているかはっきりわかりませんよね?それぞれのオペレーターのプロパティを見ても、定義済みの値が [Expr1008] = (Expr1003, Expr1006), [Expr1009] = (Expr1004, Expr1007), [Expr1010] = (Expr1002, Expr1005) とか、出力一覧がExpr1003, Expr1004, Expr1002とか意味不明ですね。expr といえば、整数計算とか式評価を思い浮かべますが....

  • Compute Scalar
    f:id:koogucc11:20180819235900p:plain

  • Concatenation
    f:id:koogucc11:20180819235903p:plain

  • Merge Interval
    f:id:koogucc11:20180819235906p:plain

 さて、今回のような実行プランを見ていくにはグラフィカルな実行プランでは詳細はわかりません。各オペレーターの詳細情報を参照するには、実行プランの XML を参照するか、SET STATISTICS PROFILE ON の結果を参照する必要があります。今回は、SET STATISTICS PROFILE ON の結果を見ていくことにしましょう。今回実行したクエリには、SET STATISTICS PROFILE ON を付加してあるので、下図の赤枠の情報も出力されているかと思います。赤枠部分の情報を詳しく見ていきましょう。
f:id:koogucc11:20180820001109p:plain

 まず、Compute Scalar オペレーターの内容から参照してみましょう。GetRangeWithMismatchedTypes という関数が発生しています。文字通り型指定が間違っているけど、範囲を取得するみたいな関数(あっているかはわかりません。)ですね。下記のパターンでは、Expr1003 = @min_date, Expr1004 = NULL がそれぞれ割り当てられます。
※(6)が何に使用されるのかイマイチ推測できない…

([Expr1003],[Expr1004],[Expr1002])=GetRangeWithMismatchedTypes([@min_date],NULL,(6))

 下記のパターンでは、Expr1006 = NULL , Expr1007 = @max_date がそれぞれ割り当てられます。

([Expr1006],[Expr1007],[Expr1005])=GetRangeWithMismatchedTypes(NULL,[@max_date],(10))

 上記の結果をクエリに当てはめると下記のようになります。

SELECT
    SalesOrderID,
    RevisionNumber,
    OrderDate
FROM
    Sales.SalesOrderHeader WITH(INDEX(IX_SalesOrderHeader_OrderDate))
WHERE
    (
        OrderDate > Expr1003
    AND OrderDate < Expr1004
    )
AND (
        OrderDate < Expr1007
    AND OrderDate > Expr1006
    )

 次に、Concatenation オペレーターをみていきましょう。 OrderDate > Expr1003 と OrderDate > Expr1006、OrderDate < Expr1004 と OrderDate < Expr1007 が結合されます。

[Expr1008] = ([Expr1003], [Expr1006]), [Expr1009] = ([Expr1004], [Expr1007]), [Expr1010] = ([Expr1002], [Expr1005])

 次に、Merge Interval オペレーターでダブった範囲が消去されます。OrderDate > Expr1003 と OrderDate > Expr1006 から、Expr1008 = @min_date となります。OrderDate < Expr1004 と OrderDate < Expr1007 から、Expr1009 = @max_date となります。
 
 最後に Expr1008 と Expr1009 でシークを行ないます。

OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[IX_SalesOrderHeader_OrderDate]), SEEK:([AdventureWorks].[Sales].[SalesOrderHeader].[OrderDate] > [Expr1008] AND [AdventureWorks].[Sales].[SalesOrderHeader].[OrderDate] < [Expr1009]) ORDERED FORWARD, FORCEDINDEX

 これは一例ですが、型の指定が間違っているだけで、SQL Server はこのような複雑な処理を行ないます。少しでも負荷を減らすためにも型を正しく指定しましょう。下記の型が正しいクエリを発行してみましょう。

-- datetime2 ではなく、datetime を指定する。
DECLARE @min_date datetime
DECLARE @max_date datetime

SET @min_date = '2011-05-31 00:00:00.000'
SET @max_date = '2011-07-31 00:00:00.000'

SET STATISTICS PROFILE ON

SELECT 
    SalesOrderID,
    RevisionNumber,
    OrderDate
FROM 
    Sales.SalesOrderHeader WITH (INDEX(IX_SalesOrderHeader_OrderDate))
WHERE 
    OrderDate > @min_date
AND OrderDate < @max_date

SET STATISTICS PROFILE OFF

 
 実行プランを参照すると、Computer Scalar、Concatenation, Merge Interval オペレーターが全て無くなっています。かなりスッキリしましたね。
f:id:koogucc11:20180820021533p:plain

 実行プランが単純化されることで、クエリの解析は高速化されますし、キャッシュプランのサイズも小さくなります。その結果、データベースサーバーへの負荷が軽減されます。条件に指定する型は正確に指定するように意識しましょう。

 SQL Server 2017 でのパフォーマンスチューニングはどう変わるんだろう。2012 で止まってる自分は未知の世界。

SQL Server 2017 Query Performance Tuning: Troubleshoot and Optimize Query Performance

SQL Server 2017 Query Performance Tuning: Troubleshoot and Optimize Query Performance