민석이의 waitstat 모니터링

-- +++++++++++++++++++++++++++++++++++++++++++++++++++++
-- 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