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

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

SQL Server のチューニングについてまとめてみる - その 23 - ( 色々な観点が必要です。 )

 今週月曜日から本日まで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

 ご興味のある方は、STATISTICS TIME,IO などで確認してみてください。使い方は下記の記事を参照してください。
ryuchan.hatenablog.com

 実行プランの見方がわからない方は、下記のシリーズを。
ryuchan.hatenablog.com

チューニングについては下記のシリーズを。
ryuchan.hatenablog.com

 行数が非常に多い場合の一つの例として見ていただければ幸いです。チューニングには色々な経験と観点が必要ですので。

 ※ちなみに、実際のプランはこんな感じです。見ただけで寒気がしますね(笑)
f:id:koogucc11:20171018001241p:plain

 相変わらす SQL Server 本は少ないなぁ....

SQL Server 2016の教科書 開発編

SQL Server 2016の教科書 開発編

SQL Server 2016データベース構築・管理ガイド Enterprise対応

SQL Server 2016データベース構築・管理ガイド Enterprise対応

 風邪対策しないとね。

カルピス 守る働く乳酸菌 L-92 200ml×24本

カルピス 守る働く乳酸菌 L-92 200ml×24本

 これ、最強。