-- +++++++++++++++++++++++++++++++++++++++++++++++++++++
-- WAIT 조사용 프로시저 생성
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++
USE MASTER
GO
IF OBJECT_ID ('AP_GET_WAITSTATS_1') IS NOT NULL
DROP PROC AP_GET_WAITSTATS_1
GO
CREATE PROC AP_GET_WAITSTATS_1
@TYPE INT = 6
, @GROUP_MIN INT = 3
, @LOGINAME NVARCHAR(256) = NULL
, @NAME NVARCHAR(256) = NULL
, @HOSTNAME NVARCHAR(256) = NULL
, @PROGRAM_NAME NVARCHAR(256) = NULL
, @SQL_HANDLE BINARY(20) = NULL
, @START_TIME SMALLDATETIME = NULL
, @END_TIME SMALLDATETIME = NULL
, @SPID INT = NULL
AS
IF @TYPE <> 6 BEGIN
; WITH DATA AS
(SELECT CONVERT(SMALLDATETIME,FLOOR((CAST(D AS FLOAT) * 24 * 60) / @GROUP_MIN ) * @GROUP_MIN / 24 / 60, 108) TIMEX, LOGINAME, NAME, HOSTNAME, PROGRAM_NAME, SQL_HANDLE, LASTWAITTYPE
FROM ##TBLWAITS
WHERE SQL_HANDLE = CASE WHEN @SQL_HANDLE IS NULL THEN SQL_HANDLE ELSE @SQL_HANDLE END
AND LOGINAME = CASE WHEN @LOGINAME IS NULL THEN LOGINAME ELSE @LOGINAME END
AND NAME = CASE WHEN @NAME IS NULL THEN NAME ELSE @NAME END
AND HOSTNAME = CASE WHEN @HOSTNAME IS NULL THEN HOSTNAME ELSE @HOSTNAME END
AND PROGRAM_NAME = CASE WHEN @PROGRAM_NAME IS NULL THEN PROGRAM_NAME ELSE @PROGRAM_NAME END
AND SPID = CASE WHEN @SPID IS NULL THEN SPID ELSE @SPID END
AND D BETWEEN CASE WHEN @START_TIME IS NULL THEN '19750523' ELSE @START_TIME END AND CASE WHEN @END_TIME IS NULL THEN '20790606' ELSE @END_TIME END )
, PER_WAITTYPE AS
(SELECT TIMEX, LASTWAITTYPE VAL, COUNT(*) CNT FROM DATA GROUP BY TIMEX, LASTWAITTYPE WITH ROLLUP)
, PER_LOGINAME AS
(SELECT TIMEX, LOGINAME, COUNT(*) CNT FROM DATA GROUP BY TIMEX, LOGINAME WITH ROLLUP)
, PER_DATABASE AS
(SELECT TIMEX, NAME, COUNT(*) CNT FROM DATA GROUP BY TIMEX, NAME WITH ROLLUP)
, PER_HOSTNAME AS
(SELECT TIMEX, HOSTNAME, COUNT(*) CNT FROM DATA GROUP BY TIMEX, HOSTNAME WITH ROLLUP)
, PER_PROGRAM_NAME AS
(SELECT TIMEX, PROGRAM_NAME, COUNT(*) CNT FROM DATA GROUP BY TIMEX, PROGRAM_NAME WITH ROLLUP)
, PER_SQL_HANDLE_RAW AS
(SELECT TIMEX, SQL_HANDLE, COUNT(*) CNT FROM DATA GROUP BY TIMEX, SQL_HANDLE WITH ROLLUP)
, PER_SQL_HANDLE AS
(SELECT TIMEX, CAST(SQL_HANDLE AS SQL_VARIANT) SQL_HANDLE, CNT, QT.TEXT FROM PER_SQL_HANDLE_RAW A OUTER APPLY SYS.DM_EXEC_SQL_TEXT(A.SQL_HANDLE) QT
)
SELECT *
FROM (SELECT * FROM PER_WAITTYPE WHERE @TYPE = 1
UNION ALL SELECT * FROM PER_LOGINAME WHERE @TYPE = 2
UNION ALL SELECT * FROM PER_DATABASE WHERE @TYPE = 3
UNION ALL SELECT * FROM PER_HOSTNAME WHERE @TYPE = 4
UNION ALL SELECT * FROM PER_PROGRAM_NAME WHERE @TYPE = 5) A
ORDER BY CASE WHEN TIMEX IS NULL THEN '20790606' ELSE TIMEX END , CNT
OPTION (RECOMPILE)
END
IF @TYPE = 6 BEGIN
; WITH DATA AS
(SELECT CONVERT(SMALLDATETIME,FLOOR((CAST(D AS FLOAT) * 24 * 60) / @GROUP_MIN ) * @GROUP_MIN / 24 / 60, 108) TIMEX, LOGINAME, NAME, HOSTNAME, PROGRAM_NAME, SQL_HANDLE, LASTWAITTYPE
FROM ##TBLWAITS
WHERE SQL_HANDLE = CASE WHEN @SQL_HANDLE IS NULL THEN SQL_HANDLE ELSE @SQL_HANDLE END
AND LOGINAME = CASE WHEN @LOGINAME IS NULL THEN LOGINAME ELSE @LOGINAME END
AND NAME = CASE WHEN @NAME IS NULL THEN NAME ELSE @NAME END
AND HOSTNAME = CASE WHEN @HOSTNAME IS NULL THEN HOSTNAME ELSE @HOSTNAME END
AND PROGRAM_NAME = CASE WHEN @PROGRAM_NAME IS NULL THEN PROGRAM_NAME ELSE @PROGRAM_NAME END
AND SPID = CASE WHEN @SPID IS NULL THEN SPID ELSE @SPID END
AND D BETWEEN CASE WHEN @START_TIME IS NULL THEN '19750523' ELSE @START_TIME END AND CASE WHEN @END_TIME IS NULL THEN '20790606' ELSE @END_TIME END )
, PER_SQL_HANDLE AS
(SELECT TIMEX, SQL_HANDLE, COUNT(*) CNT FROM DATA GROUP BY TIMEX, SQL_HANDLE WITH ROLLUP )
SELECT A.*, SQL_TEXT
FROM PER_SQL_HANDLE A
LEFT JOIN ##TBLSQLTEXT B
ON B.SQL_HANDLE = A.SQL_HANDLE
ORDER BY CASE WHEN TIMEX IS NULL THEN '20790606' ELSE TIMEX END , CNT
END
GO
IF OBJECT_ID('AP_PUT_WAITSTATS_1') IS NOT NULL
DROP PROC AP_PUT_WAITSTATS_1
GO
CREATE PROC AP_PUT_WAITSTATS_1
@MIN INT = 10
AS
SET NOCOUNT ON
-- SCRIPT BY MINSOUK KIM (SQL SERVER MVP SINCE 2006)
-- DEBUG
-- DECLARE @MIN INT
-- SET @MIN = 1
-- END OF DEBUG
DECLARE @END_TIME DATETIME
SELECT @END_TIME = DATEADD(MINUTE, ABS(@MIN), GETDATE())
SELECT @END_TIME
BEGIN TRY
DROP TABLE ##TBLWAITS
END TRY BEGIN CATCH END CATCH
BEGIN TRY
DROP TABLE ##TBLSQLTEXT
END TRY BEGIN CATCH END CATCH
SELECT * INTO ##TBLWAITS
FROM (SELECT TOP 0 CAST(S.LOGINAME AS NVARCHAR(256)) LOGINAME
, CAST(D.NAME AS NVARCHAR(256)) NAME
, CAST(S.HOSTNAME AS NVARCHAR(256)) HOSTNAME
, CAST(S.PROGRAM_NAME AS NVARCHAR(256)) PROGRAM_NAME , S.SQL_HANDLE
, S.SPID, CONVERT(SMALLINT, S.WAITTYPE) WAITTYPE
, S.LASTWAITTYPE, S.ECID, S.WAITTIME , S.BLOCKED, GETDATE() D
FROM MASTER..SYSDATABASES AS D WITH(NOLOCK)
, MASTER..SYSPROCESSES AS S WITH(NOLOCK)
WHERE S.CMD<>'AWAITING COMMAND'
AND S.CMD NOT LIKE '%BACKUP%'
AND S.CMD NOT LIKE '%RESTORE%'
AND S.SPID>50
AND S.SPID<>@@SPID
AND S.DBID = D.DBID
ORDER BY S.SPID
, S.ECID ASC) A
SELECT * INTO ##TBLSQLTEXT
FROM (SELECT TOP 0 S.SQL_HANDLE, S.STMT_START, S.STMT_END, CAST('' AS VARCHAR(8000)) SQL_TEXT
FROM MASTER..SYSDATABASES AS D WITH(NOLOCK)
, MASTER..SYSPROCESSES AS S WITH(NOLOCK)
WHERE S.CMD<>'AWAITING COMMAND'
AND S.CMD NOT LIKE '%BACKUP%'
AND S.CMD NOT LIKE '%RESTORE%'
AND S.SPID>50
AND S.SPID<>@@SPID
AND S.DBID = D.DBID
ORDER BY S.SPID
, S.ECID ASC) A
WHILE (GETDATE() < @END_TIME) BEGIN
INSERT INTO ##TBLWAITS
SELECT S.LOGINAME, D.NAME, S.HOSTNAME, S.PROGRAM_NAME, S.SQL_HANDLE
, S.SPID, CONVERT(SMALLINT, S.WAITTYPE) WAITTYPE
, S.LASTWAITTYPE, S.ECID, S.WAITTIME , S.BLOCKED, GETDATE() D
FROM MASTER..SYSDATABASES AS D WITH(NOLOCK)
, MASTER..SYSPROCESSES AS S WITH(NOLOCK)
WHERE S.CMD<>'AWAITING COMMAND'
--AND S.CMD NOT LIKE '%BACKUP%'
--AND S.CMD NOT LIKE '%RESTORE%'
AND S.SPID>50
AND S.SPID<>@@SPID
AND S.DBID = D.DBID
ORDER BY S.SPID
, S.ECID ASC
INSERT INTO ##TBLSQLTEXT (SQL_HANDLE, STMT_START, STMT_END, SQL_TEXT)
SELECT A.*, C.TEXT
FROM (SELECT S.SQL_HANDLE, S.STMT_START, S.STMT_END
FROM MASTER..SYSDATABASES AS D WITH(NOLOCK)
, MASTER..SYSPROCESSES AS S WITH(NOLOCK)
WHERE S.CMD<>'AWAITING COMMAND'
AND S.CMD NOT LIKE '%BACKUP%'
AND S.CMD NOT LIKE '%RESTORE%'
AND S.SPID>50
AND S.SPID<>@@SPID
AND S.DBID = D.DBID) A
LEFT JOIN ##TBLSQLTEXT B
ON B.SQL_HANDLE = A.SQL_HANDLE
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(A.SQL_HANDLE) C
WHERE B.SQL_HANDLE IS NULL
WAITFOR DELAY '00:00:00.100'
END
GO
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++
-- WAIT 조사용 프로시저 돌리기
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++
EXEC MASTER.DBO.AP_PUT_WAITSTATS_1 10
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++
-- 다른 세션에서 다음을 확인 한다.
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++
-- 구조 확인용
-- SELECT TOP 10 * FROM ##TBLWAITS
-- SELECT TOP 10 * FROM ##TBLSQLTEXT
---- 어떤 WAIT 이 있나?
--EXEC MASTER.DBO.AP_GET_WAITSTATS_1 @TYPE =1
--EXEC MASTER.DBO.AP_GET_WAITSTATS_1 @TYPE =2
--EXEC MASTER.DBO.AP_GET_WAITSTATS_1 @TYPE =3
--EXEC MASTER.DBO.AP_GET_WAITSTATS_1 @TYPE =4
--EXEC MASTER.DBO.AP_GET_WAITSTATS_1 @TYPE =5
--EXEC MASTER.DBO.AP_GET_WAITSTATS_1 @TYPE =6
---- 디테일 정보 확인
--EXEC MASTER.DBO.AP_GET_WAITSTATS_1
-- @TYPE = 1
-- , @GROUP_MIN = 5
-- --, @LOGINAME = 'NB10202-PC\Administrator'
-- --, @NAME = 'TEMPDB'
-- --, @HOSTNAME = 'NB10202-PC'
-- --, @PROGRAM_NAME = 'Microsoft SQL Server Management Studio - 쿼리'
-- --, @SQL_HANDLE = 0x01000100FAE6651BF09DF1050000000000000000
-- --, @START_TIME = '2010-01-06 04:21:00'
-- --, @END_TIME = '2010-01-06 04:24:00'
---- +++++++++++++++++++++++++++++++++++++++++++++++++++++
---- 클리어
---- +++++++++++++++++++++++++++++++++++++++++++++++++++++
--BEGIN TRY
-- DROP TABLE ##TBLWAITS
--END TRY BEGIN CATCH END CATCH
--BEGIN TRY
-- DROP TABLE ##TBLSQLTEXT
--END TRY BEGIN CATCH END CATCH
돌린 결과는 다음과 같다.
프로파일러(링크를 따라가면, 오른쪽 상단에 자세한 DOC 문서거 첨부되어 있습니다. )
http://cafe.naver.com/sqlmvp/68
DMV
http://cafe.naver.com/sqlmvp/688
XEvents (2012 버전부터 쓸만 합니다. 2008R2 버전까지는 RPC 에서의 SQL_TEXT Action 을 캡처하지 못해 쓸만하지 못합니다. )
http://cafe.naver.com/sqlmvp/3775
http://cafe.naver.com/sqlmvp/3398
ERROR 찾기
http://cafe.naver.com/sqlmvp/626
