February 18, 2010 at 3:36 pm
Hi ,
Observed self- blocking (SPId) in sql server 2005 with SP3.
Killed the session and it went to hung state and as a result of it i had restarted the services.
Can any one let me know the reason for self blocking and how to overcome this situation going further.
February 18, 2010 at 4:14 pm
Did you kill your own SPID?
EnjoY!
February 18, 2010 at 4:19 pm
Hello,
Not my SPID.
Some other SPID which was blocking itself 😎
February 18, 2010 at 4:21 pm
It would either have been due to parallelism (wait type would have been cxpacket) or latchio waits. Tends to be prevalent on heavy IO processes (such as reindexes)
Its not a block in the true sense of the word more a change in behaviour from I think SQL 2000 SP3 onwards.
If the CPU and io used by the spid was still going up everything was actually fine.
If it was parallelism you could try the maxdop = 1 hint or increase the threshold for parallelism for the server, I give no guarantees that is a good thing to do in your instance though
---------------------------------------------------------------------
February 18, 2010 at 6:59 pm
Self blocking is ok. There's an explanation somewhere if you search for it.
February 18, 2010 at 9:47 pm
am with George...
I was in a similar scenario...I found out the wait type of blocked process was cxpackets..
Use this query to filter out the blocked process and check the waittype...
select swt.session_id,
swt.blocking_session_id,
(
select TEXT from sys.sysprocesses cross APPLY
sys.dm_exec_sql_text(sql_handle)
where spid=swt.session_id
) As Blocked_Query,
(
select TEXT from sys.sysprocesses cross APPLY
sys.dm_exec_sql_text(sql_handle)
where spid=swt.blocking_session_id
) As Blocking_Query,
swt.wait_duration_ms,
swt.wait_type,
swt.resource_description,
ss.status,
ss.cpu_time,
ss.memory_usage,
ss.total_elapsed_time,
ss.is_user_process,
ss.reads,
ss.writes,
ss.logical_reads
from sys.dm_os_waiting_tasks swt
inner join
sys.dm_exec_sessions ss
on
swt.session_id=ss.session_id
and swt.blocking_session_id is not null
Regards,
[font="Verdana"]Sqlfrenzy[/font]
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply