-- Stored Procedure: dbo.ActiveSpids
-- Bill Wunder

use admin
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[ActiveSpids]  
AS
/*******************************************************************************************************
*	admin.dbo.ActiveSpids 
*	Creator:       Bill Wunder
*	Date:          7-16-2003
*
*	Project:       
*	Project Mgr:   
*	Dev Contact:   
*
*	Description:   track cpu and io usage and blocking of currently running spids
*	Notes:         should work even if multiple users are executing (but not to many)
*
*	Usage:
	     EXECUTE admin.dbo.ActiveSpids
*
*
*	Modifications:   
*          Developer Name     Date     Brief Description
*          ------------------ -------- ------------------------------------------------------------
*          bw                 09-02-03 add fn_get_sql for blocker            
*          bw                 04-12-04 change blocker dump to GetSpid call 
*          bw                 07-14-04 add spids with an open_tran to "runnable" and edit output header 
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------

---------------------------------------------
-- create temp tables
---------------------------------------------

---------------------------------------------
-- set session variables
---------------------------------------------
declare @blocker int
 , @spid int
 , @handle binary(20)
 , @start int
 , @end int

set nocount on
SET ANSI_WARNINGS OFF
SET ARITHIGNORE ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 


-- use a permanent table so multiple users can watch at the same time
if object_id('dbo.RunningSPIDs','U') is null
 begin
  create table dbo.RunningSPIDs
			(spid smallint
				, host varchar(15)
				, cpu int
				, io bigint
				, lastcpu int
				, lastio bigint
				, start varchar(20)
				, app varchar(35)
				, recdt datetime
				, lastrecdt datetime
				, op varchar(20)
				, open_tran smallint)
	end

delete r
from dbo.RunningSPIDs r
left join master.dbo.sysprocesses p with(nolock)
on p.spid = r.spid
where cast(p.last_batch as varchar(20)) <> r.start
or p.spid is null
or p.status <> 'runnable' 

update r
	set cpu = p.cpu
		, io = cast(p.physical_io as int)
		, lastcpu = r.cpu
		, lastio = r.io
		, recdt = getdate()
		, lastrecdt = r.recdt
		, open_tran = p.open_tran
from master.dbo.sysprocesses p with(nolock)
left join msdb.dbo.sysjobs j with(nolock)
on substring(p.program_name,charindex('0x', p.program_name) + 18, 16)
   = substring(replace(j.job_id, '-',''),17,16) 
join dbo.RunningSPIDs r 
on p.spid = r.spid
where (p.status='runnable' 
	or p.spid in (Select blocked from master.dbo.sysprocesses with(nolock) where blocked <> 0 or open_tran > 0))
and p.spid<>@@spid
and r.app = case when p.program_name like 'SQLAgent - TSQL JobStep%'
		then 'Job: ' + substring(j.name,1,30)
		else substring(p.program_name,1,35)
		end 

insert dbo.RunningSPIDs 
select p.spid
	, substring(p.hostname,1,15)
	, p.cpu
	, cast(p.physical_io as int)
	, null
	, null
	, cast(p.last_batch as varchar(20))
	, case when p.program_name like 'SQLAgent - TSQL JobStep%'
		then 'Job: ' + substring(j.name,1,30)
		else substring(p.program_name,1,35)
		end
	, getdate()
	, null
	, substring(replace(replace(replace(p.cmd,char(13),char(32)),char(10),char(32)),char(9), char(32)),1,20)
	, p.open_tran
from master.dbo.sysprocesses p with(nolock)
left join msdb.dbo.sysjobs j with(nolock)
on substring(p.program_name,charindex('0x', p.program_name) + 18, 16)
   = substring(replace(j.job_id, '-',''),17,16) 
where (p.status='runnable' 
	or p.blocked <> 0 
 or p.spid in (Select blocked from master.dbo.sysprocesses with(nolock) where blocked <> 0)
 or p.open_tran > 0)
and p.spid<>@@spid
and not exists (select 1 from dbo.RunningSPIDs with(nolock) where spid = p.spid)

print 'Active SPIDs ordered by CPU usage' 
print '  "Active" may mean spid is executing, blocked, or has an open transaction'
print '  [cur cpu] and [cur io] in last [cur dur] (current duration) seconds - rerun proc to refresh.'
print '  see also cumulative cpu & i/o since batch started to right in data row          -->'
print '  If [cur dur] and batch started not changing check for open transaction to right -->'
print ''

select spid
	, cpu-isnull(lastcpu,cpu) [cur cpu]
	, io-isnull(lastio,io) [cur io]
	, datediff(second,isnull(lastrecdt,recdt),recdt) [cur dur]
	, start [batch started]
	, host
	, op
	, app 
	, open_tran
	, cpu [tot cpu]
	, io [tot io]
from dbo.RunningSPIDs with(nolock)
order by cpu desc


if exists(select 1 from master.dbo.sysprocesses with(nolock) where blocked <> 0)
	begin
		print 'Blocking and Blocked SPIDs'
		print ''
		select p.spid,
			p.blocked [Blocker],
			p.waittime,
			cast(p.lastwaittype as varchar(20)) [lastwaittype],
			cast(rtrim(ltrim(p.waitresource)) as varchar(20)) [waitresource],
			cast(p.last_batch as varchar(20)) as [last batch],
			substring(p.hostname,1,15) as [Host Name],
			substring(replace(replace(replace(p.cmd,char(13),char(32)),char(10),char(32)),char(9), char(32)),1,20) as [op],
			case when p.program_name like 'SQLAgent - TSQL JobStep%'
				then 'Job: ' + substring(j.name,1,20)
				else substring(p.program_name,1,25)
				end as [Application Name]
			from master.dbo.sysprocesses p with(nolock)
			left join msdb.dbo.sysjobs j with(nolock)
			on substring(p.program_name,charindex('0x', p.program_name) + 18, 16)
			   = substring(replace(j.job_id, '-',''),17,16) 
			where p.spid<>@@spid
			and (p.blocked <> 0
			     or p.spid in (select blocked 
                						from master.dbo.sysprocesses with(nolock)
                						where blocked <> 0))
			order by blocked, p.last_batch
                 
  select @blocker = min(blocked) 
		from master.dbo.sysprocesses with(nolock)
		where blocked > 0
                
  while @blocker is not null
   begin
			 print 'Additional Info on Blocking SPID ' + cast(@blocker as varchar(10))
			 print ''

    exec admin.dbo.GetSpid @Blocker

    select @blocker = min(blocked) 
		  from master.dbo.sysprocesses with(nolock)
		  where blocked > @blocker
 	 end
 end
RETURN

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO