SPID blocking itself

01/04/2008, 02h02

What do I need to look at to find out why a query shows it is blocking
itself?
This is a SQL2000 sp4 server on Win2003 with a query on a table containing
~20million rows. Someone wrote a query w/i an application that ends up doing
a table scan, and when this occurs sp_who2 is showing the spid blocking
itself.

How do I start debugging this one?
Thanks

01/04/2008, 02h58

> How do I start debugging this one?

Self blocking spid's may be due to latch waits
(http://support.microsoft.com/kb/906344) or intra-query parallelism. In the
later case, you will have multiple rows for the same spid with the blocked
ones showing CXPACKET wait types.

--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"cw" <cw@3mc.com> wrote in message
news:eggVBv4kIHA.5160@TK2MSFTNGP05.phx.gbl...
> What do I need to look at to find out why a query shows it is blocking
> itself?
> This is a SQL2000 sp4 server on Win2003 with a query on a table containing
> ~20million rows. Someone wrote a query w/i an application that ends up
> doing a table scan, and when this occurs sp_who2 is showing the spid
> blocking itself.
>
> How do I start debugging this one?
> Thanks
>

01/04/2008, 13h18

Thanks! This is a big .
cw

"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:BF62AC85-58AA-46A2-9198-D4723626EDC5@microsoft.com...
>> How do I start debugging this one?
>
> Self blocking spid's may be due to latch waits
> (http://support.microsoft.com/kb/906344) or intra-query parallelism. In
> the later case, you will have multiple rows for the same spid with the
> blocked ones showing CXPACKET wait types.
>
> --
> Hope this s.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> "cw" <cw@3mc.com> wrote in message
> news:eggVBv4kIHA.5160@TK2MSFTNGP05.phx.gbl...
>> What do I need to look at to find out why a query shows it is blocking
>> itself?
>> This is a SQL2000 sp4 server on Win2003 with a query on a table
>> containing ~20million rows. Someone wrote a query w/i an application that
>> ends up doing a table scan, and when this occurs sp_who2 is showing the
>> spid blocking itself.
>>
>> How do I start debugging this one?
>> Thanks
>>
>

01/04/2008, 02h02	#1
cw Messages: n/a Hébergeur:	SPID blocking itself What do I need to look at to find out why a query shows it is blocking itself? This is a SQL2000 sp4 server on Win2003 with a query on a table containing ~20million rows. Someone wrote a query w/i an application that ends up doing a table scan, and when this occurs sp_who2 is showing the spid blocking itself. How do I start debugging this one? Thanks

01/04/2008, 02h58	#2
Dan Guzman Messages: n/a Hébergeur:	Re: SPID blocking itself > How do I start debugging this one? Self blocking spid's may be due to latch waits (http://support.microsoft.com/kb/906344) or intra-query parallelism. In the later case, you will have multiple rows for the same spid with the blocked ones showing CXPACKET wait types. -- Hope this s. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ "cw" <cw@3mc.com> wrote in message news:eggVBv4kIHA.5160@TK2MSFTNGP05.phx.gbl... > What do I need to look at to find out why a query shows it is blocking > itself? > This is a SQL2000 sp4 server on Win2003 with a query on a table containing > ~20million rows. Someone wrote a query w/i an application that ends up > doing a table scan, and when this occurs sp_who2 is showing the spid > blocking itself. > > How do I start debugging this one? > Thanks >

01/04/2008, 13h18	#3
cw Messages: n/a Hébergeur:	Re: SPID blocking itself Thanks! This is a big . cw "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:BF62AC85-58AA-46A2-9198-D4723626EDC5@microsoft.com... >> How do I start debugging this one? > > Self blocking spid's may be due to latch waits > (http://support.microsoft.com/kb/906344) or intra-query parallelism. In > the later case, you will have multiple rows for the same spid with the > blocked ones showing CXPACKET wait types. > > -- > Hope this s. > > Dan Guzman > SQL Server MVP > http://weblogs.sqlteam.com/dang/ > > "cw" <cw@3mc.com> wrote in message > news:eggVBv4kIHA.5160@TK2MSFTNGP05.phx.gbl... >> What do I need to look at to find out why a query shows it is blocking >> itself? >> This is a SQL2000 sp4 server on Win2003 with a query on a table >> containing ~20million rows. Someone wrote a query w/i an application that >> ends up doing a table scan, and when this occurs sp_who2 is showing the >> spid blocking itself. >> >> How do I start debugging this one? >> Thanks >> >

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email