Friday, February 20, 2009

Statements having High CPU Usage

select top 50 total_worker_time/execution_count as avg_cpu_cost, execution_count,db_name(depamain.dbid) as dbname,object_name(depamain.objectid,depamain.dbid) as objname,
(selecct substriung(text, statement_start_offset/2 + 1,
(case when statement_end_offset = -1 then len(covert(nvarchar(max), text)) * 2 else statement_end_offset nd - statement_start_offset)/2)
from sys.dm_exec_sql_text(sql_handle)) as query_text
from sys.dm_exec_query_stats
outer apply
( select dbid,objectid from
( select attribute,cast(value as int) as attvalue
from sys.dm_exec_plan_attributes(plan_handle)
where attribute in ('dbid','objectid')
) as depa
pivot ( max(depa.attvalue) for depa.attribute
in ("dbid", "objectid") ) as depapvt ) as depamain
where execution_count > 4
order by [avg_cpu_cost] desc;

No comments: