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

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

DBCC TRACEON を動いているシステムに設定しちゃうので、ちゃんと MSDN とか読んでみてやり方を予習してみる

 クエリの動作は奥深いです。先日、DBMS のチューニングで、NO STATS が結構発生していたので、列統計が存在しないものを一気に作成しました。そこで実際発生したのが、複雑かつ超絶に長いクエリのコンパイルによるクエリタイムアウトとパラメータスニッフィングが発生しました。(なんと運が悪い....)

 SQLコンパイル時間は、SQL Server Management Studio で参照することが可能です。下記のクエリを実行してみましょう。(データベースはいつもの AdventureWorks です。)

SET STATISTICS TIME ON;
GO
SELECT ProductID, StartDate, EndDate, StandardCost 
FROM Production.ProductCostHistory
WHERE StandardCost < 500.00;
GO
SET STATISTICS TIME OFF;
GO

f:id:koogucc11:20150419115032p:plain
 
 今回はこのSQL コンパイルの部分で 35秒かかってしまい、クエリがタイムアウトを起こしてしまいました。クエリがタイムアウトを起こすと、SQL Server はキャッシュプランが保持されないため、さらなる大惨事に....これに関しては、接続文字列のタイムアウト値を伸ばすことで対応できます。
 
 パラメータスニッフィングですが、SEの雑記にわかりやすく説明されています。blog.engineer-memo.com
 
 パラメータスニッフィングを解消するには、いくつか方法がありますが、今回は トレースフラグ 4136 を設定します。トレースフラグは、DBCC TRACEON コマンドまたは、Sqlservr.exe のコマンド ライン スタートアップ オプション -T を使用します。インスタンス全体で有効にするには、SQL Server Management Studio で下記のコマンドを実行します。

DBCC TRACEON (4136, -1);

f:id:koogucc11:20150419120235p:plain

 トレースフラグの状態をチェックするには、下記のコマンドを実行します。

DBCC TRACESTATUS(4136);

f:id:koogucc11:20150419120606p:plain

 DBCC TRACEON で設定した値は、SQL Server を再起動してしまうと無効になってしまいます。再起動後も有効にするには、起動時のパラメータを下図のように設定しておきます。
f:id:koogucc11:20150419230727p:plain

 Dynamics でも 4136 は設定しているようです。このパラメータの副作用ってあるんでしょうか?blogs.msdn.com
 
 トレースフラグの一覧はここに記述があります。このあたりはもっと勉強と経験を積まないといけないですね。

※セキュリティ面、弱いです。

Securing SQL Server, Third Edition: Protecting Your Database from Attackers

Securing SQL Server, Third Edition: Protecting Your Database from Attackers