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
AXISHKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.