deadlock search

I have a system with a lot of deadlock searches appearing in the log,
but they usually do not find any deadlocks.
message in the log:
End deadlock search 21022 ... a deadlock was not found.
How is deadlock search triggered? Is it configurable? I think
performance is degrade by the deadlock searches but I need to know a
little more about under what conditions they occur.
/StenSten
You can capture it on the client (on Error) or run SQL Server Profiler with
Lock:Deadlock event
"Sten" <stenperersejspam@.hotmail.com> wrote in message
news:1108560501.098795.130320@.z14g2000cwz.googlegroups.com...
> I have a system with a lot of deadlock searches appearing in the log,
> but they usually do not find any deadlocks.
> message in the log:
> End deadlock search 21022 ... a deadlock was not found.
> How is deadlock search triggered? Is it configurable? I think
> performance is degrade by the deadlock searches but I need to know a
> little more about under what conditions they occur.
> /Sten
>|||A deadlock search occurs 500 millis after any lock is denied... Deadlock
searches used to happen immediately on denial ( in SQL 6.5). But many of
those deadlock searches were un-necessary, since it is normal to be denied a
lock while someone is using a row for a short period of time, then they
release the lock and you go about your business... Yet we used to have to
pay for a deadlock search anyway... In recent releases MS delayed the
deadlock search until 500 millis after the lock is denied... THe thinking is
that during the normal case, you will be granted the lock in a very short
period of time, and the deadlock search can be avoided altogether... Also
this allows a single deadlock search the opportunity to search for deadlocks
for multiple users in a single search... All of this is an optimization in
recent SQL versions.
There may be some way to set the deadlock search timeout, but if so , I do
not know what it might be.( I don't think MS exposes that to us...)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sten" <stenperersejspam@.hotmail.com> wrote in message
news:1108560501.098795.130320@.z14g2000cwz.googlegroups.com...
>I have a system with a lot of deadlock searches appearing in the log,
> but they usually do not find any deadlocks.
> message in the log:
> End deadlock search 21022 ... a deadlock was not found.
> How is deadlock search triggered? Is it configurable? I think
> performance is degrade by the deadlock searches but I need to know a
> little more about under what conditions they occur.
> /Sten
>|||Wayne Snyder wrote:
> A deadlock search occurs 500 millis after any lock is denied...
Deadlock
> searches used to happen immediately on denial ( in SQL 6.5). But many
of
> those deadlock searches were un-necessary, since it is normal to be
denied a
> lock while someone is using a row for a short period of time, then
they
> release the lock and you go about your business... Yet we used to
have to
> pay for a deadlock search anyway... In recent releases MS delayed the

> deadlock search until 500 millis after the lock is denied... THe
thinking is
> that during the normal case, you will be granted the lock in a very
short
> period of time, and the deadlock search can be avoided altogether...
Also
> this allows a single deadlock search the opportunity to search for
deadlocks
> for multiple users in a single search... All of this is an
optimization in
> recent SQL versions.
> There may be some way to set the deadlock search timeout, but if so ,
I do
> not know what it might be.( I don't think MS exposes that to us...)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Sten" <stenperersejspam@.hotmail.com> wrote in message
> news:1108560501.098795.130320@.z14g2000cwz.googlegroups.com...
log,
a
OK, so basically if You have long transactions holding locks more than
500 millis You will be in danger of deadlock search.
Thank You very much for a fast response.
/Sten