小物SEのメモ帳

IT関係の試したことや調べたことの備忘録

【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]
;
利用状況モニターを使用してインスタンス全体のクエリから対象のクエリを確認する場合

利用状況モニターを開いて、「アクティブなコストの高いクエリ」を押下して、確認するクエリを選択し右クリック、「ライブ実行プランの表示」を選択します。
f:id:utiowa:20170604201923p:plain

セッションから対象のクエリのみを確認する場合

SSMSの上部メニューから「ライブクエリ統計を含む」アイコンをクリックします。
f:id:utiowa:20170604201936p:plain


確認結果は以下のようになります。
f:id:utiowa:20170604202018p:plain

処理中である箇所や、各処理に要した時間や処理行数がリアルタイムで確認することが可能になりますので、処理の完了を待たずにボトルネックが把握できるため長時間が予想されるクエリのチューニング時に活用してみては。



参考情報:
ライブ クエリ統計

広告を非表示にする