[MS SQL] 부하걸리는 SQL문 확인하기

1. 실행가능작업의 값 확인
- 이거는 현재 MSSQL의 테스크 사용량을 확인하는 명령어 같음...

 select Scheduler_ID, Current_Tasks_Count, Runnable_Tasks_Count from  SYS.dm_os_schedulers 
 WHERE Scheduler_ID < 255

 

2. CPU를 많이 사용하는 상위 100개의 SQL문 확인

select top 100 
(a.total_worker_time/a.execution_count) as [Avg_CPU_Time], 
CONVERT(Varchar, Last_Execution_Time) AS 'Last_execution_Time', 
Total_Physical_Reads, 
SUBSTRING(b.text, a.statement_start_offset/2, 
(case when a.statement_end_offset = -1 then LEN(convert(nvarchar(max), b.text)) *2 else a.statement_end_offset end - a.statement_start_offset)/2) as Query_Text, 
dbname = upper(db_name(b.dbid)), 
b.objectid as 'Object_ID' 
from sys.dm_exec_query_stats a 
cross apply sys.dm_exec_sql_text(a.sql_handle) as b 
order by [Avg_CPU_Time] desc