[MSSQL] 점유율 높은 쿼리 찾기

-- LOCK  죽이기

SELECT * FROM

(

SELECT 

request_session_id, 

'kill ' + convert(varchar(10), request_session_id) AS [KILL],

COUNT(*) AS num_locks 

FROM sys.dm_tran_locks WITH(NOLOCK) 

GROUP BY request_session_id 

) AS T

WHERE num_locks > 1 

ORDER BY num_locks desc

-- CPU 점유율이 높은 쿼리 10개

SELECT TOP 10 

 [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;