Blocks query does not give accurate process query
-
Hi,
I am using the below query to log blocks on my servers every minute. Often I get something like 'Create Procedure ...' for waiter_batch column.
I remember similar issue when using fn_get_sql in SQL 2000. Until I started to use it this way:
DECLARE @sql_handle binary(20)DECLARE @stmt_start Int, @stmt_end Int
DECLARE @spid smallint
SET @spid = 823
SELECT top 1 @sql_handle = sql_handle, @stmt_start = stmt_start, @stmt_end = stmt_end from master..sysprocesses (nolock)
WHERE spid = @spid
SELECT TOP 1 SUBSTRING(text, (@stmt_start + 2)/2,
CASE @stmt_end
WHEN -1 THEN (datalength(text))
ELSE (@stmt_end - @stmt_start +2)/2
END)
FROM ::fn_get_sql(@sql_handle)
instead of just
select * from FROM ::fn_get_sql(@sql_handle)I was getting the same 'create stored procedure ...'
So I was wondering if there was a more correct way of using the query below?
Thanks.
insert into BlocksLog
select t1.resource_type
,db_name(resource_database_id)
,t1.resource_associated_entity_id
,t1.request_mode-- lock requested
,t1.request_session_id -- spid of waiter
,t2.wait_duration_ms
,(select text from sys.dm_exec_requests as r --- get sql for waiter
cross apply sys.dm_exec_sql_text(r.sql_handle)
where r.session_id = t1.request_session_id)
,(select substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end - r.statement_start_offset)/2)
from sys.dm_exec_requests as r
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
where r.session_id = t1.request_session_id) --- this is the statement executing right now
,t2.blocking_session_id -- spid of blocker
,(select text from sys.sysprocesses as p--- get sql for blocker
cross apply sys.dm_exec_sql_text(p.sql_handle)
where p.spid = t2.blocking_session_id)
,getdate()
from
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
where
t1.lock_owner_address = t2.resource_address
-
Up! 🙂
-
Ok, will try to resurrect the topic again. It looks like the below query no longer works in SQL 2005. Is there a reason why? It looks like stmt_start and stmt_end columns have zero value most of the time. Are they getting deprecated or something? It worked fine in SQL 2000 and it was a more accurate method of getting the current query being executed by a spid and was suggested by Kalen Delaney in one of her articles in SQL Server magazine.
Any ideas?
DECLARE @sql_handle binary(20)DECLARE @stmt_start Int, @stmt_end Int
DECLARE @spid smallint
SET @spid = 823
SELECT top 1 @sql_handle = sql_handle, @stmt_start = stmt_start, @stmt_end = stmt_end from master..sysprocesses (nolock)
WHERE spid = @spid
SELECT TOP 1 SUBSTRING(text, (@stmt_start + 2)/2,
CASE @stmt_end
WHEN -1 THEN (datalength(text))
ELSE (@stmt_end - @stmt_start +2)/2
END)
FROM ::fn_get_sql(@sql_handle)
-
This is probably not the reason, but I've never been able to find an answer as to *which* sql handle you really need to be grabbing. (There are often multiple sql handles for 1 SPID) Whenever I see multiple, they are all 0 except for one. Not sure that is always the case, but that's what I've always seen.
One thing to at least investigate is whether or not your query is grabbing the correct one, by slightly altering it. Something like:
DECLARE @sql_handle binary(20)DECLARE @stmt_start Int, @stmt_end Int
DECLARE @spid smallint
SET @spid = 823
SELECT @sql_handle = sql_handle, @stmt_start = stmt_start, @stmt_end = stmt_end from master..sysprocesses (nolock)
WHERE spid = @spid AND sql_handle = (SELECT MAX(sql_handle) FROM master..sysprocesses WHERE spid = @spid)
--or
--SELECT top 1 @sql_handle = sql_handle, @stmt_start = stmt_start, @stmt_end = stmt_end from master..sysprocesses (nolock)
--WHERE spid = @spid
--ORDER BY sql_handle DESC --Or stmt_start DESC
SELECT TOP 1 SUBSTRING(text, (@stmt_start + 2)/2,
CASE @stmt_end
WHEN -1 THEN (datalength(text))
ELSE (@stmt_end - @stmt_start +2)/2
END)
FROM ::fn_get_sql(@sql_handle)
Another idea would be to log all the entries from the sysprocesses table for your spid at the time your query runs so you can go back through them later and see if anything stands out. If there's only 1 and it's still doing it, you can probably cross this off the list.
-
While playing with this some more, I've noticed that the stmt_start and end will be 0 if the status is sleeping. The entry is still there because the connection is still open, but it's not currently executing a statement, so you're only getting 1 letter of what executed. If it's not currently executing, I believe fn_get_sql is basically just DBCC INPUTBUFFER.
Try this code instead:
DECLARE @sql_handle binary(20)DECLARE @stmt_start Int, @stmt_end Int
DECLARE @spid smallint
SET @spid = 52
SELECT top 1 @sql_handle = sql_handle, @stmt_start = stmt_start, @stmt_end = stmt_end from master..sysprocesses (nolock)
WHERE spid = @spid
ORDER BY sql_handle DESC --Or stmt_start DESC
SELECT TOP 1 CASE WHEN @stmt_start > 0
THEN SUBSTRING(text, (@stmt_start + 2)/2,
CASE @stmt_end
WHEN -1 THEN (datalength(text))
ELSE (@stmt_end - @stmt_start +2)/2
END)
ELSE [Text]
END
FROM ::fn_get_sql(@sql_handle)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply