一応、その 24 でww
ryuchan.hatenablog.com
ここ二日寝続けたので、腰痛が悪化...(´;ω;｀)
一回3秒 これだけ体操 腰痛は「動かして」治しなさい (講談社+α新書)
臨床研究で実証! 80%以上が改善! 「ねたままストレッチ」で腰痛は治る!
今週月曜日から本日まで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] )
そして、実行プランは下図の通りです。一見、そんなに問題のないプランに見えますね。
このような残念なクエリでも、テーブルの件数が数十万、数百万であれば大した問題にはなりませんが、これが数億オーバーのテーブルになると途端に問題が発生します。上図のクラスターシークで数千万件ヒットしたとします。それだけでも大惨事ですが、その次のフィルター処理も非常に時間がかかってしまいます。しかも、件数が多いことから 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 も減少しています。
ご興味のある方は、STATISTICS TIME,IO などで確認してみてください。使い方は下記の記事を参照してください。
ryuchan.hatenablog.com
実行プランの見方がわからない方は、下記のシリーズを。
ryuchan.hatenablog.com
チューニングについては下記のシリーズを。
ryuchan.hatenablog.com
行数が非常に多い場合の一つの例として見ていただければ幸いです。チューニングには色々な経験と観点が必要ですので。
※ちなみに、実際のプランはこんな感じです。見ただけで寒気がしますね(笑)
相変わらす SQL Server 本は少ないなぁ....
SQL Server 2016データベース構築・管理ガイド Enterprise対応
風邪対策しないとね。
![サラヤハンドラボ 手指消毒アルコールスプレーVH 300mL [指定医薬部外品]](https://images-fe.ssl-images-amazon.com/images/I/419JCUdeTlL._SL160_.jpg "サラヤハンドラボ 手指消毒アルコールスプレーVH 300mL [指定医薬部外品]")
サラヤハンドラボ 手指消毒アルコールスプレーVH 300mL [指定医薬部外品]
いい天気に恵まれました。そして、数か月ぶりの連休。ゆっくり過ごせました。しかし、明日からまた怒涛の出張が待っています。今月は国内外合わせてあと 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回くらい使っていたので、壊れても仕方ない、かな。
![RIMOWA リモワ Bolero ボレロ Business MultiWheel ビジネスマルチホイール 39L matte black マットブラック 865.50.32.4 スーツケース ビジネス [並行輸入品]](https://images-fe.ssl-images-amazon.com/images/I/41pokyHTdoL._SL160_.jpg "RIMOWA リモワ Bolero ボレロ Business MultiWheel ビジネスマルチホイール 39L matte black マットブラック 865.50.32.4 スーツケース ビジネス [並行輸入品]")
二輪が好きなんですが、最近四輪のスーツケースが売れ筋らしく、二輪のスーツケースがあまり売ってません。色々さがした結果、見た目で判断して、エンド―鞄株式会社 の『フリークエンター』にチョイス。
最近、すっかりブログを書かなくなりました。習慣化しておかないといけませんね。去年はこんなに書いていたのに...一か月に 10 も投稿していたんだなぁ。
クエリのレビューをしていてふと気が付いたことを書いてみます。それは、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
上記のクエリの場合、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)
再度、下記のクエリを実行してみます。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
ついでに付加列を足せば、スッキリとした実行プランになりますね。
実際、業務でクエリを書いていると、完全に Sort オペレーターを排除することは相当困難ではありますが....
秋めいてきました。夏とは違った感じでビールの美味しい季節になってきました。
【2017年リニューアル】 新・キリン 一番搾り 350ml×24本
怒涛の出張が継続しております。今年も出張三昧です(笑)(´;ω;｀)
さて、今回チューニングの一環で指示により、キャッシュプランが増えそうだなぁというクエリになりそうで..... あ、そういえばキャッシュプランのサイズって一度もみたことないとふと思ったので、早速サイズをチェックしてみました。下記のクエリを 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'
総合計を出力してみます。あれ、エラーでました。int の範囲を超えるサイズがキャッシュされているのか。
SELECT [キャッシュサイズ合計(byte)] = SUM(cp.size_in_bytes) FROM sys.dm_exec_cached_plans cp WHERE cp.cacheobjtype = 'Compiled Plan'
BIGINT でキャストしましょう。正しく出力できました。こんなにキャッシュされていたのか...普通なのでしょうか？
SELECT [キャッシュサイズ合計(byte)] = SUM(CAST(cp.size_in_bytes AS BIGINT)) FROM sys.dm_exec_cached_plans cp WHERE cp.cacheobjtype = 'Compiled Plan'
Mac Book Pro ほしい。
けど、Lenovo Yoga 920 もいいな。
www3.lenovo.com
シャンパンゴールドがなくなったのは、非常に残念。けど、ブロンズいいな。
お久しぶりです。最近、色々と忙しくて記事が全然書けていませんでした。新しい技術にも全くキャッチアップできず、日々雑な仕事ばかりしていると、気持ちがすさんでいきますし、技術力が下がっていく一方です。11月には Tech Summit があります。このようなカンファレンスにも出ていないと、モチベーション保てないです....
Microsoft Tech Summit 2017 | インフラエンジニア、アーキテクト、IT 戦略立案に関わる皆様の為の技術カンファレンス - Microsoft Events & Seminars
さて、気を取り直して...最近、SQL Server Management Studio 17.2 がリリースされてました。
Download SQL Server Management Studio (SSMS) | Microsoft Docs
New in SSMS: Performance Dashboard built-in だそうなので、ちょっと見てみましょう。
blogs.msdn.microsoft.com
ということで、17.2 をインストール。
下図のようにクリックします。
パフォーマンスダッシュボードが表示されます。日本語化けてますが、気にせず使いましょう。
CPU の高いクエリを抽出したりできます。棒グラフをクリックすると、
実際のクエリを参照できます。便利ですね。
dmv、dmf に関してまだあまり理解していない！という方にはお勧めです。下図のように SQL Server Profiler 等を用いて、どのようなクエリが発行されているかを参照するのも勉強になるかもしれません。
働きすぎ、社畜やん。
