都内で働くSEの技術的なひとりごと

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

SQL Server のチューニングについてまとめてみる - その 24 - ( これもチューニングについてまとめてみるだった )

 一応、その 24 でww
ryuchan.hatenablog.com

 ここ二日寝続けたので、腰痛が悪化...(´;ω;`)

カラー版 9割の腰痛は自分で治せる (中経の文庫)

カラー版 9割の腰痛は自分で治せる (中経の文庫)

臨床研究で実証! 80%以上が改善!  「ねたままストレッチ」で腰痛は治る!

臨床研究で実証! 80%以上が改善! 「ねたままストレッチ」で腰痛は治る!

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本

 これ、最強。

SQL Server 2012 SP4 をみてみる

 いい天気に恵まれました。そして、数か月ぶりの連休。ゆっくり過ごせました。しかし、明日からまた怒涛の出張が待っています。今月は国内外合わせてあと 10 回の搭乗がまっています...さて、気を取り直して...先週、SQL Server 2012 の SP4 がリリースされました。まだまだ、SQL Server 2012 を使用しているシステムも多いかと思います。(自分の参画しているプロジェクトではまだまだ主力です...)
blogs.msdn.microsoft.com

2014 と 2016 の機能がいくつか 2012 にフィードバックされますね。DMV、拡張イベントとか、クエリプランの機能強化とか、DBCC CLONEDATABASなど監視、パフォーマンスチェックなどに必要な機能がフィードバックされているのはうれしいですね。

  • All fixes and Cumulative Updates (CUs) for SQL Server 2012 up to and including SQL Server 2012 SP3 CU10.Scalability and performance improvements for SQL Server.
  • Additional monitoring capabilities through enhancements in DMV, Extended Events and Query Plans and the ability to clone the database including statistics with DBCC CLONEDATABASE.
  • New improvements based on Connect feedback items filed by the SQL Server Community.
  • Some of the improvements originally introduced in SQL Server 2014 SP2 and SQL Server 2016 SP1.

 Premium Assuranceを使用すると製品サポートを6年間延長できますyo!

Customers running SQL Server 2012 can extend their product support lifecycle by six years with Premium Assurance. Learn more about this option and read the Premium Assurance datasheet to explore this opportunity to stay compliant with minimal disruption.

 色々試そうかと思いましたが、明日の顧客先に持っていく資料ができていないので、ここでおわりでございます

 今日は秋冬物のスーツ買ったり、壊れてしまったリモワのボレロの代わりを探してました。ここ一年くらいは、多いときに週4回くらい使っていたので、壊れても仕方ない、かな。


 二輪が好きなんですが、最近四輪のスーツケースが売れ筋らしく、二輪のスーツケースがあまり売ってません。色々さがした結果、見た目で判断して、エンド―鞄株式会社 の『フリークエンター』にチョイス。

ORDER BY について簡単に説明してみる

 最近、すっかりブログを書かなくなりました。習慣化しておかないといけませんね。去年はこんなに書いていたのに...一か月に 10 も投稿していたんだなぁ。
f:id:koogucc11:20170926231920p:plain

 クエリのレビューをしていてふと気が付いたことを書いてみます。それは、ORDER BY。意外と考慮からもれている場合が多く見受けれます。クエリを実行する上で、CPU パワーを使うし、IO も大量に発生させてしまう原因になりやすいです。ORDER BY を効率的に処理させるには、兎に角 Sort オペレータをなるべく出現させないようにする、が鉄則です。早速、AdventureWorks データベースを使って実験してみましょう。下記のクエリを SQL Server Management Studio で実行されてみましょう。Sort オペレータが出現しているのが確認できます。

SELECT
    SalesOrderID,
    RevisionNumber,
    OrderDate,
    DueDate,
    ShipDate,
    Status,
    OnlineOrderFlag,
    SalesOrderNumber,
    PurchaseOrderNumber,
    AccountNumber,
    CustomerID,
    SalesPersonID,
    TerritoryID,
    BillToAddressID,
    ShipToAddressID,
    ShipMethodID,
    CreditCardID,
    CreditCardApprovalCode,
    CurrencyRateID,
    SubTotal,
    TaxAmt,
    Freight,
    TotalDue,
    Comment,
    rowguid,
FROM
    Sales.SalesOrderHeader WITH(FORCESEEK, INDEX(IX_SalesOrderHeader_SalesPersonID))
WHERE
    SalesPersonID = 277
ORDER BY
    OrderDate

f:id:koogucc11:20170926233446p:plain

 上記のクエリの場合、OrderDate で ORDER BY しているため、OrderDate をインデックスに含めることでインデックスによる並び替えがされている状態になるため、Sort オペレーターをなくすことが可能です。インデックスを変更してみましょう。

CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_SalesPersonID ON Sales.SalesOrderHeader
(
    SalesPersonID ASC,
    OrderDate ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

f:id:koogucc11:20170926234214p:plain

 再度、下記のクエリを実行してみます。Sort オペレーターがなくなっていることが判断できます。

SELECT
    SalesOrderID,
    RevisionNumber,
    OrderDate,
    DueDate,
    ShipDate,
    Status,
    OnlineOrderFlag,
    SalesOrderNumber,
    PurchaseOrderNumber,
    AccountNumber,
    CustomerID,
    SalesPersonID,
    TerritoryID,
    BillToAddressID,
    ShipToAddressID,
    ShipMethodID,
    CreditCardID,
    CreditCardApprovalCode,
    CurrencyRateID,
    SubTotal,
    TaxAmt,
    Freight,
    TotalDue,
    Comment,
    rowguid,
FROM
    Sales.SalesOrderHeader WITH(FORCESEEK, INDEX(IX_SalesOrderHeader_SalesPersonID))
WHERE
    SalesPersonID = 277
ORDER BY
    OrderDate

f:id:koogucc11:20170926234428p:plain

 ついでに付加列を足せば、スッキリとした実行プランになりますね。
f:id:koogucc11:20170926235045p:plain

 実際、業務でクエリを書いていると、完全に Sort オペレーターを排除することは相当困難ではありますが....

 秋めいてきました。夏とは違った感じでビールの美味しい季節になってきました。

いまさらだなぁと思いつつ、キャッシュプランがどのくらいのサイズがあるのか確認してみる

 怒涛の出張が継続しております。今年も出張三昧です(笑)(´;ω;`)
f:id:koogucc11:20170907233018p:plain

 さて、今回チューニングの一環で指示により、キャッシュプランが増えそうだなぁというクエリになりそうで..... あ、そういえばキャッシュプランのサイズって一度もみたことないとふと思ったので、早速サイズをチェックしてみました。下記のクエリを SQL Server Management Studio で実行してみましょう。各クエリのキャッシュサイズが確認できます。

SELECT
    [クエリ] = st.text,
    [キャッシュサイズ(byte)] = cp.size_in_bytes
FROM
    sys.dm_exec_cached_plans cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE
    cp.cacheobjtype = 'Compiled Plan'

f:id:koogucc11:20170907232701p:plain

 総合計を出力してみます。あれ、エラーでました。int の範囲を超えるサイズがキャッシュされているのか。

SELECT
    [キャッシュサイズ合計(byte)] = SUM(cp.size_in_bytes)
FROM
    sys.dm_exec_cached_plans cp
WHERE
    cp.cacheobjtype = 'Compiled Plan'

f:id:koogucc11:20170907232718p:plain

 BIGINT でキャストしましょう。正しく出力できました。こんなにキャッシュされていたのか...普通なのでしょうか?

SELECT
    [キャッシュサイズ合計(byte)] = SUM(CAST(cp.size_in_bytes AS BIGINT))
FROM
    sys.dm_exec_cached_plans cp
WHERE
    cp.cacheobjtype = 'Compiled Plan'

f:id:koogucc11:20170907232725p:plain

 Mac Book Pro ほしい。

 けど、Lenovo Yoga 920 もいいな。
www3.lenovo.com

 シャンパンゴールドがなくなったのは、非常に残念。けど、ブロンズいいな。
f:id:koogucc11:20170907234441p:plain
f:id:koogucc11:20170907234434p:plain