SQL Server Performance Forum – Threads Archive

Which query or SP is using the most CPU?

Hi, I have got a SQL Server 2005 database on a Windows 2003 Server x64.
The server has got 4 dual core Xeons with 16GB memory.
In the last few weeks sqlservr.exe has been hitting 100% CPU usage on all 8 CPUs. What is the best method to use to find out which query or stored proc is using the most amount of CPU? Thanks

The following blogs should get you most of the information you required in this issue:
http://sqlserver-qa.net/blogs/perft…op-stored-procedures-that-are-recompiled.aspx http://sqlserver-qa.net/blogs/perftune/archive/2007/05/08/stored-procedures-performance-issues.aspx http://sqlserver-qa.net/blogs/perft…that-a-query-could-benefit-from-an-index.aspx http://sqlserver-qa.net/blogs/perftune/archive/2007/04/26/high-cpu-spikes-affecting-performance.aspx Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Thanks Satya. Have found the culprit is a sproc which is running 6 times a second!!

[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] glad you could find the relevant issue from the blogs above, appreciate your feedback. Also ensure to manage the recompilatons as per other blog entries shown above. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

]]>