【SQL Server】クエリの実行プランとパフォーマンス情報の取得方法Part1

クエリのパフォーマンス状態の分析において、必ずといってよいほど必要となる実行プランやCPU時間や実行時間、実行回数などの取得方法について、いくつか方法がありますのでまとめます。

大きく分けて以下のような方法で取得できます。

1.動的管理ビューで取得する

これは、実際に実行済みでかつキャッシュ上に残存している場合に取得できます。本番環境などで余計な負荷をかけずに取得できる方法かと思います。
ただし、キャッシュされていない実行プランはもちろん取得できないことになります。

クエリでDMV組み合わせて取得する

例として、CPU負荷が高いTOP100のSQLで、CPUtimeや実行時間、実行回数も同時に取得するクエリです。

USE master
GO
SET NOCOUNT ON
GO

SELECT TOP 100
        rank() over(order by total_worker_time desc,sql_handle,statement_start_offset) AS [row_no]
,       db_name(st.dbid) AS [database_name]
,       creation_time
,       last_execution_time
,       (total_worker_time+0.0)/1000 AS [total_worker_time(ms)]
,       (total_worker_time+0.0)/(execution_count*1000) AS [AvgCPUTime(ms)]
,       (total_elapsed_time+0.0)/1000 AS [total_elapsed_time(ms)]
,       (total_elapsed_time+0.0)/(execution_count*1000) AS [AvgElapsedTime(ms)]
,       total_logical_reads AS [LogicalReads(page)]
,       total_logical_writes AS [logicalWrites(page)]
,       total_logical_reads+total_logical_writes AS [AggIO(page)]
,       (total_logical_reads+total_logical_writes)/(execution_count + 0.0) AS [AvgIO(page)]
,       execution_count
,       total_rows
,       st.text AS [batch_query_text]
,   CASE 
            WHEN sql_handle IS NULL
            THEN ' '
            ELSE ( SUBSTRING(st.text,(qs.statement_start_offset+2)/2,(CASE 
                                                                          WHEN qs.statement_end_offset = -1        
                                                                          THEN LEN(CONVERT(NVARCHAR(MAX),st.text))*2      
                                                                          ELSE qs.statement_end_offset    
                                                                      END 
            - qs.statement_start_offset) /2  ) )
    END AS [statement_query_text] 
,       plan_generation_num
,       qp.query_plan
FROM sys.dm_exec_query_stats   AS [qs]
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS [st]
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS [qp]
WHERE total_worker_time > 0 
ORDER BY total_worker_time  DESC
OPTION (RECOMPILE)

また、実行プランなしでCPU時間や実行時間等が知りたい場合、上記クエリのほかにレポートに出力も可能ですので、下記記事をご確認ください。
memorandom-nishi.hatenablog.jp

利用状況モニターから取得する

実際に現在実行されているものや比較的負荷が高いものが対象にあります。DMVで取得するものを利用状況モニターから取得しているだけですので、こちらも過去記事をご確認いただければと思います。

memorandom-nishi.hatenablog.jp

2.実行時にSET～を使用する

この場合、これから実行しようとするクエリの推定プラン（実行自体はしない）や実際の実行したクエリが取得できます。

推定の実行プランを取得する

SET SHOWPLAN_xx　で取得する場合、クエリ自体は実行せずに推測の実行プランを確認することができます。
グラフィカルな実行プランで確認したい場合は次のように確認します。

SET SHOWPLAN_XML ON
GO
SELECT * 
	FROM		dbo.EMPLOYEES		AS [EMP]
	INNER JOIN 	dbo.DEPARTMENTS		AS [DEPA]
	ON		[EMP].MANAGER_ID = [DEPA].MANAGER_ID	
SET SHOWPLAN_XML OFF
GO

また、クエリ実行時にSSMSの「推定実行プランの表示」をクリックしても同様の結果が取得できます。

グラフィカルな実行プランの場合、各アイコンやノードにマウスカーソルを合わせると詳細情報として、CPU・IOのコストや予測行数などを確認できます。
SSMSがインストールされている環境であれば視覚的に確認できてよいかと思います。

テキスト形式で実行プランを取得したい場合、実行プランのみ取得するものと、詳細情報も併せて取得するものの2パターンあります。

実行プランのみ取得する場合は次のように実行します。

SET SHOWPLAN_TEXT ON
GO

--実行プランを取得したいクエリ
SELECT * 
	FROM		dbo.EMPLOYEES	AS [EMP]
	INNER JOIN 	dbo.DEPARTMENTS	AS [DEPA]
	ON　　　　　　　[EMP].MANAGER_ID = [DEPA].MANAGER_ID	
GO

SET SHOWPLAN_TEXT OFF
GO

実行プランと詳細情報の場合は次のように実行します。

SET SHOWPLAN_ALL ON
GO

--実行プランを取得したいクエリ
SELECT * 
	FROM		dbo.EMPLOYEES	AS [EMP]
	INNER JOIN 	dbo.DEPARTMENTS	AS [DEPA]
	ON　　　　　　　[EMP].MANAGER_ID = [DEPA].MANAGER_ID
GO

SET SHOWPLAN_ALL OFF
GO

情報としては上記グラフィカルな実行プランではマウスカーソルにて確認できる情報がまとめて確認できるような形となります。テキスト形式で保存すれば、環境に依存せず他人と情報を共有できて便利かと思います。

実際の実行プランを取得する

SET STATISTICS xx　で取得する場合、クエリ自体は実行して実際の実行プランを確認することができます。
クエリを実際に実行してしまうため、本番環境での使用は十分注意が必要ですが、その分予測行数と実際の行数など実行時の情報が確認できるため非常に有効な情報となります。

グラフィカルな実際の実行プランで確認したい場合は次のように確認します。

SET STATISTICS XML ON

--実行プランを取得したいクエリ
SELECT * 
	FROM		dbo.EMPLOYEES	AS [EMP]
	INNER JOIN 	dbo.DEPARTMENTS	AS [DEPA]
	ON　　　　　　　[EMP].MANAGER_ID = [DEPA].MANAGER_ID
GO

SET STATISTICS XML OFF

推定の実行プランと比較すると、各アイコンやノードにマウスカーソルを合わせた際に実際の行数や実行回数などが追加で取得できていることが確認できます。

テキスト形式で実際の実行プランで確認したい場合は次のように確認します。

SET STATISTICS PROFILE ON

--実行プランを取得したいクエリ
SELECT * 
	FROM		dbo.EMPLOYEES	AS [EMP]
	INNER JOIN 	dbo.DEPARTMENTS	AS [DEPA]
	ON　　　　　　　[EMP].MANAGER_ID = [DEPA].MANAGER_ID
GO

SET STATISTICS PROFILE OFF

こちらも推定の実行プランに比べ、実際の行数や実行回数が追加されていることがわかるかと思います。

また、実行プランの取得ではありませんが、実行して分析する際には次のようにクエリがどのページを読み取ったかや構文解析時間、クエリ実行時間も合わせて取得しておくと解析時に便利かと思います。

SET STATISTICS IO ON
SET STATISTICS TIME ON

	--実行プランを取得したいクエリ
	SELECT * 
		FROM		dbo.EMPLOYEES		AS [EMP]
		INNER JOIN 	dbo.DEPARTMENTS		AS [DEPA]
		ON		[EMP].MANAGER_ID = [DEPA].MANAGER_ID	
	GO

SET STATISTICS TIME OFF
SET STATISTICS IO OFF

上記のほかにトレースして実行計画やパフォーマンス情報を確認する方法はありますが、それは次回記載します。