sysprocesses and blocking

Question

sysprocesses and blocking

dallas13

SSCertifiable

Points: 7491
More actions
July 17, 2008 at 11:39 pm

#195508

SELECTp1.spid,
p1.blocked,
p1.cmd,
p1.sql_handle,
p1.stmt_start/2 AS CodeStart,
CASE WHEN p1.stmt_end = -1 THEN -1
ELSE p1.stmt_end/2 END AS CodeEnd,
p1.DBID
INTO #Block
from sys.sysprocesses p1
where blocked<>0
and spid >51
After running this query I m getting this main columns(not all included here)
SpidBlockedCmd
5453Update
These are the few columns of sysprocesses table.
Right now I can see that 54 is blocked by 53.
I can see that 54 is updating.
But I have 1000s of blocking and I want to create an automated script which catch this information.
I want to make an automated script which understands that 53 is the culprit for 54 and gives details of what command 53 is running?
So I want the cmd,and sqL_handle(statement) information for column 1 and column 2 both.
we can use sysprocesses table and make some join but I m not sure how it works?
Thanks and I really appreciate ur help

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

For SQL 2005, try this one. Create a table somewhere then have a SQL agent job doing the insert at whatever interval you like

SELECT er.session_id, blocking_session_id, wait_type, wait_time, wait_resource, database_id, login_time,

login_name, command, st.text

FROM sys.dm_exec_requests er INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id

CROSS APPLY sys.dm_exec_sql_text(sql_handle) st

WHERE blocking_session_id >0

UNION ALL

SELECT er.session_id, blocking_session_id, wait_type, wait_time, wait_resource, database_id, login_time,

login_name, command, st.text

FROM sys.dm_exec_requests er INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id

CROSS APPLY sys.dm_exec_sql_text(sql_handle) st

WHERE er.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id >0)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dallas13 SSCertifiable Points: 7491 More actions · Answer 2

WEll thanks Gail

But

I want to capture blocking_session_id's commands and from where these commands come from?

So after executing your code, I want to modify this code so that i can get blocking spid's info,

DECLARE c_Block CURSOR FAST_FORWARD FOR

SELECTSpid,

Cmd,

sql_handle,

CodeStart,

CodeEnd,

DBID

FROM#Block

OPEN c_Block

FETCH c_Block INTO @Spid, @Cmd, @sql_handle-2, @Start, @End, @DBID

WHILE @@FETCH_STATUS = 0 BEGIN

INSERT INTO test(SnapshotDate, Spid, Cmd, Statement, BlockingCode, DBID)

SELECT@SnapshotDate,

@Spid,

@Cmd,

Text,

SUBSTRING( text, COALESCE(NULLIF(@Start, 0), 1), CASE @End

WHEN -1

THEN DATALENGTH(text)

ELSE

(@End - @Start)

END ),

@DBID

FROM::fn_get_sql (@sql_handle)

FETCH c_Blockers INTO @Spid, @Cmd, @sql_handle-2, @Start, @End, @DBID

END

CLOSE c_Blockers

DEALLOCATE c_Blockers

END

so that i can see snapshot of actual statements and from which SP they are coming from. because i have llot of blockings happens during particular job

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

roadtrain64 (7/18/2008)
WEll thanks Gail
But
I want to capture blocking_session_id's commands and from where these commands come from?

Did you run my code? The query been run is already included in there.

I don't include the Statement_stat_offset and statement_end_offset columns, but they can be added and the substring that you';re doing in the cursor can be done in the query based of those two values and the st.text column

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dallas13 SSCertifiable Points: 7491 More actions · Answer 4

YA i ran ur code but i can get a query for blocked spid

i dont see a query for blocking spid. and that's what i want so that i can analyze later from that snapshot better.

hope u got what i want or ask me

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

The query being run by the processes is in the st.text column, and it's almost the same as what fn_getSQLHandle would return.

I've used this query for years to track blocking and the queries for blocking and blocked always appear in the expected column

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass