When it comes to database performance tuning, first information you must have is “Which queries are taking more CPU time and with which frequency these queries are being executed.". Following query will give you information’s like:
- Number of times this plan has been recompiled while it has remained in the cache
- Total amount of CPU time, in microseconds and seconds, that was consumed by executions of this plan since it was compiled.
- Time at which the plan was compiled.
- Number of times that the plan has been executed since it was last compiled.
- Minimum CPU time, in microseconds, that this plan has ever consumed during a single execution.
- Maximum CPU time, in microseconds, that this plan has ever consumed during a single execution.
SELECT
q.[text],
highest_cpu_queries.plan_generation_num,
highest_cpu_queries.total_worker_time AS
total_worker_time_in_microseconds,
highest_cpu_queries.total_worker_time / 1000000 AS
total_worker_time_in_seconds,
highest_cpu_queries.creation_time,
highest_cpu_queries.execution_count,
highest_cpu_queries.min_worker_time,
highest_cpu_queries.max_worker_time,
q.dbid
FROM
(SELECT TOP 100
plan_handle,
plan_generation_num,
creation_time,
execution_count,
min_worker_time,
max_worker_time,
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
WHERE q.dbid NOT IN (1,2,3,4) OR q.dbid IS NULL -- From User Databases Only
-- AND q.[text] LIKE '%YOUR SELECTED QUERY TEXT%'
ORDER BY highest_cpu_queries.total_worker_time DESC
excellent thank you....
ReplyDeleteCan you explain about the columns plan_generation_num, total_worker_time_in_microseconds, total_worker_time_in_seconds, creation_time, execution_count, min_worker_time, max_worker_time
ReplyDeleteI already explained these points in BULLETS. Please execute query on your desired database and to interpret results re-read the post, specially points in bullets.
ReplyDelete