locked
Excessive MS SQL CPU usage RRS feed

  • Question

  • We host several VPS servers for school clients around the country (http://www.servesouth.com), and have seen some crazy high usage of CPU by MS SQL and were wondering what we should be looking for that would be causing this.  I understand that SQL will each as much RAM as you can throw at it (by design), but not typically CPU.  We have 5 servers that are all hosting school websites, but only 1 that;s using tons of CPU (over 60%).

    Can anyone give me some ideas as to where I should start looking or what would be causing this?

    Thanks!

    Tuesday, May 4, 2010 1:12 AM

Answers

  • There might be cases in which the SQL processes running against the MSCRM database, start using all available CPU resources causing bad performance and downtimes. Using the following script it is easy to identify which processes are running in the SQL instance and, take reactive actions.

    -- THIS SCRIPT HAS TO BE USED IN CASE THE SQL SERVER CPU REMAINS CONSTANTLY AT 100%

    -- IT ALLOWS TO INTERCEPT (AND KILL IF NECESSARY) THE PROCESS CAUSING THE PROBLEM

    -- CHANGE THE CONTEXT TO MASTER AND LIST ALL THE PROCESSES RUNNING

    -- AGAINST THE CRM DATABASE SORTING BY CPU UTILIZATION.

    -- REMEMBER TO CHANGE THE DATABASE NAME ACCORDING TO THE NAME OF THE CRM DATABASE

    USE Master

    GO

     

    -- RETRIEVE THE DATABASE ID FOR THE MSCRM DATABASE

    DECLARE @DATABASE_ID INT

    SET @DATABASE_ID = DB_ID(N'OrganizationName_MSCRM');

     

    SELECT ST.TEXT,

    SP.*

    FROM DBO.SYSPROCESSES SP

    CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST

    WHERE SP.DBID = @DATABASE_ID

    ORDER BY CPU DESC

    GO

     

    -- ONCE YOU IDENTIFY THE PROCESS, IF IS POSSIBLE, KILL IT. TO KILL THE PROCESS IDENTIFY ITS

    -- SPID AND USE THE KILL COMMAND (REMEMBER TO REMOVE "<" AND ">")

    KILL <SPID>


    Tiaan van Niekerk http://crmdelacreme.blogspot.com Skype:tiaan.van.niekerk1
    Tuesday, May 4, 2010 4:41 AM