ms-sql 부하 확인 쿼리 > 질문답변

• 목록

SELECT TOP 20

 [Average CPU used] = total_worker_time / qs.execution_count

,[Total CPU used] = total_worker_time

,[Execution count] = qs.execution_count

,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,

        (CASE WHEN qs.statement_end_offset = -1

            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

          ELSE qs.statement_end_offset END -

qs.statement_start_offset)/2)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY [Total CPU used] DESC, [Average CPU used] DESC;







SELECT TOP 20

 [Average CPU used] = total_worker_time / qs.execution_count

,[Total CPU used] = total_worker_time

,[Last CPU used] = last_worker_time

,[MAX CPU used] = max_worker_time

,[Execution count] = qs.execution_count

,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,

        (CASE WHEN qs.statement_end_offset = -1

            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

,qs.creation_time

,qs.last_execution_time

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY [Average CPU used] DESC;



출처: https://debasedev.tistory.com/46 [맨땅에 헤딩하기]



http://blog.daum.net/_blog/BlogTypeView.do?blogid=04qAU&articleno=15309484&categoryId=413121®dt=20100628140007
안녕하세요 김민석 입니다.

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

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

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

 

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

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

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

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

SELECT
  sqltext.TEXT,
  req.session_id,
  req.status,
  req.command,
  req.cpu_time,
  req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext


출처: https://gunnm.tistory.com/164 [독까의 이야기]





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




mssql 접속자 확인
SELECT S.SPID, S.LOGINAME, S.LOGIN_TIME, S.LAST_BATCH, C.CLIENT_NET_ADDRESS
FROM sys.sysprocesses S, sys.dm_exec_connections C
 WHERE S.spid = C.SESSION_ID
 order by client_net_address;