Love this script. Hope you don't mind, I added one tweak, to pass a time parameter in (default to 3 seconds). Changes bolded below.

if exists (select * from master.dbo.sysobjects where id = object_id('dbo.sp_whocpu') )

Drop Procedure dbo.sp_whocpu

go

/*====================================================================

-- Mircea Anton Nita - 2010

-- https://www.mcpvirtualbusinesscard.com/VBCServer/Mircea/card

======================================================================*/

Create Procedure dbo.sp_whocpu

@samplingTime int = 3, -- seconds to pass, but maybe don't get carried away

@dbname sysname = null,

@loginame sysname = null

as

set nocount on

declare

@retcode int

,@sidlow varbinary(85)

,@sidhigh varbinary(85)

,@sid1 varbinary(85)

,@spidlow int

,@spidhigh int

,@seldbid varchar(10)

,@charMaxLenLoginName varchar(24)

,@charMaxLenDBName varchar(24)

,@charMaxLenCPUTime varchar(10)

,@charMaxLenCPUDelta varchar(10)

,@charMaxLenDiskIO varchar(10)

,@charMaxLenHostName varchar(24)

,@charMaxLenProgramName varchar(10)

,@charMaxLenLastBatch varchar(10)

,@charMaxLenCommand varchar(10)

,@charsidlow varchar(85)

,@charsidhigh varchar(85)

,@charspidlow varchar(11)

,@charspidhigh varchar(11)

,@command varchar(8000)

,@samplingTimeString varchar(10) -- string version of sampleTime to pass to WAITFOR DELAY

-- CHANGES TO TAKE VARIABLE SAMPLING TIME

if @samplingTime < 3 set @samplingTime = 3 -- use the acceptable minimum

SELECT @samplingTimeString = cast(

(

CASE WHEN @samplingTime/3600<10 THEN '0' ELSE '' END

+ RTRIM(@samplingTime/3600)

+ ':' + RIGHT('0'+RTRIM((@samplingTime % 3600) / 60),2)

+ ':' + RIGHT('0'+RTRIM((@samplingTime % 3600) % 60),2)

) as varchar(10))

-- set defaults

set @retcode = 0

set @sidlow = convert(varbinary(85), (replicate(char(0), 85)))

set @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))

set @spidlow = 0

set @spidhigh = 32767

if (@dbname is not null)

set @seldbid = cast((select top 1 dbid from master.dbo.sysdatabases where name like '%'+@dbname+'%') as varchar(10))

else

set @seldbid = '0'

if (@loginame is null) -- Simple default to all LoginNames.

GOTO LABEL_PARAM

select @sid1 = null

if exists(select * from sys.syslogins where loginname = @loginame)

select @sid1 = sid from sys.syslogins where loginname = @loginame

if (@sid1 is not null) -- The parameter is a recognized login name.

begin

select @sidlow = suser_sid(@loginame)

,@sidhigh = suser_sid(@loginame)

GOTO LABEL_PARAM

end

if (lower(@loginame collate Latin1_General_CI_AS) in ('Active')) -- Special action, not sleeping.

begin

select @loginame = lower(@loginame collate Latin1_General_CI_AS)

GOTO LABEL_PARAM

end

if (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0) -- Is a number.

begin

select

@spidlow = convert(int, @loginame)

,@spidhigh = convert(int, @loginame)

GOTO LABEL_PARAM

end

raiserror(15007,-1,-1,@loginame)

select @retcode = 1

GOTO LABEL_RETURN

LABEL_PARAM:

-- Getting data over a time window to allow the cpu_delta metric calculation

if object_id('tempdb.dbo.#cpu1') is not null drop table #cpu1

if object_id('tempdb.dbo.#cpu2') is not null drop table #cpu2

select spid, cpu into #cpu1 from master.dbo.sysprocesses with (nolock) order by cpu desc

waitfor delay @sampling_time_string

select spid, cpu into #cpu2 from master.dbo.sysprocesses with (nolock) order by cpu desc

-------------------- Capture consistent sysprocesses. -------------------

select

sp.spid

,status

,sid

,hostname

,program_name

,cmd

,sp.cpu

