June 28, 2015 at 4:30 am
Dear Experts.
I'm trying to figure out why an sql query is being blocked .
when using the script here :
I find
58XTZ-RATIS.Net SqlClient Data ProviderrunningNULLNULL063527DB2INSERTNULL63527021669062095671
68XTZ-RATIS.Net SqlClient Data ProviderrunningNULLNULL07747DB1UPDATEPREEMPTIVE_OLEDBOPS77471382980146187417
(i changed the db names due to privacy aspects).
I can see that the blocking is stated as NULL
and that that the sessions ID are blocked_by 0.
if I take the text on session 58 and try to run it in a query window it runs just fine without hanging.
and advice would be much appreciated on how to figure out the real cause for this block.
Thanks.
June 30, 2015 at 2:17 am
Hi,
I use the following query to display blocked sessions including the head blocking process.
select
SPID
, sp.Status
, DB_NAME(dbid) as [database]
, (select TEXT from sys.dm_exec_sql_text(sp.sql_handle)) as last_cmd
, open_tran
, blocked
, case when waittime > 300000 then cast(waittime/1000/60 as varchar(10)) + ' min'
when waittime > 3000 then cast(waittime/1000.0 as varchar(10)) + ' sec'
else cast(waittime as varchar(10)) + ' ms'
end as waittime
, lastwaittype
, Loginame
, Hostname
, program_name
from sys.sysprocesses sp
left outer join sys.dm_exec_requests der
on sp.spid = der.session_id
where SPID > 50
and SPID <> @@SPID-- exclude this current connection
/** display blocking en blocked processes **/
and (blocked > 0
or
SPID in (select blocked from sys.sysprocesses where blocked > 0)
)
order by blocked
The first row in the result will display the headblocking process. The columnm The query text of all rows will give you an indication about the object it is using and that (most likely) is the source of the blocking. It also states the wait type that will give you an indication of why the process (especially the head blocking process) is running so long.
Keep in mind that locking and blocking is normal in a RDBS to preserve data integrity. In a busy OLTP system you could see frequent (but very short!) blocked actions. However blocking can become in issue when a lock is taken for a long time. Therefor it is advised to keep each action as short as possible.
June 30, 2015 at 2:43 am
Thank you very much for you reply.
I'll try to give it a try next time a lock / block occurs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply