Admit it, you've been there. You're sitting there working on your computer, and all of a sudden it starts chugging, or you hear the Hard Drive grinding away. A quick ctrl+shift+esc brings up your trusted friend: Windows Task Manager. Within a second you can see how hard the CPU is being hit, and by zeroing in on the processes tab and clicking the CPU column (twice -- which bugs; I wish it sorted descending by default) you can see which processes are burning up CPU.

It's true that there are better tools out there to get an idea of what 'rogue' processes are burning through your resources, but sometimes you don't care to use them (mostly because you're just trying to catch something in the act and don't have the time to start up perfmon, or SysInternals' ProcessExplorer, etc.)

I've been in a number of similar situations with SQL Server. You know something is burning through CPU on the box (cuz you can see it in Windows Task Manager on the Server, and you can see that sqlservr.exe is the culprit). Windows Task Manager won't show you what individual processes/connections etc are doing in SQL Server, because SQL Server implements its own thread scheduling via the User Mode Scheduler (UMS). So, to get an accurate idea of what exact process is burning up your server from within SQL Server, you have to gain access to threading info from the UMS. However, if you go look in Enterprise Manager at the Management > Current Activity > Process Info Node you'll notice that trying to discern what is burning through the CPU on the SQL Server end is virtually impossible as EM sorts them all as strings. Using something like:

SELECT * FROM master..sysprocesses ORDER BY CPU DESC

Is an okay idea, but the problem is that the CPU listed there is a representation of all the CPU accumulated by each PID since it connected, or since the server started operating. Unless you're really quick with numbers and can notice which cpu values are changing, and by how much, each time you hit F5 to refresh your query... you're not going to get very far.

Below is a script I've used with a great deal of success to help me get a very good handle on not only what is burning through my CPU at 'problematic times', but has given me a good insight into just what takes place on my SQL Server at any given time.

The idea for the script is a simple one. Take the wealth of information returned by querying sysprocesses, and give it a point of reference. To put it another way... create a way to query sysprocesses from an explicit starting point. The result is to create a report/query that you can refresh repeatedly which will show you quite well what is burning through your CPU since you last set a 'start point'

I've implemented this through the use of a global temp table, and a temp sproc. Feel free to tweak it in whatever way makes sense to you. The logic involved represents what I believe to be an idiom, even if it does sadly have to rely upon directly querying sys tables to execute. (The querying of sys tables directly is a non-best-practice because the resulting query is NOT impervious to change - though at the time of writing, the following 'query' not only works fine against Yukon Beta 1, but pulls back other exciting information not available in SQL Server 2000)

IF (OBJECT_ID('tempdb..#sp_sql_top')) IS NOT NULL DROP PROC #sp_sql_top
GO
CREATE PROC #sp_sql_top  
 @reset  bit = 0
AS
 SET NOCOUNT ON
 IF (@reset = 1) BEGIN
    IF (OBJECT_ID('tempdb..##sysproc')) IS NOT NULL 
  DROP TABLE ##sysproc
 END
 IF (OBJECT_ID('tempdb..##sysproc')) IS NULL  BEGIN
  SELECT spid,waittime,cpu,physical_io,[memusage],last_batch 
  INTO ##sysproc 
  FROM master..sysprocesses
 END
 -- Remove any recycled spids:
 DELETE ##sysproc FROM ##sysproc t
 INNER JOIN master..sysprocesses s ON t.spid = s.spid
 WHERE t.last_batch < s.login_time
 
 SELECT 
  r.spid,
  r.blocked,
  r.waittime - ISNULL(t.waittime,0) waittime,
  r.lastwaittype,
  r.cpu - ISNULL(t.cpu,0) cpu,
  r.physical_io - ISNULL(t.physical_io,0) phyiscal_io,
  r.[memusage] - ISNULL(t.[memusage],0) [memusage],
  r.cmd,
  r.open_tran,
  CASE 
  WHEN r.last_batch > ISNULL(t.last_batch, GETDATE() -1) 
     THEN r.last_batch ELSE NULL END last_batch,
  CAST(RTRIM(r.hostname) AS varchar(30)) hostname,
  CAST(RTRIM(r.program_name) as varchar(128)) program_name,
  r.nt_username,
  r.loginame
 FROM
  master..sysprocesses r 
  RIGHT OUTER JOIN ##sysproc t ON r.spid = t.spid
 WHERE
  r.spid <> @@SPID
 ORDER BY
  r.cpu - t.cpu DESC
GO
EXEC #sp_sql_top  -- or to refresh: EXEC #sp_sql_top 1

Fire up Query Analyzer and give this query a try. You'll be pleased. Refreshing it over and over will give you a great idea of what kind of things your threads are doing, what they're waiting on, etc.