,c2.cpu-c1.cpu as 'cpu_delta'

,physical_io

,blocked

,dbid

,convert(sysname, rtrim(loginame)) as loginname

,sp.spid as 'spid_sort'

, substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '

+ substring( convert(varchar,last_batch,113) ,13 ,8 ) as 'last_batch_char'

into #tb1_sysprocesses

from #cpu2 c2 join #cpu1 c1 on c2.spid = c1.spid join master.dbo.sysprocesses sp with (nolock) on sp.spid = c2.spid

where c2.cpu-c1.cpu > 0

if @@error <> 0

begin

select @retcode = @@error

GOTO LABEL_RETURN

end

if (@loginame in ('active'))

delete #tb1_sysprocesses

where lower(status) = 'sleeping'

and upper(cmd) in (

'AWAITING COMMAND'

,'LAZY WRITER'

,'CHECKPOINT SLEEP'

)

and blocked = 0

and dbid <> @seldbid

-- Prepare to dynamically optimize column widths.

select

@charsidlow = convert(varchar(85),@sidlow)

,@charsidhigh = convert(varchar(85),@sidhigh)

,@charspidlow = convert(varchar,@spidlow)

,@charspidhigh = convert(varchar,@spidhigh)

select

@charMaxLenLoginName =

convert( varchar

,isnull( max( datalength(loginname)) ,16)

)

,@charMaxLenDBName =

convert( varchar

,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,20)

)

,@charMaxLenCPUTime =

convert( varchar

,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,10)

)

,@charMaxLenCPUDelta =

convert( varchar

,isnull( max( datalength( rtrim(convert(varchar(128),cpu_delta)))) ,10)

)

,@charMaxLenDiskIO =

convert( varchar

,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)

)

,@charMaxLenCommand =

convert( varchar

,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)

)

,@charMaxLenHostName =

convert( varchar

,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,16)

)

,@charMaxLenProgramName =

convert( varchar

,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)

)

,@charMaxLenLastBatch =

convert( varchar

,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9)

)

from

#tb1_sysprocesses

where

spid >= @spidlow

and spid <= @spidhigh

-- Output the report.

set @command = '

set nocount off

select

SPID = convert(char(5),spid)

,Status =

CASE lower(status)

When ''sleeping'' Then lower(status)

Else upper(status)

END

,Login = substring(loginname,1,' + @charMaxLenLoginName + ')

,HostName =

CASE hostname

When Null Then '' .''

When '' '' Then '' .''

Else substring(hostname,1,' + @charMaxLenHostName + ')

END

,BlkBy =

CASE isnull(convert(char(5),blocked),''0'')

When ''0'' Then '' .''

Else isnull(convert(char(5),blocked),''0'')

END

,DBName = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')

,Command = substring(cmd,1,' + @charMaxLenCommand + ')

,CPU_Total = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')

,CPU_Delta = substring(convert(varchar,cpu_delta),1,' + @charMaxLenCPUDelta + ')

,DiskIO = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')

,LastBatch = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')

,ProgramName = substring(program_name,1,' + @charMaxLenProgramName + ')

,SPID = convert(char(5),spid) -- Handy extra for right-scrolling users.

from

#tb1_sysprocesses

where spid > 50 -- filter out system spids

and spid <> @@spid -- and current process spid

and spid >= ' + @charspidlow + '

and spid <= ' + @charspidhigh + '

'

if @seldbid > 0

set @command = @command +

'

and dbid = ' + @seldbid + '

'

set @command = @command +

' order by cast(cpu_delta as int) desc, cast(cpu as int) desc

set nocount on

'

exec (@command)

LABEL_RETURN:

if object_id('tempdb.dbo.#tb1_sysprocesses') is not null drop table #tb1_sysprocesses

if object_id('tempdb.dbo.#cpu1') is not null drop table #cpu1

if object_id('tempdb.dbo.#cpu2') is not null drop table #cpu2

return @retcode -- sp_whocpu

go

if exists (select * from sysobjects

where id = object_id('dbo.sp_whocpu')

and sysstat & 0xf = 4)

grant exec on dbo.sp_whocpu to public

go