use master 
go 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_spidtop10]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sp_spidtop10] 
GO 

CREATE PROCEDURE sp_spidtop10 (@order    varchar(3) ='CPU', 
                               @spid     int        = NULL, 
                               @interval int        = NULL) 
AS 
/* 
Procedure to show top 10 resource using SPID's 
based on a passed in order value (default CPU) 
or view the resource usage of one spid, for a 
period of 30 seconds (default) 

Change Description                  Author      Date 
Initial Creation                    J Smith     10 Sep 2001 

*/ 

SET NOCOUNT ON 

   DECLARE @cinterval char(8) 
   
   IF @interval IS NOT NULL 
   BEGIN 
      IF @interval > 59 SET @interval = 59  /* max period */ 
      SET @cinterval = '00:00:' + CAST(@interval as char(2)) 
   END 
   ELSE 
   BEGIN 
      SET @cinterval = '00:00:30' /* default period */ 
   END 
   
   CREATE TABLE #thread (RUN INT NOT NULL, 
                         SPID INT NOT NULL, 
                         THREAD INT NULL, 
                         STATUS varchar(30) NOT NULL, 
                         [IO] INT NOT NULL, 
                         CPU INT NOT NULL, 
                         MEM INT NOT NULL, 
                         LOCKS int NOT NULL, 
                         BLK INT NOT NULL 
                         
                         CONSTRAINT PK_thread1 PRIMARY KEY CLUSTERED 
                         (RUN,SPID)) 
   
   /* Initial snapshot */ 
   INSERT #thread 
   SELECT 1,pr.spid,pr.kpid,pr.status,pr.physical_io,pr.cpu, 
          pr.memusage,ISNULL(count(lk.spid),0),pr.blocked 
   FROM   master.dbo.sysprocesses pr 
   LEFT OUTER JOIN  master.dbo.syslocks  lk ON pr.spid = lk.spid 
   WHERE pr.spid>=50 AND pr.spid<>@@SPID 
     AND (@spid is NULL OR pr.spid=@spid) 
        GROUP BY pr.spid,pr.kpid,pr.status,pr.physical_io, 
            pr.cpu,pr.memusage,pr.blocked 

   
   /* controlled by @interval parameter */ 
   WAITFOR DELAY @cinterval 

   
   /* Secondary snapshot */ 
   INSERT #thread 
   SELECT 2,pr.spid,pr.kpid,pr.status,pr.physical_io,pr.cpu, 
          pr.memusage,ISNULL(count(lk.spid),0),pr.blocked 
   FROM   master.dbo.sysprocesses pr 
   LEFT OUTER JOIN  master.dbo.syslocks  lk ON pr.spid = lk.spid 
   WHERE pr.spid>=50 AND pr.spid<>@@SPID 
     AND (@spid is NULL OR pr.spid=@spid) 
        GROUP BY pr.spid,pr.kpid,pr.status,pr.physical_io, 
            pr.cpu,pr.memusage,pr.blocked 
   
   
   /* Get the top 10 ordered by @order (default CPU) */ 
   SELECT TOP 10 T.* 
   FROM( 
          SELECT a.SPID,b.THREAD,b.STATUS,(b.[IO]-a.[IO]) as 'IO', 
                 (b.CPU-a.CPU) as 'CPU',(b.MEM-a.MEM) as 'MEM', 
                 b.LOCKS,b.BLK 
            FROM #thread a 
            JOIN #thread b 
            ON a.SPID=b.SPID and a.RUN=1 and b.RUN=2) T 
   ORDER BY CASE @order 
            WHEN 'IO'  THEN [IO] 
            WHEN 'CPU' THEN CPU 
            WHEN 'MEM' THEN MEM 
            WHEN 'LCK' THEN LOCKS 
            ELSE CPU END DESC 
   
   DROP TABLE #thread 

RETURN 
GO