CPU Consumption on SQL Server

Posted on 2014-07-11
Last Modified: 2014-07-16
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

    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
Question by:AXISHK
    LVL 20

    Assisted Solution

    by:Marten Rune
    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
    LVL 26

    Accepted Solution

    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:

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now