SQL Server Performance

Which query or SP is using the most CPU?

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by fraparty, May 14, 2007.

  1. fraparty New Member

    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
  2. satya Moderator

    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.
  3. fraparty New Member

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

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] glad you could find the relevant issue from the blogs above, appreciate your feedback.<br />Also ensure to manage the recompilatons as per other blog entries shown above.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>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.</i></font id="size1"></font id="teal"></center>

Share This Page