August 20, 2007 at 12:14 pm
wondering if someone can help me with pointing out most resource intensive query in the sql 2005. apparently some views have changed. I am able to use following query and find out plan_handle, sql_handle etc. but there is no query text in the result set. any idea which table has the text?
select
highest_cpu_queries
.plan_handle,
highest_cpu_queries
.total_worker_time,
q
.dbid,
q
.objectid,
q
.number,
q
.encrypted,
q
.[text]
from
(select top 50
qs
.plan_handle,
qs
.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order
by highest_cpu_queries.total_worker_time desc
thanks
August 21, 2007 at 7:20 am
I mixed and matched this for a while without compelling results and finally went to a third party tool teratrax.
Mike
August 21, 2007 at 8:19 am
Try this... I forget where I got this from
SELECT TOP 50
qs.total_worker_time/qs.execution_count as [Avg CPU Time],
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)
as query_text,
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY
[Avg CPU Time] DESC
August 21, 2007 at 9:48 am
It's the cross apply with the function that gets the text. It's not in a table, need a handle to the plan to get it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply