Database running at 100%

Question

My database server is using 100% of the processing all of a sudden and it doesn't seem like I have any more traffic on my sites. What can I do to look where the increased traffic is coming from inside of SQL Server Management Studio?

Answer 1

Try checking the amount of free space you have in your files. It may well be that SQL is off increasing the size of your mdf/ldf

If you haven't got much free space set to increase by fixed size rather than %

Answer 2

Take a look at the sql server activity monitor, specifically the cpu column. maybe you have one or more queries running wild.

Answer 3

although I use this query to quickly check for blocking (with low overhead), you can use it to see how many processes you have running, each process's cpu time, reads, writes, and logical_reads:

;with Blockers AS
(   SELECT

        r.session_id AS spid
            ,r.blocking_session_id AS BlockingSPID
            ,LEFT(OBJECT_NAME(st.objectid, st.dbid),50) AS ShortObjectName
            ,LEFT(DB_NAME(r.database_id),50) AS DatabaseName
            ,s.program_name
            ,s.login_name
            ,r.cpu_time,r.reads,r.writes,r.logical_reads 
            ,OBJECT_NAME(st.objectid, st.dbid) AS ObjectName
            ,SUBSTRING(st.text, (r.statement_start_offset/2)+1,( (CASE r.statement_end_offset
                                                                      WHEN -1 THEN DATALENGTH(st.text)
                                                                      ELSE r.statement_end_offset
                                                                  END - r.statement_start_offset
                                                                 )/2
                                                               ) + 1
                      ) AS SQLText
        FROM sys.dm_exec_requests                          r
            JOIN sys.dm_exec_sessions                      s ON r.session_id = s.session_id
            CROSS APPLY sys.dm_exec_sql_text (sql_handle) st
    --WHERE r.session_id > 50
)
SELECT Blockers.* FROM Blockers WHERE spid!=@@SPID

Answer 4

A typical cause of high processor usage is unoptimized queries, from stored procedures or otherwise. I would take a look at what stored procedures you have running frequently and make sure that they are running efficiently and are properly indexed.

Some examples of this and resources: http://msmvps.com/blogs/omar/archive/2006/08/15/100-cpu-100-io-a-near-death-experience-for-sql-server-2005-and-us.aspx

http://www.aneef.net/2009/04/11/100-cpu-sql-server-2005-servers-crashes/

An article on Performance tuning: http://207.46.16.252/en-us/magazine/2007.10.sqlcpu.aspx