exec sp_executesql @stmt=N'begin try<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
select top 10 rank() over(order by (total_worker_time+0.0)/execution_count desc,sql_handle,statement_start_offset ) as row_no<o:p></o:p>
,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (rank() over(order by (total_worker_time+0.0)/execution_count desc,sql_handle,statement_start_offset ))%2 as l1<o:p></o:p>
,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; creation_time<o:p></o:p>
,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; last_execution_time <o:p></o:p>
,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (total_worker_time+0.0)/1000 as total_worker_time<o:p></o:p>
,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]<o:p></o:p>
,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; total_logical_reads as [LogicalReads]<o:p></o:p>
,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; total_logical_writes as [LogicalWrites]<o:p></o:p>
,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; execution_count<o:p></o:p>
,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; total_logical_reads+total_logical_writes as [AggIO]<o:p></o:p>
,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (total_logical_reads+total_logical_writes)/(execution_count+0.0) as [AvgIO]<o:p></o:p>
,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; case when sql_handle IS NULL<o:p></o:p>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; then '' ''<o:p></o:p>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else ( substring(st.text,(qs.statement_start_offset+2)/2,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (case when qs.statement_end_offset = -1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; then len(convert(nvarchar(MAX),st.text))*2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else qs.statement_end_offset&nbsp;&nbsp;&nbsp; end - qs.statement_start_offset) /2&nbsp; ) )<o:p></o:p>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end as query_text <o:p></o:p>
,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; db_name(st.dbid) as db_name<o:p></o:p>
,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; st.objectid as object_id<o:p></o:p>
from sys.dm_exec_query_stats&nbsp; qs<o:p></o:p>
cross apply sys.dm_exec_sql_text(sql_handle) st<o:p></o:p>
where total_worker_time&nbsp; &gt; 0 <o:p></o:p>
order by [AvgCPUTime] desc<o:p></o:p>
end try<o:p></o:p>
begin catch<o:p></o:p>
select -100 as row_no<o:p></o:p>
,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 as l1, 1 as create_time,1 as last_execution_time,1 as total_worker_time,1 as AvgCPUTime,1 as LogicalReads,1 as LogicalWrites<o:p></o:p>
,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ERROR_NUMBER() as execution_count<o:p></o:p>
,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ERROR_SEVERITY() as AggIO<o:p></o:p>
,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ERROR_STATE() as AvgIO<o:p></o:p>
,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ERROR_MESSAGE() as query_text<o:p></o:p>
,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 as db_name<o:p></o:p>
,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 as object_name<o:p></o:p>
end catch',@params=N''<o:p></o:p>
<o:p>&nbsp;</o:p>

서버 켠 후 부터 CPU 가장 많이 잡아먹는 쿼리 찾기