quote:

---

Originally posted by nr

Add this sp to master
http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html

It will show you what is executing and what is blocking and the commands executed.
Check the diskio for things that are doing a lot o work.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---

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

Create procedure sp_nrInfo
@status varchar(20) = 'active' ,
@type   varchar(10) = 'info'
as
/*
exec sp_nrSpidByStatus			-- all spids whith status runnable
exec sp_nrSpidByStatus 'sleeping'	-- all spids whith status sleeping
exec sp_nrSpidByStatus 'background'	-- all spids whith status background
exec sp_nrSpidByStatus 'sleeping'	-- all spids whith status sleeping
exec sp_nrSpidByStatus 'all'		-- all spids
exec sp_nrSpidByStatus 'blk'		-- all blocked or blocking spids
exec sp_nrSpidByStatus '74'		-- an individual spid - also gives subthreads
select * from master..sysprocesses where spid = 56
*/
set transaction isolation level read uncommitted
set nocount on
-- Get blocking
declare @blkspid varchar(20)

select top 1 @blkspid = convert(varchar(20),spid) from master..sysprocesses 
where spid in (select blocked from master..sysprocesses)
order by blocked

if @blkspid is not null
begin
	-- this is all that will appear on the intranet
	select 	[blocking spid] = spid ,
		last_batch = last_batch ,
		hostname = left(hostname,14) ,
		program_name = left(program_name,20) ,
		cmd = cmd ,
		physical_io = physical_io
	from master..sysprocesses
	where spid = @blkspid
	
	exec ('dbcc inputbuffer (' + @blkspid + ')')
	
	print  'blocking spid command'
	DECLARE @sql_handle binary(20)
	SELECT	@sql_handle = sql_handle
			FROM master.dbo.sysprocesses
			WHERE	spid = @blkspid
				AND ecid = 0
  			SELECT *
   			FROM ::fn_get_sql(@sql_handle)
	
	print 'sp_who2 result for blocking spid'
	exec ('sp_who2 ' + @blkspid) 
	
	print 'spids that are being blocked'
	select spid, blocked from master..sysprocesses 
	where spid in (select blocked from master..sysprocesses)
	order by blocked
end

declare	@cmd varchar(1000)

