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

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

DBCC TRACEON (4136, -1);

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

DBCC TRACESTATUS(4136);

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

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

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

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