MSSQL CPU 사용율 높은 쿼리 찾기/락걸린 SQL 찾아보기

CPU 사용율 높은 쿼리 찾기

USE master
SELECT TOP 10 
       REPLACE(CONVERT(VARCHAR(20), CONVERT(MONEY,qs.total_worker_time / qs.execution_count/1000),1),'.00','') as [Avg CPU Time(ms)]
      ,qs.execution_count
      ,substring (qt.text,
                  qs.statement_start_offset/2+1,
                  (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) as query_text
      ,qt.dbid
      ,sd.name
      ,qt.objectid
      ,qt.number
      ,qt.encrypted
      ,qt.text
      ,sr.session_id
      ,sr.command
      ,sr.status
      ,sr.last_wait_type
      ,sr.wait_resource
      ,sq.query_plan
      ,fs.text
  FROM sys.dm_exec_query_stats qs
       CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt 
       CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as sq
       LEFT OUTER JOIN sys.databases as sd on qt.dbid = sd.database_id
       LEFT OUTER JOIN sys.dm_exec_requests as sr on qs.sql_handle = sr.sql_handle
       OUTER APPLY sys.fn_get_sql(sr.sql_handle) AS fs       
 ORDER BY qs.total_worker_time / qs.execution_count DESC
GO

 

Lock 걸린 SQL 찾기

DB와 연계된 프로그램을 작성하다
보면 가끔씩 Lock 이 걸리는 형상을 발견하게 됩니다.
sp_lock 라는 명령어가 있지만 그건만으로는
좀 부족하죠 그래서 Lock 걸린 문장을 찾는 방법을 소개합니다.

declare @spid int = 0

select top 1 @spid = spid --, dbid, ObjId, IndId, Type, Resource, Mode, Status
from (
select convert (smallint, req_spid) As spid,
  rsc_dbid As dbid,
  rsc_objid As ObjId,
  rsc_indid As IndId,
  substring (v.name, 1, 4) As Type,
  substring (rsc_text, 1, 32) as Resource,
  substring (u.name, 1, 8) As Mode,
  substring (x.name, 1, 5) As Status

from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u

where master.dbo.syslockinfo.rsc_type = v.number
  and v.type = 'LR'
  and master.dbo.syslockinfo.req_status = x.number
  and x.type = 'LS'
  and master.dbo.syslockinfo.req_mode + 1 = u.number
  and u.type = 'L') a where a.Mode = 'X'
group by spid --, dbid, ObjId, IndId, Type, Resource, Mode, Status

PRINT '@spid > ' + CONVERT(VARCHAR(5), @spid)

if @SPID > 0
begin
  DBCC INPUTBUFFER(@spid)
end

 

spid >0 이라고 나오면 락걸린 쿼리가 없다는거고
DBCC INPUTBUFFER(@spid) 이 실행되면서 SQL 정보가 나오면
표시된 SQL에서 락이 걸린것임