[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

CPU Consumption on SQL Server

I have used the following query to get the 10 worst CPU consuming queries on the SQL Server. what fact can cause a high CPU consumption ? Will it be possible that the CPU are doing table scan ? Tks


SELECT TOP 10
    sql.text as sql
   , qp.query_plan
   , creation_time
   , last_execution_time
   , execution_count
   , (total_worker_time / execution_count) as avg_cpu
   , total_worker_time as total_cpu
   , last_worker_time as last_cpu
   , min_worker_time as min_cpu
   , max_worker_time as max_cpu
   , (total_physical_reads + total_logical_reads) as total_reads
   , (max_physical_reads + max_logical_reads) as max_reads
   , (total_physical_reads + total_logical_reads) / execution_count as avg_reads
   , max_elapsed_time as max_duration
   , total_elapsed_time as total_duration
   , ((total_elapsed_time / execution_count)) / 1000000 as avg_duration_sec
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
CROSS APPLY sys.dm_exec_query_plan (plan_handle) qp
ORDER BY qs. total_worker_time DESC
0
AXISHK
Asked:
AXISHK
2 Solutions
 
Marten RuneCommented:
From here on, examine each question one by one. And look att the queryplan, it will show if thera are table Scans. Now remember, a table scan may very well be the desired method for gathering a chunk of data.

It's not neccessarily bad.

You can try to run the Querys through DTA (Data Tuning Advisor), it might give you some hints on what could/should be done. But all in all it's a craftmanship to tune Querys, try measure and try again is my advice, and remember a perfectly tuned Query might be a considerable constrain for other parts of the application, i e maintaining indexes etc.

Regards Marten
0
 
ZberteocCommented:
The query executed like this is deceiving. It will show you the overall statistic since the server was last time restarted, which may not be useful because it will show a accumulation of CPU time that could not be relevant to the moment situation. What you really want is the statistics for the last, let's say, 24hr, could be an hour, depending of the needs. In the form posted a query that is optimized will still be shown at the top for a long time after that.

The best way to do this is either to purge the stats  before you run it and store the results in a table.  If you don't want to purge the stats then you should compare the results between the last and previous run.

Another thing is that CPU is not the best way to monitor the performance, waits is much more to the truth. Here is a good article by SQL guru Paul Randal, one of those who built the SQL engine:

http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now