Tuesday, January 4, 2011

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.
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

3 comments:

  1. excellent thank you....

    ReplyDelete
  2. Can 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

    ReplyDelete
  3. I 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

All suggestions are welcome