How do I find out what is hammering my SQL Server

Question

My SQL Server CPU has been at around 90  for the most part of today   I am not in a position to be able to restart it due to it being in constant use   Is it possible to find out what within SQL is causing such a CPU overload   I have run SQL Profiler but so much is going on it s difficult to tell if anything in particular is causing it   I have run sp who2 but am not sure what everything means exactly and if it is possible to identify possible problems in here   To pre-empt any  it s probably just being used a lot  responses  this has only kicked in today from perfectly normal activitly levels   I m after any way of finding what is causing CPU grief within SQL

User · Accepted Answer

I assume due diligence here that you confirmed the CPU is actually consumed by SQL process (perfmon Process category counters would confirm this). Normally for such cases you take a sample of the relevant performance counters and you compare them with a baseline that you established in normal load operating conditions. Once you resolve this problem I recommend you do establish such a baseline for future comparisons.

You can find exactly where is SQL spending every single CPU cycle. But knowing where to look takes a lot of know how and experience. Is is SQL 2005/2008 or 2000 ? Fortunately for 2005 and newer there are a couple of off the shelf solutions. You already got a couple good pointer here with John Samson's answer. I'd like to add a recommendation to download and install the SQL Server Performance Dashboard Reports. Some of those reports include top queries by time or by I/O, most used data files and so on and you can quickly get a feel where the problem is. The output is both numerical and graphical so it is more usefull for a beginner.

I would also recommend using Adam's Who is Active script, although that is a bit more advanced.

And last but not least I recommend you download and read the MS SQL Customer Advisory Team white paper on performance analysis: SQL 2005 Waits and Queues.

My recommendation is also to look at I/O. If you added a load to the server that trashes the buffer pool (ie. it needs so much data that it evicts the cached data pages from memory) the result would be a significant increase in CPU (sounds surprising, but is true). The culprit is usually a new query that scans a big table end-to-end.

User · Answer

For a GUI approach I would take a look at Activity Monitor under Management and sort by CPU

User · Answer

This query uses DMV s to identify the most costly queries by CPU  SELECT TOP 20     qs sql handle      qs execution count      qs total worker time AS Total CPU      total CPU inSeconds   --Converted from microseconds         qs total worker time 1000000      average CPU inSeconds   --Converted from microseconds          qs total worker time 1000000    qs execution count      qs total elapsed time      total elapsed time inSeconds   --Converted from microseconds         qs total elapsed time 1000000      st text      qp query plan FROM     sys dm exec query stats AS qs CROSS APPLY      sys dm exec sql text qs sql handle  AS st CROSS APPLY     sys dm exec query plan  qs plan handle  AS qp ORDER BY      qs total worker time DESC   For a complete explanation see  How to identify the most costly SQL Server queries by CPU

User · Answer

You can find some useful query here   Investigating the Cause of SQL Server High CPU   For me this helped a lot   SELECT s session id      r status      r blocking session id  Blk by       r wait type      wait resource      r wait time    1000   60   Wait M       r cpu time      r logical reads      r reads      r writes      r total elapsed time    1000   60   Elaps M       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 statement text      Coalesce Quotename Db name st dbid     N      Quotename Object schema name st objectid  st dbid     N          Quotename Object name st objectid  st dbid        AS command text      r command      s login name      s host name      s program name      s last request end time      s login time      r open transaction count FROM sys dm exec sessions AS s     JOIN sys dm exec requests AS r ON r session id   s session id     CROSS APPLY sys Dm exec sql text r sql handle  AS st WHERE r session id      SPID ORDER BY r cpu time desc   In the fields of status  wait type and cpu time you can find the most cpu consuming task that is running right now

User · Answer

You can run the SQL Profiler  and filter by CPU or Duration so that you re excluding all the  small stuff   Then it should be a lot easier to determine if you have a problem like a specific stored proc that is running much longer than it should  could be a missing index or something    Two caveats     If the problem is massive amounts of tiny transactions  then the filter I describe above would exclude them  and you d miss this  Also  if the problem is a single  massive job  like an 8-hour analysis job or a poorly designed select that has to cross-join a billion rows  then you might not see this in the profiler until it is completely done  depending on what events you re profiling  sp completed vs sp statementcompleted     But normally I start with the Activity Monitor or sp who2

User · Answer

Run either of these a few second apart  You ll detect the high CPU connection  Or  stored CPU in a local variable  WAITFOR DELAY  compare stored and current CPU values  select   from master  sysprocesses where status    runnable  --comment this out order by CPU desc  select   from master  sysprocesses order by CPU desc   May not be the most elegant but it d effective and quick

[sql-server] How do I find out what is hammering my SQL Server?

Examples related to sql-server

Examples related to performance

Examples related to cpu-usage