Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

DBA Tools: sp_whocpu

By Mircea Nita,

In recent years I often needed to quickly identify the processes that consume the most CPU on the server in real time, without settin up traces.

If you try to run sp_who2 and base your investigation on the CPUTime listed there, you will find that the processes showing the highest figures, might not be in fact those that consume the most CPU in real time.

This is because sp_who2 displays the total CPUTime accumulated since the connection was established, therefore sp_who2 can show high CPUTime ficures, which do not reflect the current activity on the server.

In order to get an indication on the real time CPU for the user processes, I have modified the sp_who2 stored procedure to include a new metric.

The result is called sp_whocpu and in addition to the usual columns returned by sp_who2 it contains a new metric that I introduced, called CPU_Delta.

The CPU_Delta is calculated by sampling the CPU readings over a period of time, and returns accurate figures in milliseconds about the CPU activity per process. I found that setting the sampling period at 3 seconds is a good choice for calculating the CPU_Delta.

Like in any sampling process, increasing the sampling period and the number of samples taken would increase the accuracy. However, three seconds is a period that I found suited for this, as it is also the time you have to wait before getting any output from sp_whocpu.

The stored procedure sp_whocpu orders the output by CPU_Delta in descending order, therefore making it easy to rapidly identify the top CPU consumers on the server.

The CPU_Total time is the same figure as the one returned by sp_who2 which appears listed there as just CPUTime. The CPU_Delta figure is the CPU time consumed within the 3 seconds sampling period.

 

 

From the figure above you can immediately see that the process having the highest CPU at the time of the investigation, is not the process that has the highest accumulated cpu (CPU_Total). It would be therefore impossible to determine the highest momentary CPU only based on what the standard sp_who2 returns.

 

I hope that sp_whocpu is going to prove as useful to you as it is for me in investigating performance issues.

Total article views: 3168 | Views in the last 30 days: 10
 
Related Articles
FORUM

sp_who2

sp_who2

FORUM

CURSOR READ SP_WHO2

CURSOR READ SP_WHO2

FORUM

Delta Load in SSIS

Delta Load in SSIS

FORUM

sp_who2, Command = NOP

in sp_who2, what is command = NOP

FORUM

sp_who2 results show one spid 24 times

Results of sp_who2 shows ~24 lines for one spid

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones