【SQL Server】処理の進捗がわかるライブクエリ統計
SQLServer2014以降で使用することができるライブクエリ統計の機能がクエリのパフォーマンスチューニングに役立ちそうだったため使い方を記します。
ライブクエリ統計を使うことで実行に長時間かかるクエリが、どの処理で時間を要しているのかわかるため、改善すべき処理が把握でき便利かと思います。
(多少なりとも負荷がかかる設定のため、商用環境以外での使用となるかと思いますが)
事前準備
使用するためには事前に以下の設定が必要です。
セッションのみの監視をする場合
これから監視するクエリのセッションで事前にSET STATISTICS XML ON;もしくはSET STATISTICS PROFILE ON;を実行
インスタンス全体のクエリを監視する場合
拡張イベントでquery_post_execution_showplanを有効にする
SQLServer 2016 SP1以降の場合、軽量版統計インフラストラクチャを有効にすることでも確認可能となります。
トレースフラグ7412を使用
DBCC TRACEON(7412, -1)
インスタンス全体のクエリを監視する場合
拡張イベントでquery_thread_profileを有効にする
実際に試してみる
ためしに時間のかかる適当なクエリを実行してライブクエリ統計を確認してみます。
WITH CTE AS ( SELECT EMP.EMPLOYEE_ID, EMP.FIRST_NAME, EMP.LAST_NAME, EMP.SALARY, JOBS.MIN_SALARY, JOBS.MAX_SALARY, HIST.EMPLOYEE_ID AS [HIST_EMP_ID] FROM dbo.EMPLOYEES AS [EMP] INNER JOIN dbo.JOBS AS [JOBS] ON EMP.JOB_ID = JOBS.JOB_ID LEFT OUTER JOIN JOB_HISTORY AS [HIST] ON EMP.EMPLOYEE_ID = HIST.EMPLOYEE_ID ) SELECT * FROM CTE AS [CTE_A] ,CTE AS [CTE_B] ;
利用状況モニターを使用してインスタンス全体のクエリから対象のクエリを確認する場合
利用状況モニターを開いて、「アクティブなコストの高いクエリ」を押下して、確認するクエリを選択し右クリック、「ライブ実行プランの表示」を選択します。