declare @buf varchar(1000) ,
	@id int ,
	@spid int ,
	@maxSpid int
	create table #spid (spid int, command varchar(1000) null)
	create table #temp (x varchar(100), y int, s varchar(1000), id int identity (1,1))
	create table #spids (spid int)
	if isnumeric(@status) = 1
	begin
		insert #spids select @status
	end
	else if @status = 'blk'
	begin
		insert	#spids 
		select spid from master..sysprocesses where blocked <> 0
		union
		select blocked from master..sysprocesses where blocked <> 0
	end
	else if @status = 'active'
		insert	#spids 
		select 	distinct spid 
		from 	master..sysprocesses 
		where 	blocked <> 0
		or	upper(cmd)    not in 	(
				                     'AWAITING COMMAND'
				                    ,'MIRROR HANDLER'
				                    ,'LAZY WRITER'
				                    ,'CHECKPOINT SLEEP'
				                    ,'RA MANAGER'
						)
		or lower(status) <> 'sleeping'
	else
	begin
		insert	#spids select spid from master..sysprocesses where (status = @status or @status = 'all') and ecid = 0
	end
	select 	@spid = 0 ,
		@maxSpid = max(spid)
	from	#spids
	
	if @type = 'info'
	begin
		while @spid < @maxSpid
		begin
			select	@spid = min(spid) from #spids where spid > @spid
			
			select @cmd = 'dbcc inputbuffer (' + convert(varchar(10),@spid) + ')'
			
			delete #temp
			
			insert #temp
			exec (@cmd)
			
			select 	@id = 0 ,
				@buf = ''
			select @buf = @buf + replace(replace(s,char(10),'|'),char(13),'|')
	
			from #temp
	
			insert 	#spid
			select	@spid, @buf
		end
	end
	else
	begin
		insert	#spid select spid, '' from #spids
	end
	
	select 	spid 		= convert(varchar(4),#spid.spid) ,
		status 		= min(left(case when s.status = 'Runnable' then 'aRunnable' else 'z' + s.status end ,12)) ,
		threads		= count(*) ,
		loginame 	= min(left(s.loginame, 25)) ,
		hostname	= min(left (s.hostname, 14)) ,
		BlkBy		= max(case when s.blocked <> 0 then convert(varchar(3),s.blocked) else '   ' end) ,
		DBName		= left(db_name(min(dbid)), 10) ,
		command		= min(left(s.cmd, 30)) ,
		CPUTime		= sum(s.cpu) ,
		DiskIO		= sum(s.physical_io) ,
		LastBatch	= max(convert(varchar(23),s.last_batch,121)) ,
		ProgramName	= min(left(s.program_name, 30)) ,
		spid		= convert(varchar(4),#spid.spid) ,
		buffercmd	= max(#spid.command)
	from	#spid ,
		master..sysprocesses s
	where	s.spid = #spid.spid
	group by #spid.spid
	order by convert(int,#spid.spid) desc

	drop table #spid
	drop table #temp

go

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

Create procedure sp_nrInfo
@status varchar(20) = 'active' ,
@type   varchar(10) = 'info'
as
/*
exec sp_nrSpidByStatus			-- all spids whith status runnable
exec sp_nrSpidByStatus 'sleeping'	-- all spids whith status sleeping
exec sp_nrSpidByStatus 'background'	-- all spids whith status background
exec sp_nrSpidByStatus 'sleeping'	-- all spids whith status sleeping
exec sp_nrSpidByStatus 'all'		-- all spids
exec sp_nrSpidByStatus 'blk'		-- all blocked or blocking spids
exec sp_nrSpidByStatus '74'		-- an individual spid - also gives subthreads
select * from master..sysprocesses where spid = 56
*/
set transaction isolation level read uncommitted
set nocount on
-- Get blocking
declare @blkspid varchar(20)

select top 1 @blkspid = convert(varchar(20),spid) from master..sysprocesses 
where spid in (select blocked from master..sysprocesses)
order by blocked

if @blkspid is not null
begin
	-- this is all that will appear on the intranet
	select 	[blocking spid] = spid ,
		last_batch = last_batch ,
		hostname = left(hostname,14) ,
		program_name = left(program_name,20) ,
		cmd = cmd ,
		physical_io = physical_io
	from master..sysprocesses
	where spid = @blkspid
	
	exec ('dbcc inputbuffer (' + @blkspid + ')')
	
	print 'sp_who2 result for blocking spid'
	exec ('sp_who2 ' + @blkspid) 
	
	print 'spids that are being blocked'
	select spid, blocked from master..sysprocesses 
	where spid in (select blocked from master..sysprocesses)
	order by blocked
end

declare	@cmd varchar(1000)

declare @buf varchar(1000) ,
	@id int ,
	@spid int ,
	@maxSpid int
	create table #spid (spid int, command varchar(1000) null)
	create table #temp (x varchar(100), y int, s varchar(1000), id int identity (1,1))
	create table #spids (spid int)
	if isnumeric(@status) = 1
	begin
		insert #spids select @status
	end
	else if @status = 'blk'
	begin
		insert	#spids 
		select spid from master..sysprocesses where blocked <> 0
		union
		select blocked from master..sysprocesses where blocked <> 0
	end
	else if @status = 'active'
		insert	#spids 
		select 	distinct spid 
		from 	master..sysprocesses 
		where 	blocked <> 0
		or	upper(cmd)    not in 	(
				                     'AWAITING COMMAND'
				                    ,'MIRROR HANDLER'
				                    ,'LAZY WRITER'
				                    ,'CHECKPOINT SLEEP'
				                    ,'RA MANAGER'
						)
		or lower(status) <> 'sleeping'
	else
	begin
		insert	#spids select spid from master..sysprocesses where (status = @status or @status = 'all') and ecid = 0
	end
	select 	@spid = 0 ,
		@maxSpid = max(spid)
	from	#spids
	
	if @type = 'info'
	begin
		while @spid < @maxSpid
		begin
			select	@spid = min(spid) from #spids where spid > @spid
			
			select @cmd = 'dbcc inputbuffer (' + convert(varchar(10),@spid) + ')'
			
			delete #temp
			
			insert #temp
			exec (@cmd)
			
			select 	@id = 0 ,
				@buf = ''
			select @buf = @buf + replace(replace(s,char(10),'|'),char(13),'|')
	
			from #temp
	
			insert 	#spid
			select	@spid, @buf
		end
	end
	else
	begin
		insert	#spid select spid, '' from #spids
	end
	
	select 	spid 		= convert(varchar(4),#spid.spid) ,
		status 		= min(left(case when s.status = 'Runnable' then 'aRunnable' else 'z' + s.status end ,12)) ,
		threads		= count(*) ,
		loginame 	= min(left(s.loginame, 25)) ,
		hostname	= min(left (s.hostname, 14)) ,
		BlkBy		= max(case when s.blocked <> 0 then convert(varchar(3),s.blocked) else '   ' end) ,
		DBName		= left(db_name(min(dbid)), 10) ,
		command		= min(left(s.cmd, 30)) ,
		CPUTime		= sum(s.cpu) ,
		DiskIO		= sum(s.physical_io) ,
		LastBatch	= max(convert(varchar(23),s.last_batch,121)) ,
		ProgramName	= min(left(s.program_name, 30)) ,
		spid		= convert(varchar(4),#spid.spid) ,
		buffercmd	= max(#spid.command)
	from	#spid ,
		master..sysprocesses s
	where	s.spid = #spid.spid
	group by #spid.spid
	order by convert(int,#spid.spid) desc

	drop table #spid
	drop table #temp

go