Blocking Question

I am beginning to see blocked and blocking processes in
Lock / Process ID tab under the current activity window.
SQL Server resolves this after a while but is this
normal ' If not how can I resolve the blocking problems '
Database is a highly transactional ~500 user database.
Thanks for any feedback.Blocking is OK, as long as there's no dead lock. If one process opens a
transaction and then the second process try to access the same tables
used by the first one, the second process will be blocked. It will just
sit there and wait until the first process finish its job (commit or
rollback), so it's normal.
--
Eric Li
SQL DBA
MCDBA
Nick wrote:
> I am beginning to see blocked and blocking processes in
> Lock / Process ID tab under the current activity window.
> SQL Server resolves this after a while but is this
> normal ' If not how can I resolve the blocking problems '
> Database is a highly transactional ~500 user database.
> Thanks for any feedback.|||Just to add a little to Eric's answer. A little bit of blocking is normal
and can be OK as long as it doesn't wait too long. If your seeing a lot of
blocking you may need to tune some of your statements or indexes some.
--
Andrew J. Kelly
SQL Server MVP
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:eDAeTbPREHA.1048@.tk2msftngp13.phx.gbl...
> Blocking is OK, as long as there's no dead lock. If one process opens a
> transaction and then the second process try to access the same tables
> used by the first one, the second process will be blocked. It will just
> sit there and wait until the first process finish its job (commit or
> rollback), so it's normal.
> --
> Eric Li
> SQL DBA
> MCDBA
> Nick wrote:
> > I am beginning to see blocked and blocking processes in
> > Lock / Process ID tab under the current activity window.
> > SQL Server resolves this after a while but is this
> > normal ' If not how can I resolve the blocking problems '
> >
> > Database is a highly transactional ~500 user database.
> >
> > Thanks for any feedback.
>|||Hi,
Have a look into thebelow article which explains the strategies to reduce
blocking:-
http://www.sql-server-performance.com/sf_block_prevention.asp
Have a look into the below link as well to reduce locks
http://www.sql-server-performance.com/reducing_locks.asp
Thanks
Hari
MCDBA
"Nick" <anonymous@.discussions.microsoft.com> wrote in message
news:1491601c444f2$f95cf610$a001280a@.phx.gbl...
> I am beginning to see blocked and blocking processes in
> Lock / Process ID tab under the current activity window.
> SQL Server resolves this after a while but is this
> normal ' If not how can I resolve the blocking problems '
> Database is a highly transactional ~500 user database.
> Thanks for any feedback.|||Thanks to All.........
>--Original Message--
>I am beginning to see blocked and blocking processes in
>Lock / Process ID tab under the current activity window.
>SQL Server resolves this after a while but is this
>normal ' If not how can I resolve the blocking
problems '
>Database is a highly transactional ~500 user database.
>Thanks for any feedback.
>.
>

Blocking processes

In sql2005 database it happenings that 2 processes lock different tables and
each process wait for the other to finish - or something similar.
The result is that application waits and nothing works.
I heard that SQL2005 automatically handles this situations and kill process
who did less work. Is there some setting?
Regards,SAre you describing a deadlock scenario? SQL Server has had deadlock detectio
n and resolution since
version 1.0 (although the internal algorithms has changed over the versions)
. I suggest that you
start reading up on "deadlock" (Books Online, Google etc) to see if this is
what you refer to.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:uWwj27UWGHA.1084@.TK2MSFTNGP04.phx.gbl...
> In sql2005 database it happenings that 2 processes lock different tables a
nd each process wait for
> the other to finish - or something similar.
> The result is that application waits and nothing works.
> I heard that SQL2005 automatically handles this situations and kill proces
s who did less work. Is
> there some setting?
> Regards,S
>|||Well I have locked processes, which never finishes.
But as I heared, the SQL2005 now has automatic detection and kill the
process who has done less job until deadlock.
What can I do?
It's not mine application so I don't know exactly what is happening.
When I kill process manually, than everything works.
It's happening about once a w.
Regards,Simon
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OvNnuBVWGHA.2080@.TK2MSFTNGP05.phx.gbl...
> Are you describing a deadlock scenario? SQL Server has had deadlock
> detection and resolution since version 1.0 (although the internal
> algorithms has changed over the versions). I suggest that you start
> reading up on "deadlock" (Books Online, Google etc) to see if this is what
> you refer to.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "simonZ" <simon.zupan@.studio-moderna.com> wrote in message
> news:uWwj27UWGHA.1084@.TK2MSFTNGP04.phx.gbl...
>|||> Well I have locked processes, which never finishes.
Than you don't have a deadlock, you just have a blocking scenario. Unless yo
u have a deadlock that
haven't been detected by SQL Server, which would be considered a bug. If it
is a blocking scenario,
you have to find who is blocking the others, and why that transaction doesn'
t finish.

> It's not mine application so I don't know exactly what is happening.
Blocking and deadlock problems is an application problem, so you need to tal
k to the application
vendor about this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:OO$MWAYWGHA.4212@.TK2MSFTNGP02.phx.gbl...
> Well I have locked processes, which never finishes.
> But as I heared, the SQL2005 now has automatic detection and kill the proc
ess who has done less
> job until deadlock.
> What can I do?
> It's not mine application so I don't know exactly what is happening.
> When I kill process manually, than everything works.
> It's happening about once a w.
> Regards,Simon
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OvNnuBVWGHA.2080@.TK2MSFTNGP05.phx.gbl...
>