Blocking

[Hal]

I am experiencing blocking problems on SQL Server 2000, SP3a. I have
read the posts and set up a job SQL agent to report on these
occurences I save the results to a table before executing an sp to
kill the offending process id. I am puzzled as to how a process that
does no updating can be guilty of blocking. Typically, I think what
is happening is a process that does no updates is blocking processes
that are trying to do updates. Can someone explain this to me? What
is the best way for me to resolve this?

Thanks!

[Erland Sommarskog]

Re: Blocking

Hal (hforman1@cfl.r r.com) writes:[color=blue]
> I am experiencing blocking problems on SQL Server 2000, SP3a. I have
> read the posts and set up a job SQL agent to report on these
> occurences I save the results to a table before executing an sp to
> kill the offending process id. I am puzzled as to how a process that
> does no updating can be guilty of blocking. Typically, I think what
> is happening is a process that does no updates is blocking processes
> that are trying to do updates. Can someone explain this to me? What
> is the best way for me to resolve this?[/color]

The fact that a reader can block a writer is not strange at all. Say
that you have a report function that performs some serious calculation
and has to scans several big tables. You can get big blocking issues
in this way.

What is the best in your case, is difficult to tell since I don't
know about the blocking situation. If indeed the problem is with
reports, when having a dedicated report server may be an idea.

But it could also be as simple that a few indexes can speed up the
blocking queries.

The action anyway is to get more information about the blocking queries,
and analyse them.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp