process is blocking itself

8 Jun 2006 7:54 PM

TSQL

I have a stored proc created for report and now suddenly the report is taking
lot of time to run. The store proc is having simple select statement with
multiple case statements.

I checked the sysprocesses table and noticed that the process is blocking
itself.

What could be reason and how to solve this issue??

Thanks

8 Jun 2006 8:04 PM

Roy Harvey

So you are saying that when you run sp_who the spid that appears in
the "blk" column is the SAME as the value in the "spid" column of the
same line?

Roy Harvey
Beacon Falls, CT

On Thu, 8 Jun 2006 12:54:02 -0700, TSQL
<T***@discussions.microsoft.com> wrote:

Show quote

>I have a stored proc created for report and now suddenly the report is taking
>lot of time to run. The store proc is having simple select statement with
>multiple case statements.
>
>I checked the sysprocesses table and noticed that the process is blocking
>itself.
>
>What could be reason and how to solve this issue??
>
>Thanks

8 Jun 2006 8:25 PM

TSQL

Yes Exactly.
I ran -

select * from sysprocesses
where physical_io>25 or cpu>15 or memusage>15
order by blocked desc

Show quote

"Roy Harvey" wrote:

> So you are saying that when you run sp_who the spid that appears in
> the "blk" column is the SAME as the value in the "spid" column of the
> same line?
>
> Roy Harvey
> Beacon Falls, CT
>
>
> On Thu, 8 Jun 2006 12:54:02 -0700, TSQL
> <T***@discussions.microsoft.com> wrote:
>
> >I have a stored proc created for report and now suddenly the report is taking
> >lot of time to run. The store proc is having simple select statement with
> >multiple case statements.
> >
> >I checked the sysprocesses table and noticed that the process is blocking
> >itself.
> >
> >What could be reason and how to solve this issue??
> >
> >Thanks
>

9 Jun 2006 2:16 AM

Omnibuzz

seems wierd. Try it with
OPYION (MAXDOP 1) and
and recompile the stored procedure.
Can you post the script?

--
-Omnibuzz (The SQL GC)

http://omnibuzz-sql.blogspot.com/

9 Jun 2006 3:10 AM

Mike Hodgson

SQL 2000 latches quite often block other latch request from the same
SPID by design (which Tracy also acknowledged; see
http://support.microsoft.com/default.aspx/kb/906344). With SP4
Microsoft have started displaying those latch blocks (in addition to the
lock blocks) in the blocked column of sysprocesses, so now it looks like
a SPID is blocking itself (which, I guess, technically, it is) but in
fact it's usually just waiting on a page to be read into memory due to
slow I/O.

--
*mike hodgson*
http://sqlnerd.blogspot.com

Omnibuzz wrote:

Show quote

>seems wierd. Try it with
>OPYION (MAXDOP 1) and
>and recompile the stored procedure.
>Can you post the script?
>
>

9 Jun 2006 6:59 AM

Omnibuzz

Thanks Mike, Tracy. Went through the article. Makes sense. Makes a lot of
sense.

--
-Omnibuzz (The SQL GC)

http://omnibuzz-sql.blogspot.com/

8 Jun 2006 8:09 PM

Tracy McKibben

You're running SQL 2000 with SP4 installed:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;906344

TSQL wrote:
Show quote

> I have a stored proc created for report and now suddenly the report is taking
> lot of time to run. The store proc is having simple select statement with
> multiple case statements.
>
> I checked the sysprocesses table and noticed that the process is blocking
> itself.
>
> What could be reason and how to solve this issue??
>
> Thanks