Blocking Processes

I need some help with a problem that is growing worse. We are experiencing a problem with our database where it stops responding due to a process that is blocking other processes from executing. We had thought these were deadlocks but we could see that
SQL Server was handling some deadlocks. I ran a trace this morning in SQL Profiler that was supposed to show Deadlock and DeadlockChain events but when the server stopped responding and I had to go kill another "blocking" process, the trace showed nothin
g. Also, the blocking process often shows the query it is trying to execute is a select statement. I don't understand how a select statement can contribute to a deadlock. So that has me wondering now if the problem is not a deadlock but something else.
When EM shows a process is blocking other processes, what is that problem and how do I solve it? This is getting serious for us. Any help would be greatly appreciated.
TIA,
Darwin
The following should help you get starting on addressing the
issues:
INF: Understanding and Resolving SQL Server 7.0 or 2000
Blocking Problems
http://support.microsoft.com/?id=224453
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
-Sue
On Thu, 1 Apr 2004 12:06:22 -0800, Darwin
<dsass(removethis)@.browz.com> wrote:

>I need some help with a problem that is growing worse. We are experiencing a problem with our database where it stops responding due to a process that is blocking other processes from executing. We had thought these were deadlocks but we could see that
SQL Server was handling some deadlocks. I ran a trace this morning in SQL Profiler that was supposed to show Deadlock and DeadlockChain events but when the server stopped responding and I had to go kill another "blocking" process, the trace showed nothi
ng. Also, the blocking process often shows the query it is trying to execute is a select statement. I don't understand how a select statement can contribute to a deadlock. So that has me wondering now if the problem is not a deadlock but something else
. When EM shows a process is blocking other processes, what is that problem and how do I solve it? This is getting serious for us. Any help would be greatly appreciated.
>TIA,
>Darwin

Blocking Processes

I need some help with a problem that is growing worse. We are experiencing
a problem with our database where it stops responding due to a process that
is blocking other processes from executing. We had thought these were deadl
ocks but we could see that
SQL Server was handling some deadlocks. I ran a trace this morning in SQL P
rofiler that was supposed to show Deadlock and DeadlockChain events but when
the server stopped responding and I had to go kill another "blocking" proce
ss, the trace showed nothin
g. Also, the blocking process often shows the query it is trying to execute
is a select statement. I don't understand how a select statement can contr
ibute to a deadlock. So that has me wondering now if the problem is not a d
eadlock but something else.
When EM shows a process is blocking other processes, what is that problem an
d how do I solve it? This is getting serious for us. Any help would be gre
atly appreciated.
TIA,
DarwinThe following should help you get starting on addressing the
issues:
INF: Understanding and Resolving SQL Server 7.0 or 2000
Blocking Problems
http://support.microsoft.com/?id=224453
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
-Sue
On Thu, 1 Apr 2004 12:06:22 -0800, Darwin
<dsass(removethis)@.browz.com> wrote:

>I need some help with a problem that is growing worse. We are experiencing a probl
em with our database where it stops responding due to a process that is blocking oth
er processes from executing. We had thought these were deadlocks but we could see t
hat
SQL Server was handling some deadlocks. I ran a trace this morning in SQL P
rofiler that was supposed to show Deadlock and DeadlockChain events but when
the server stopped responding and I had to go kill another "blocking" proce
ss, the trace showed nothi
ng. Also, the blocking process often shows the query it is trying to execut
e is a select statement. I don't understand how a select statement can cont
ribute to a deadlock. So that has me wondering now if the problem is not a
deadlock but something else
. When EM shows a process is blocking other processes, what is that problem and how do I s
olve it? This is getting serious for us. Any help would be greatly appreciated.kred">
>TIA,
>Darwin

Blocking

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!Hal (hforman1@.cfl.rr.com) writes:
> 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?

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@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp