 |
|
SQL Server Tips by Robin Schumacher
|
Deadlocks
Deadlocks or deadly embraces as they are called on other database
platforms occur when processes cannot proceed because they are
waiting on a set of resources held by each other or held by other
processes. Deadlock problems are a serious red flag that application
design issues exist in the database.
It is easy to understand whether or not there is a deadlock problem.
Depending on the SQL Server version, one of the two following
queries can be used to get a count of deadlocks, the first being for
SQL Server 7, and the other being for 2000 and higher:
* deadlock7.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
select
sum(cntr_value)
from
master.dbo.sysperfinfo
where
object_name = 'SQLServer:Locks' and
counter_name = 'Number of Deadlocks/sec'
* deadlock8.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
select
cntr_value
from
master.dbo.sysperfinfo
where
object_name = 'SQLServer:Locks' and
counter_name = 'Number of Deadlocks/sec' and
instance_name = '_Total'
Consistently seeing deadlock counts greater than zero will indicate
that some user processes are experiencing delays in completing their
work.
When SQL Server identifies a deadlock, it resolves the situation by
choosing the process that can break the deadlock. This process is
called the deadlock victim. SQL Server rolls back the deadlock
victim’s transaction, and notifies the process application by
returning an error message. It also cancels the process’ request and
allows the transactions of the remaining processes to continue. SQL
Server always attempts to choose the least expensive thread running
the transaction as the deadlock victim.
The above book excerpt is from:
High-Performance SQL Server DBA
Tuning & Optimization Secrets
ISBN:
0-9761573-6-5
Robin Schumacher
http://www.rampant-books.com/book_2005_2_sql_server_dba.htm |
