SQL Server/実行プラン

• SQL Server | 実行プランの確認方法

• 実行プランを読む – 基本編 (その 1)
• 実行プランを読む – 基本編 (その 2)
• 実行プランを読む – 基本編 (その 3)

• 実行プランのキャッシュと再利用

• 「パラメータースニッフィング」によって、あるタイミングから処理が遅くなった（パフォーマンストラブル）

• ストアド プロシージャの再コンパイル

実行プランの再利用 †

• クエリの実行プランを強制する
• XML 形式での実行プランの保存
• USE PLAN クエリ ヒントの使用

• SQL Server Management Studioで、クエリを選択して「CTRL+L」で実行プランをビジュアル表示
• この実行プラン上で、右クリック⇒「実行プランのXMLの表示」
• このXMLは、XMLで書かれた実行プランのDump
• クエリ内に '（アポストロフィー）がある場合
  • エディタで '（アポストロフィー）を &apos に文字列置換
• クエリの後に「option (use plan N'【実行プランのXML】');」を追記して実行

• 実行プランを再利用することで、環境が変わろうがなんだろうが、指定したプラン通りに実行される
• ただし、他のクエリヒント（HASH JOINやLOOP JOIN）などと同様に、オプティマイザが働かなくなる点は注意

実行プランのRecompileが発生するトリガ †

• スキーマの変更
• 統計の変更
• コンパイルの遅延
• Set オプションの変更
• Temp テーブルの変更
• リモート行セットの変更
• 参照権限の変更
• クエリ通知環境の変更
• パーティション ビューの変更
• カーソル オプションの変更
• オプション (再コンパイル) の要求

実行プラン再作成イベントの確認 †

• ウィザードを使用した拡張イベント セッションの作成 (オブジェクト エクスプローラー)
• SQL:StmtRecompile イベント クラス

実行プランの固定化 †

• 実行プランはプランガイドを使用して固定することが可能
• プランガイドとは、あるクエリが発行された場合にはこのヒントやプランを使用するといった方法で、固定の実行プランを使用して特定のクエリを実行する方法

• プランガイド
• パラメーター化クエリのプランガイドの作成
• SQL Server Profiler を使用したプランガイドの作成とテスト
• プランガイドへの固定クエリプランの適用

注意点 †

• 実行プランを固定化した場合、下記の注意事項、および、デメリットが生じる可能性がある
  • クエリにスペースが一つ入っただけでも、異なるクエリとみなされてプランガイドが機能しなくなる恐れがある
  • 毎回、記述が変化するようなクエリの場合、プランガイドの固定化は事実上不可能
  • 実行プランを固定する場合、例えば レコード件数の大幅な増加、減少が発生することで、現状では最適な実行プランであったとしても、データの大幅な増減後では最適ではない実行プランとなる可能性がある

SQL実行時間の計測 †

• これでスッキリ！！　SQLServerダイエット！
• sys.dm_exec_query_stats (Transact-SQL)

• 直近で実行したSQLの実行時間

  SELECT st.text
      ,last_execution_time
      ,(last_elapsed_time+0.0)/1000 AS [last_elapsed_time(ms)]
      ,(min_elapsed_time+0.0)/1000 AS [min_elapsed_time(ms)]
      ,(max_elapsed_time+0.0)/1000 AS [max_elapsed_time(ms)]
      ,(total_elapsed_time+0.0)/execution_count/1000 AS [avg_elapsed_time(ms)]
      ,execution_count
  FROM  sys.dm_exec_query_stats qs WITH(NOLOCK)
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st 
  ORDER BY last_execution_time desc

index使用状況 †

• SQLServer: 指定したテーブルのインデックス使用状況を取得する