SQL Server のチューニングについてまとめてみる - その 23 - ( 色々な観点が必要です。 ) - 都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

　今週月曜日から本日まで2日間、高熱を出してしまいお休みを頂いておりました。しかも、昨日の夜には子供に感染し、本日は妻にまで感染(´;ω;｀) 非常にまずい状況です。発熱時にはやはり、麻黄湯エキスが効きますね。汗かきますし、短時間で熱が下げられます。
www.rad-ar.or.jp

　まだ、頭の回転がよくならないので、記事書いて元の状態に戻したいと思います。最近、下記の類のクエリを見かけました。(あくまでもサンプルです。実際のものとはことなります(当然ですね(笑)))

SELECT 
    *
FROM
    AdventureWorks2014.Sales.SalesOrderDetail 
WHERE
    SalesOrderID IN (43659,43660,43661,43662,43663,43664,43665,43666,43667,43668,43669,43670,43671,43672,43673,43674,43675,43676,43677,43678,43679,43680,43681,43682,43683,43684,43685,43686,43687,43688,43689)
AND ProductID IN (720,721,722,723,725,726,727,729,730,732,733,736,738,739,741,742)

　下記のインデックスも作成しています。

CREATE NONCLUSTERED INDEX [SampleIndex]
ON  [Sales].[SalesOrderDetail](
        [SalesOrderID] ASC,
        [ProductID] ASC
    ) INCLUDE(
        [SalesOrderDetailID],
        [CarrierTrackingNumber],
        [OrderQty],
        [SpecialOfferID],
        [UnitPrice],
        [UnitPriceDiscount],
        [LineTotal],
        [rowguid],
        [ModifiedDate]
    )

　そして、実行プランは下図の通りです。一見、そんなに問題のないプランに見えますね。
f:id:koogucc11:20171017234433p:plain

　このような残念なクエリでも、テーブルの件数が数十万、数百万であれば大した問題にはなりませんが、これが数億オーバーのテーブルになると途端に問題が発生します。上図のクラスターシークで数千万件ヒットしたとします。それだけでも大惨事ですが、その次のフィルター処理も非常に時間がかかってしまいます。しかも、件数が多いことから SQL Server はクラスターシークから Parallel 処理に切り替えますので、その後の処理もすべて Parallel 処理に切り替わりますので、余計な CPU コストも使用することになってしまいます。

　すこしトリッキーかもしれませんが、下記のように変更するのも一つの手法です。

;WITH SalesOrderDetail_CTE(
    SalesOrderID,
    SalesOrderDetailID,
    CarrierTrackingNumber,
    OrderQty,
    ProductID,
    SpecialOfferID,
    UnitPrice,
    UnitPriceDiscount,
    LineTotal,
    rowguid,
    ModifiedDate)
AS(
SELECT 
    *
FROM
    AdventureWorks2014.Sales.SalesOrderDetail
WHERE
    ProductID IN (720,721,722,723,725,726,727,729,730,732,733,736,738,739,741,742)
)
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43659
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43660
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43661
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43662
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43663
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43664
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43665
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43666
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43667
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43668
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43669
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43670
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43671
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43672
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43673
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43674
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43675
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43676
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43677
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43678
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43679
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43680
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43681
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43682
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43683
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43684
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43685
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43686
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43687
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43688
UNION ALL
SELECT * FROM AdventureWorks2014.Sales.SalesOrderDetail WHERE SalesOrderID = 43689

　実行プランは下図の通りです。一瞬、『ん？』と思われるかもしれませんが、重たい Filter 処理がなくなっており、Where 句条件がすべてシーク述語になっています。
※述語などの内容について下記を参照
ryuchan.hatenablog.com
　その結果、非常に高速にアクセスできています。実際、IO も減少しています。
f:id:koogucc11:20171017235611p:plain