SQL ServerでOracleのStatspackみたいな事をやる

IT/テクニカル系

要は、揮発性の動的管理ビュー(関数)(DMV)

    • SYS.DM_EXEC_QUERY_STATS
    • SYS.DM_EXEC_SQL_TEXT
    • SYS.DM_EXEC_QUERY_PLAN

のスナップショットをため込んでいこうというお話。

SNAPSHOTテーブル定義

CREATE DATABASE
	PERFSTAT
;

SELECT
	GETDATE() SNAP_DATE,
	T1.*,
	T2.*,
	T3.QUERY_PLAN
INTO
	PERFSTAT.DBO.SNAP_QUERY_PLAN
FROM
	SYS.DM_EXEC_QUERY_STATS T1
	CROSS APPLY
	SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE) T2
	CROSS APPLY
	SYS.DM_EXEC_QUERY_PLAN(PLAN_HANDLE) T3
WHERE
	1 = 2
;

ALTER TABLE
	PERFSTAT.DBO.SNAP_QUERY_PLAN
ADD CONSTRAINT
	PK_SNAP_QUERY_PLAN
PRIMARY KEY
(
	PLAN_HANDLE,
	STATEMENT_START_OFFSET
)
;

WKテーブル定義

※DMVの仕様がMSDN見てもよくわからないところがあるのでとりあえず...

SELECT
	T1.*,
	T2.*,
	T3.QUERY_PLAN
INTO
	PERFSTAT.DBO.SNAP_QUERY_PLAN_WK
FROM
	SYS.DM_EXEC_QUERY_STATS T1
	CROSS APPLY
	SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE) T2
	CROSS APPLY
	SYS.DM_EXEC_QUERY_PLAN(PLAN_HANDLE) T3
WHERE
	1 = 2
;

ALTER TABLE
	PERFSTAT.DBO.SNAP_QUERY_PLAN_WK
ADD CONSTRAINT
	PK_SNAP_QUERY_PLAN_WK
PRIMARY KEY
(
	PLAN_HANDLE,
	STATEMENT_START_OFFSET
)
;

SNAPSHOTを追加するSQL

MERGE INTO
	PERFSTAT.DBO.SNAP_QUERY_PLAN T1
USING
	(
	SELECT
		*
	FROM
		PERFSTAT.DBO.SNAP_QUERY_PLAN_WK T1
	WHERE
		-- 新しくキャッシュされたステートメントか、最後にSNAPSHOTしてから実行されたステートメント
		NOT EXISTS
		(
		SELECT
			NULL
		FROM
			PERFSTAT.DBO.SNAP_QUERY_PLAN T2
		WHERE
			T1.PLAN_HANDLE = T2.PLAN_HANDLE
			AND
			T1.STATEMENT_START_OFFSET = T2.STATEMENT_START_OFFSET
			AND
			T1.LAST_EXECUTION_TIME = T2.LAST_EXECUTION_TIME
		)
	) T2
ON
	T1.PLAN_HANDLE = T2.PLAN_HANDLE
	AND
	T1.STATEMENT_START_OFFSET = T2.STATEMENT_START_OFFSET
WHEN MATCHED THEN
	UPDATE SET
		T1.[SNAP_DATE] = GETDATE(),
		T1.[SQL_HANDLE] = T2.[SQL_HANDLE],
		T1.[STATEMENT_START_OFFSET] = T2.[STATEMENT_START_OFFSET],
		T1.[STATEMENT_END_OFFSET] = T2.[STATEMENT_END_OFFSET],
		T1.[PLAN_GENERATION_NUM] = T2.[PLAN_GENERATION_NUM],
		T1.[PLAN_HANDLE] = T2.[PLAN_HANDLE],
		T1.[CREATION_TIME] = T2.[CREATION_TIME],
		T1.[LAST_EXECUTION_TIME] = T2.[LAST_EXECUTION_TIME],
		T1.[EXECUTION_COUNT] = T2.[EXECUTION_COUNT],
		T1.[TOTAL_WORKER_TIME] = T2.[TOTAL_WORKER_TIME],
		T1.[LAST_WORKER_TIME] = T2.[LAST_WORKER_TIME],
		T1.[MIN_WORKER_TIME] = T2.[MIN_WORKER_TIME],
		T1.[MAX_WORKER_TIME] = T2.[MAX_WORKER_TIME],
		T1.[TOTAL_PHYSICAL_READS] = T2.[TOTAL_PHYSICAL_READS],
		T1.[LAST_PHYSICAL_READS] = T2.[LAST_PHYSICAL_READS],
		T1.[MIN_PHYSICAL_READS] = T2.[MIN_PHYSICAL_READS],
		T1.[MAX_PHYSICAL_READS] = T2.[MAX_PHYSICAL_READS],
		T1.[TOTAL_LOGICAL_WRITES] = T2.[TOTAL_LOGICAL_WRITES],
		T1.[LAST_LOGICAL_WRITES] = T2.[LAST_LOGICAL_WRITES],
		T1.[MIN_LOGICAL_WRITES] = T2.[MIN_LOGICAL_WRITES],
		T1.[MAX_LOGICAL_WRITES] = T2.[MAX_LOGICAL_WRITES],
		T1.[TOTAL_LOGICAL_READS] = T2.[TOTAL_LOGICAL_READS],
		T1.[LAST_LOGICAL_READS] = T2.[LAST_LOGICAL_READS],
		T1.[MIN_LOGICAL_READS] = T2.[MIN_LOGICAL_READS],
		T1.[MAX_LOGICAL_READS] = T2.[MAX_LOGICAL_READS],
		T1.[TOTAL_CLR_TIME] = T2.[TOTAL_CLR_TIME],
		T1.[LAST_CLR_TIME] = T2.[LAST_CLR_TIME],
		T1.[MIN_CLR_TIME] = T2.[MIN_CLR_TIME],
		T1.[MAX_CLR_TIME] = T2.[MAX_CLR_TIME],
		T1.[TOTAL_ELAPSED_TIME] = T2.[TOTAL_ELAPSED_TIME],
		T1.[LAST_ELAPSED_TIME] = T2.[LAST_ELAPSED_TIME],
		T1.[MIN_ELAPSED_TIME] = T2.[MIN_ELAPSED_TIME],
		T1.[MAX_ELAPSED_TIME] = T2.[MAX_ELAPSED_TIME],
		T1.[QUERY_HASH] = T2.[QUERY_HASH],
		T1.[QUERY_PLAN_HASH] = T2.[QUERY_PLAN_HASH],
		T1.[DBID] = T2.[DBID],
		T1.[OBJECTID] = T2.[OBJECTID],
		T1.[NUMBER] = T2.[NUMBER],
		T1.[ENCRYPTED] = T2.[ENCRYPTED],
		T1.[TEXT] = T2.[TEXT],
		T1.[QUERY_PLAN] = T2.[QUERY_PLAN]
WHEN NOT MATCHED THEN
	INSERT VALUES (
		GETDATE(),
		T2.[SQL_HANDLE],
		T2.[STATEMENT_START_OFFSET],
		T2.[STATEMENT_END_OFFSET],
		T2.[PLAN_GENERATION_NUM],
		T2.[PLAN_HANDLE],
		T2.[CREATION_TIME],
		T2.[LAST_EXECUTION_TIME],
		T2.[EXECUTION_COUNT],
		T2.[TOTAL_WORKER_TIME],
		T2.[LAST_WORKER_TIME],
		T2.[MIN_WORKER_TIME],
		T2.[MAX_WORKER_TIME],
		T2.[TOTAL_PHYSICAL_READS],
		T2.[LAST_PHYSICAL_READS],
		T2.[MIN_PHYSICAL_READS],
		T2.[MAX_PHYSICAL_READS],
		T2.[TOTAL_LOGICAL_WRITES],
		T2.[LAST_LOGICAL_WRITES],
		T2.[MIN_LOGICAL_WRITES],
		T2.[MAX_LOGICAL_WRITES],
		T2.[TOTAL_LOGICAL_READS],
		T2.[LAST_LOGICAL_READS],
		T2.[MIN_LOGICAL_READS],
		T2.[MAX_LOGICAL_READS],
		T2.[TOTAL_CLR_TIME],
		T2.[LAST_CLR_TIME],
		T2.[MIN_CLR_TIME],
		T2.[MAX_CLR_TIME],
		T2.[TOTAL_ELAPSED_TIME],
		T2.[LAST_ELAPSED_TIME],
		T2.[MIN_ELAPSED_TIME],
		T2.[MAX_ELAPSED_TIME],
		T2.[QUERY_HASH],
		T2.[QUERY_PLAN_HASH],
		T2.[DBID],
		T2.[OBJECTID],
		T2.[NUMBER],
		T2.[ENCRYPTED],
		T2.[TEXT],
		T2.[QUERY_PLAN]
	)
;

確認用。

SELECT
	SNAP_DATE,
	PLAN_HANDLE,
	LAST_EXECUTION_TIME,
	EXECUTION_COUNT,
	TEXT
FROM
	PERFSTAT.DBO.SNAP_QUERY_PLAN
ORDER BY
	SNAP_DATE DESC,
	PLAN_HANDLE,
	STATEMENT_START_OFFSET
;


XML型のQUERY_PLAN列って、Management Studioから照会すると実行プランをすぐに可視化してくれて便利すぎる。

Work! Enjoy it!