>Which Queries to Pay Attention to…
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.
highest_cpu_queries.total_worker_time / 1000000 AS
(SELECT TOP 100
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