SQL Server 2005 이후 버전은 DMV를 이용해서 간단하게 Bottleneck을 일으키는 쿼리를 찾을 수 있다.
--CPU Bottleneck 상위50개
select top 50
qs.total_worker_time / execution_count as avg_worker_time,
substring(st.text, (qs.statement_start_offset/2)+1,
((case qs.statement_end_offset
when -1 then datalength(st.text)
else qs.statement_end_offset
end - qs.statement_start_offset)/2) + 1) as statement_text,
*
from
sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
order by
avg_worker_time desc
--I/O Bottleneck 상위50개
select top 50
(total_logical_reads + total_logical_writes) as total_logical_io,
(total_logical_reads/execution_count) as avg_logical_reads,
(total_logical_writes/execution_count) as avg_logical_writes,
(total_physical_reads/execution_count) as avg_phys_reads,
substring(st.text, (qs.statement_start_offset/2)+1,
((case qs.statement_end_offset
when -1 then datalength(st.text)
else qs.statement_end_offset
end - qs.statement_start_offset)/2) + 1) as statement_text,
*
from
sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
order by
total_logical_io desc
'SQL Server > Tips' 카테고리의 다른 글
SQL Server DBCC CHECKDB (0) | 2012.07.30 |
---|---|
SQL Server COALESCE 함수 (0) | 2012.07.26 |
SQL Server KILL WITH STATUSONLY 옵션 (0) | 2012.07.26 |
링크 (0) | 2012.07.19 |
tempdb 공간 사용 현황 (0) | 2011.03.25 |