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 · 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

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 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

User · 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

[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