CPU bottleneck in SQL Server

Hi Friends,

Inspired by Rahul’s post, here are 2 more scripts that can help in detecting CPU pressure

select top 50 
    sum(qs.total_worker_time) as total_cpu_time, 
    sum(qs.execution_count) as total_execution_count,
    count(*) as  number_of_statements, 
    qs.plan_handle 
from
    sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc

The above query gives you a high-level view of which currently cached batches or procedures are using the most CPU.

   

The following query gives you time spent by workers in RUNNABLE state.

SELECT SUM(signal_wait_time_ms)/1000 as [Wait time in Sec]
FROM sys.dm_os_wait_stats;

You can use the value of above query and compare with your baseline to see if there is any major difference. In fact, you can build a trend analysis on this value.

Hope this helps. Note: There are many such queries you can find here;

 

 

   

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

2 Comments on “CPU bottleneck in SQL Server”

  1. Hi Robert,

    Thanks for raising the concern. I would like to reciprocate:

    1. The same query is available from multiple sources like blogs, articles, whitepapers, etc, some Microsoft and some non-Microsoft.

    2. After reading your comment, I ran a search on Google “CPU bottleneck in SQL Server” and immediately found 2 sources in the first page result which had this script. Here are the links apart from the link you gave me:

    http://msdn.microsoft.com/en-us/library/cc966540.aspx

    http://www.google.com/url?sa=t&source=web&cd=2&ved=0CB0QFjAB&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2FD%2FB%2FD%2FDBDE7972-1EB9-470A-BA18-58849DB3EB3B%2FTShootPerfProbs2008.docx&ei=KO5TToqlDtDGrAeKoLXoBg&usg=AFQjCNHcN0L4Dc0MpPXVfvbtBTAXDy8a6g

    Over the years, Microsoft has published hundreds of such scripts through different sources to be used by customers and community alike. Like many SQL folks, I use them in day to day work without remembering the source of it. For example, some reader of this post can take this query and use it in his work and may be later blog about it – end of the day, it’s just a query !

    Coming to your concern about the “description”. I have about 30 different queries to troubleshoot CPU bottleneck, many of them are DMVs with variations. Each script has a one-liner comment to tell what the script does. All in a notepad file. Not denying that, I might have taken it from the same source or may be another source some months or years back and put the same on-liner without putting down the source, so kindly don’t penalize me for that 🙂

    Even for the next query, if you really see, you can find a match for this also “time spent by workers in RUNNABLE state” 🙂

    Think about it, what problems will an author really have to put a reference to MSDN/TechNET/BOL sites, its just that I pulled out a query along with the comment from my repository !

    We at SQLServerGeeks.com take utmost care of plagiarism and any sort of copy right violations. In case you find any, let me know, I will be happy to bring down the content. If we post content from another source, we should and we will definitely attribute the source.

    Going by your current suggestion, I have modified this blog post and put a reference to MSDN & TechNET site. I thank you again for notifying this to me. And thanks much for reading our posts, we value your inputs !

    Regards

    Amit

Leave a Reply

Your email address will not be published.