[펌] 현재 sql server 에서 가장 많은 부하를 일으키는 spid 와 쿼리는?

-----------------------------------------------------------------------------------------------

                                                                   출처: http://www.sqler.com/127797

 

SQL Server에서 가장 많은 부하를 일으키는 쿼리를 찾는 방법에 대한 글을 찾아보다가 아주 유용한 글이

있어 원본 출처를 명시하고 제 블로그로 옮겨 왔습니다. '-'a

-----------------------------------------------------------------------------------------------

 

안녕하세요 김민석 입니다.

SQL Server Profiler 나 XEvents 는 쿼리가 종료 해야지만 해당 비용을 보고해 줍니다.

이에 DBA는 현재 끝나지 않았지만 누군가 돌렸을 무거운 쿼리를 찾아야 하는 경우가 종종 있습니다.

이때는 Sysprocesses 를 이용하면 좀 더 쉽게 알아 볼 수 있습니다.

 

먼저 SQL Server 가 CPU 를 소모하는지, 다른 프로세스가 CPU 를 소모하는지

작업관리자 프로세스에서 확인 합니다.

그런 후, SQL Server 가 모든 CPU 리소스를 사용한다는 것이 확인되면,

아래쿼리를 이용해 어떤 SPID 에서 어떤 쿼리로 인해 CPU 가 높아지는지 확인 할 수 있습니다.
 

 

USE MASTER
GO

IF OBJECT_ID('AP_FIND_BIG_LOAD_SPID') IS NOT NULL
DROP PROC AP_FIND_BIG_LOAD_SPID
GO

CREATE PROC AP_FIND_BIG_LOAD_SPID
@LOOPCOUNT INT = 3
, @CPU_LIMIT INT = 10
AS

-- script by minsouk kim

-- 2009/12/06
-- 부하쿼리가 지나가더라도 잡아 낼 수 있도록 변경 되었습니다.
-- 2005 에서도 사용 가능 합니다.
-- minsouk@hotmail.com
--DECLARE @LOOPCOUNT INT = 3

IF OBJECT_ID('TEMPDB..#TMP_SYSPROCESSES') IS NOT NULL
    DROP TABLE #TMP_SYSPROCESSES

SELECT *
  INTO #TMP_SYSPROCESSES
  FROM (SELECT TOP 0 SPID, DBID, CAST(CPU AS BIGINT) CPU,
               CAST(PHYSICAL_IO AS BIGINT) PHYSICAL_IO, GETDATE() AS CURTIME,
               SQL_HANDLE
          FROM MASTER.DBO.SYSPROCESSES) A

DECLARE @CURCOUNT INT, @CURTIME DATETIME
SET @CURCOUNT = 0
 
WHILE (1=1) BEGIN
    SET @CURTIME = GETDATE()

    INSERT INTO #TMP_SYSPROCESSES
    SELECT SPID, DBID, CASE WHEN LASTWAITTYPE ='CXPACKET' THEN @CURCOUNT * 1000000 + CPU ELSE CPU END,
           PHYSICAL_IO, @CURTIME AS CURTIME,
           SQL_HANDLE
      FROM MASTER.DBO.SYSPROCESSES
     WHERE ECID = 0
    

    SET @CURCOUNT = @CURCOUNT + 1
    IF @CURCOUNT > @LOOPCOUNT
    BREAK
    WAITFOR DELAY '00:00:01'
END

;WITH PROCESSES_TIMES
AS (
    SELECT *, DENSE_RANK() OVER(ORDER BY CURTIME) TIMES
      FROM #TMP_SYSPROCESSES
     WHERE SPID <> @@SPID
   ),
PROCESS_DIFF
AS (
    SELECT C.*, N.CPU AS NEXT_CPU, N.PHYSICAL_IO NEXT_PHYSICAL_IO, N.SQL_HANDLE N_SQL_HANDLE
      FROM PROCESSES_TIMES C
      LEFT JOIN PROCESSES_TIMES N
        on C.SPID = N.SPID
       AND C.TIMES + 1  = N.TIMES
       AND C.SQL_HANDLE = N.SQL_HANDLE
   )
--SELECT * FROM   PROCESS_DIFF ORDER BY TIMES, SPID
,
PROCESS_DIFF_SUM
AS (
    SELECT SPID, SQL_HANDLE, MAX(DBID) DBID,
           SUM(ISNULL(NEXT_CPU,0) - CASE WHEN NEXT_CPU IS NULL OR NEXT_CPU = 0 THEN 0 ELSE CPU END) AS DIFF_CPU
         , SUM(ISNULL(NEXT_PHYSICAL_IO,0) - CASE WHEN NEXT_PHYSICAL_IO IS NULL OR NEXT_PHYSICAL_IO = 0 THEN 0 ELSE PHYSICAL_IO END) AS DIFF_PHYSICAL_IO 
      FROM PROCESS_DIFF
     GROUP BY SPID, SQL_HANDLE
   )
--SELECT * FROM PROCESS_DIFF_SUM ORDER BY SPID
SELECT TOP 20 SPID, S.DBID, DIFF_CPU, DIFF_PHYSICAL_IO, SQL_HANDLE,ST.TEXT
  FROM PROCESS_DIFF_SUM S
 OUTER APPLY SYS.DM_EXEC_SQL_TEXT(S.SQL_HANDLE) ST
 WHERE DIFF_CPU > @CPU_LIMIT
ORDER BY DIFF_CPU DESC

GO

EXEC DBO.AP_FIND_BIG_LOAD_SPID 3,0
GO

 

SQL Query 부하 체크.txt

=================================================================================================
  http://cafe.naver.com/sqlmvp
 sqlserver mvp 2006 ~ 2009
 msn minsouk@hotmail.com
 Tel 010-8585-0915
=================================================================================================

 

SQL Query 부하 체크.txt

0.0MB