June 6, 2012 at 9:21 am
Hi,
Can anyone please suggest with a query to find Top 10 Adhoc Queries running in SQL Server?
Thanks.
June 6, 2012 at 9:31 am
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
June 7, 2012 at 5:50 am
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
June 8, 2012 at 11:14 am
--
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
--
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply