Query to find Top 10 Adhoc Queries in SQL Server

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  June 6, 2012 at 9:21 am

  #392449

  Hi,

  Can anyone please suggest with a query to find Top 10 Adhoc Queries running in SQL Server?

  Thanks.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 6, 2012 at 9:31 am

  #1497700

  Top 10 by what criteria?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Grant Fritchey

  SSC Guru

  Points: 399061

  More actions

  June 7, 2012 at 5:50 am

  #1498079

  Assuming you're going to use sys.dm_exec_query_stats and then sort by CPU, I/O or some combination, then you can combine that DMO with sys.dm_exec_query_plan and look for queries that do not have an object_id. Those will be ad hoc queries (and functions & parameterized queries). It'll get you close.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• thadeushuck

  Ten Centuries

  Points: 1367

  More actions

  June 8, 2012 at 11:14 am

  #1498859

  --

  SELECT usecounts, cacheobjtype, objtype, size_in_bytes/1024 as 'Size(KB)', TEXT

  FROM sys.dm_exec_cached_plans

  CROSS APPLY sys.dm_exec_sql_text(plan_handle)

  WHERE usecounts = 1 and objtype = 'Adhoc'

  ORDER BY size_in_bytes/1024 DESC;

  --

  SELECT q.[text],

  (highest_cpu_queries.total_worker_time/highest_cpu_queries.execution_count) / 1000000.0 AS AverageCPU,

  highest_cpu_queries.execution_count as distinctCalls, highest_cpu_queries.total_worker_time,

  highest_cpu_queries.last_execution_time,

  highest_cpu_queries.last_physical_reads, highest_cpu_queries.min_physical_reads,

  highest_cpu_queries.sql_handle

  FROM (SELECT TOP 10 qs.sql_handle, qs.total_worker_time, qs.last_execution_time,

  qs.plan_generation_num, qs.execution_count, qs.last_physical_reads,

  qs.min_physical_reads 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 (sql_handle) AS q

  ORDER BY AverageCPU DESC

  --