process is blocking itself

I have a stored proc created for report and now suddenly the report is takin
g
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'
ThanksSo 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
<TSQL@.discussions.microsoft.com> wrote:

>I have a stored proc created for report and now suddenly the report is taki
ng
>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|||You're running SQL 2000 with SP4 installed:
http://support.microsoft.com/defaul...KB;EN-US;906344
TSQL wrote:
> I have a stored proc created for report and now suddenly the report is tak
ing
> 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|||Yes Exactly.
I ran -
select * from sysprocesses
where physical_io>25 or cpu>15 or memusage>15
order by blocked desc
"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
> <TSQL@.discussions.microsoft.com> wrote:
>
>|||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/|||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:

>seems wierd. Try it with
>OPYION (MAXDOP 1) and
>and recompile the stored procedure.
>Can you post the script?
>|||Thanks Mike, Tracy. Went through the article. Makes sense. Makes a lot of
sense.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/