identify most resource intensive query

• sqlstar2011

  SSCertifiable

  Points: 6000

  More actions

  August 20, 2007 at 12:14 pm

  #118136

  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

• mngong

  SSCertifiable

  Points: 6167

  More actions

  August 21, 2007 at 7:20 am

  #728287

  I mixed and matched this for a while without compelling results and finally went to a third party tool teratrax.

  Mike

• Rich Hansell

  SSCrazy

  Points: 2396

  More actions

  August 21, 2007 at 8:19 am

  #728308

  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

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737403

  More actions

  August 21, 2007 at 9:48 am

  #728363

  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.