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

Posted on January 4, 2011, in Performance Tuning, TSQL Tips n Tricks. Bookmark the permalink. 3 Comments.

  1. >excellent thank you….

  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

  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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: