SQL Server Profilerの使用は非推奨？

SQL Serverでupdlock,rowlockヒント付きの同じselect文同士で、デッドロックが発生した。
デッドロックエラー時の実行プランをみたところ、4列からなる主キーのうち、シークキーとしているのが1列のみ。
理由は、select文のwhere句に指定があるのは、主キーの第1,3,4番目の列で、主キーの第2番目の列を指定していないため。
別に用意されていた(where句の検索条件で十分絞り込まれる)2次インデックスは使われていなかった。

対応として、(たとえSQL Serverが計算したコストが高くなっても)2次インデックス使うようにするヒントを追加する対応をする。

で、効果確認で、実行後にプランハンドルを検索し実行プランを抽出しようとしたが、
検索に手間取りプランハンドル特定時には実行プランが流れて(消えて)しまっていた。

なので、SQL Profilerを仕込んで、SQLを実行することにしようとしたが、netを見ると、profilerの使用は非推奨となっており、
拡張イベントを使う方法が推奨とのこと。

というわけで、SQL Profilerと拡張イベントでの実行プランの採取方法の備忘録。


まずは、profilerの方法
Microsoft SQL Server Tools→SQL Server Profiler を起動し、新しいトレースを押し、



サーバへの接続ダイアログで、該当サーバに接続し、

トレースのプロパティダイアログで、[全般]タブでは、使用するテンプレートでStandard(default)のまま、
[イベントの選択]タブで、すべてのイベントを表示するにチェックボックスをONにした後、



Performance→Showplan XML Statistics Profileの行にチェックを入れて実行ボタンを押下。


調査するSQL文の実行が終わった後に、赤い四角アイコン(選択したトレースの停止)を押して、トレース採取完了。

EventClassがShowplan XML Statistics Profileの行を選択すると、SQLPlanが図示される。




次に拡張イベントの方法
SQL Server Management Studioでサーバへの接続ダイアログで、該当サーバに接続し、
オブジェクトエクスプローラーウィンドウでSQL Server→管理→拡張イベント→セッションを右クリックし新しいセッションウィザードを選択。


ウィザード(ステップ式ダイアログ入力)で
[セッションのプロパティの設定]ステップで、セッション名を指定し、

[テンプレートの選択]ステップで、テンプレートにStandardを選択し、

[キャプチャするイベントの選択]ステップで、query_post_execution_showplanイベントを追加し、

[グローバルフィールドのキャプチャ]ステップと、
[セッションイベントフィルターの設定]ステップはデフォルトのまま
[セッションデータストレージの指定]ステップで、SQLサーバー上のファイル名を指定して(SQL Serverへのログインユーザの次第では書き込み権限がないかも。)完了ボタンを押下。

成功のダイアログを閉じる。

オブジェクトエクスプローラーウィンドウでSQL Server→管理→拡張イベント→セッションを展開すると、ウィザードステップ1でしていしたセッション名が赤四角(停止中)で表示される。

右クリック→[セッションの開始]すると、赤い四角から、緑の右向き三角となり採取中となる。






最後に、今回の対応での実行プランの見方をpubsデータベースsalesテーブルを使って解説。
salesテーブルは、主キーが、stor_id,ord_num,title_idとなっているクラスター化インデックスキーとなっている。
salesテーブルにstor_id,title_idを持つインデックスixを作ってみる。


SQL Server Management Studioで[実際の実行プランを含める(Ctrl + M)]をした後、
select * from sales where stor_id='6380' and title_id='BU1032'
を実行し、実行プランを見ると、SELECTはクラスター化インデックスシークを行っていることがわかる。

クラスター化インデックスシークをポイントすると、シークキーにstor_idのみ使われていることがわかり、

SELECTをポイントすると、サブツリーの推定コストが、0.0032842とわかる。




次に、select * from sales with(ixndex(ix)) where stor_id='6380' and title_id='BU1032'
を実行し、実行プランを見ると、SELECTは[sales].[ix]のインデックスシークと、[sales].UPKCL_salesのキー参照をNested Loops(入れ子ループ)でインナージョインしていることがわかる。

インデックスシークでは、シークキーに、stor_idと、title_idが使われており、

SELECTでは、サブツリーの推定コストが、0.0065704とわかる。


つまり、with(index(ix))のヒントにより、SQL Serverは、推定コストが0.0032842ではなく、それより大きい0.0065704の実行プランを採用していることがわかる。

これで、デッドロックの解消ができればいいんだけれど。