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;
If you’re going to post content from other sources, please provide attribution. Even your description of the first query is taken directly off of this page: http://technet.microsoft.com/en-us/library/bb838723(office.12).aspx
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