Using Trace Flag 2861

[talenx]

Hello all,
I have been attempting to create a procedure that will monitor a SQL server for block processes. if found I need it to log specific information about the process ie spid , blocking spid login time ect... but the main thing I need is the one thing that I am having trouble with, and that the SQL that was executed or executing. I am amazed that MS hasn't come up with a tool to do this.. but I digress.

The process that I am using seem to be pretty straight forward( its triggered by a scheduled job to run every 10 min). I check the sysprocesses table for any spids that are blocking ( but not blocking themselves) if found i dump all spids and detail into a temp table then loop through each spid that is blocking and attempt to grab the sql_handle (text) using the fn_get_sql() function. unfortunatly two things happen
1. When using the fn_get_sql() outside of the loop , and specifying a spid the results null ( reason... TRACE FLAG 2861) which turns out pretty much has NO documentation. From what I have found it looks like it enables SQL to store zero-cost procedures. Which would allow you to grab a once very volatile handle. in addtion it bloats you sysprocedurecache table and can lead to performance degration. (this of course is a huge issue)
2. And this may just be a coding thing but when the fn_get_sql() is used within then loop it returns the same execution( which it's usually it's self.)

Basically my question is does anyone have any ideas as how to track the SQL text that is executing or executed from a spid that is blocking.

BTW here is the code

SET NOCOUNT ON
--check for orfan records if found flag the record as cleared ( seeing that the block has been removed.
IF EXISTS(SELECT SPID FROM DBAdmin.dbo.tProcessLog WITH (NOLOCK) WHERE CLEARED_FLAG = 0)
	BEGIN
	UPDATE a1
SET CLEARED_FLAG = 1, CLEARED_TIME = GETDATE() FROM master..SysProcesses a0 
LEFT JOIN DBAdmin.dbo.tProcessLog a1
ON  
a0.spid = a1.SPID and a0.blocked = a1.SPID_BLOCKING
WHERE a1.CLEARED_FLAG = 0  AND a0.SPID IS NULL
	END


--check for blocks
IF EXISTS(SELECT SPID FROM master..SysProcesses WITH (NOLOCK)WHERE BLOCKED > 0)
	--start process
	BEGIN	
	--insert new log drop old temp and build spid list
	DROP TABLE ##TEMPLOG001
	SELECT SPID AS BLOCKED_BY , 0 AS USED INTO ##TEMPLOG001 FROM master..SysProcesses WITH (NOLOCK)

--flush through all blocking spids and return FULL SQL for each spid.
		WHILE exists(SELECT BLOCKED_BY FROM ##TEMPLOG001 WHERE USED = 0)
		BEGIN
		DECLARE @BLOCKINGSPID AS INT
		DECLARE @SPIDTSQL AS VARCHAR (8000)

		SELECT TOP 1 @BLOCKINGSPID = BLOCKED_BY FROM ##TEMPLOG001 WHERE USED = 0
	--get FULL sql from handle
		DECLARE @Handle BINARY(20)
		SELECT @Handle = sql_handle FROM master..SysProcesses WHERE SPID = @BLOCKINGSPID
	--apply sql handle to var
		SELECT @SPIDTSQL = text FROM ::fn_get_sql(@handle)
	--update temp table to reflect used spid
		UPDATE ##TEMPLOG001 SET USED = 1 WHERE BLOCKED_BY = @BLOCKINGSPID

--insert logging data
INSERT INTO DBAdmin.dbo.tProcessLog(SPID, SPID_BLOCKING, HOST_NAME, LOGIN_TIME, NUMBER_OF_THREADS, STATUS, ENTRY_TIME, WAIT_TYPE, WAIT_TIME, SQL_TEXT, CMD)
SELECT spid, blocked, hostname, login_time, COUNT(ecid), status, GETDATE(), waittype, waittime, @SPIDTSQL ,cmd
FROM MASTER..SysProcesses with (nolock)
WHERE SPID = @BLOCKINGSPID
GROUP BY spid, blocked, hostname, login_time, status, waittype, waittime, cmd
		END

Any thoughts?

Thanks

TalenX

 

[mrdenny]

I use this code to pull blocking code.

create table #sqltext
(spid int,
hostname varchar(255),
nt_username varchar(255),
loginame varchar(255),
input_buffer varchar(255),
blocked int,
sql text,
sqlhandle binary(20),
login_time datetime,
last_batch datetime)

create table #inputbuffer
(eventtype varchar(255),
parms varchar(255),
sql varchar(255))

create table #sql
(sql text)

declare @spid int
declare @hostname varchar(255)
declare @nt_username varchar(255)
declare @loginame varchar(255)
declare @input_buffer varchar(255)
declare @sql_handle binary(20)
declare @blocked int
declare @login_time datetime
declare @last_batch datetime
declare @sql varchar(8000)

declare cur CURSOR STATIC for select spid, hostname, nt_username, loginame, sql_handle, blocked, login_time, last_batch from sysprocesses order by Blocked DESC
open cur
fetch next from cur into @spid, @hostname, @nt_username, @loginame, @sql_handle, @blocked, @login_time, @last_batch
while @@FETCH_STATUS = 0
BEGIN
truncate table #inputbuffer
truncate table #sql

insert into #inputbuffer
exec usp_InputBuffer @spid

set @input_buffer = (select sql from #inputbuffer)

insert into #SQL
SELECT [text]
FROM ::fn_get_sql(@SQL_Handle)

if exists (select * from #sql)
set @sql = (select convert(varchar(8000), sql) from #sql)
else
BEGIN
set @sql = null
set @sql_handle = null
END

insert into #sqltext
(spid, hostname, nt_username, loginame, blocked, sqlhandle, sql, input_buffer, login_time, last_batch)
values
(@spid, @hostname, @nt_username, @loginame, @blocked, @sql_handle, @sql, @input_buffer, @login_time, @last_batch)


fetch next from cur into @spid, @hostname, @nt_username, @loginame, @sql_handle, @blocked, @login_time, @last_batch
END
close cur
deallocate cur

insert into sysprocess_monitoring
select getdate(), *
from #sqltext

drop table #sqltext
drop table #inputbuffer
drop table #sql

This actually pulls all the spids (you'd have to modify the query to only pull the blocking spids).

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[talenx]

Hi mrdenny,
First off, thank you for you response.

I noticed that you also pull the SQL string from the fn_get_sql() function as well... from what I have read about (and experience) is that it may not always return the SQL string.. Example if the process in it execution or has a cost of zero. And the only way to get around that is to enable the trace flag 2861. Yet it forces SQL to store all query plan even if they have a cost of zero.. Which I find that’s fine.. I really don’t care about those... Though most of the time if a query is currently running I can grab that handle.. The reason to me is still unknown... though when i enable that flag I am then able to grab the handle.)
I need the ability to grab that handle at any given time but also don't want to hinder performance because SQL is now storing every SQL pass that is getting executed and bloating the sysproccache table.

Any thoughts?

Thanks
TalenX

 

[mrdenny]

From what I've seen the as long as the query is still executing you will get the full command fro mthe fn_get_sql(). However if the command has completed that's when it will return null data.

You'll notice that I account for this within my code.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com