Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  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  

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter