Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

When do you start troubleshooting a SQL Server 2005/2008 deadlocks and how? The alert is turned on SSMS via SQL Server performance condition alert, objects->SQLServer:Locks, Counter->Lock Waits/Sec, Instance:_Total, alert if counter: rises above value 3. Is this a proactive way of monitoring it? What is the acceptable value? I would highly apprecate your help. Thank you!!!

share|improve this question

The first thing to do is look at the deadlock graph and see exactly what's going on. Then you can consider your options, which usually boil down to a) modify the application to standardize on an order of locking objects (e.g. if possible given the application logic, make a convention with your developers to always lock tables in alphabetical order) or b) see if you are really operating at the appropriate isolation level in each transaction.

share|improve this answer
    
According to stackoverflow.com/a/112256/14731 a consistent locking order does not prevent deadlocks. – Gili May 14 '13 at 21:46

I would go with automatic server side tracing of 2 main issues:

  • long queries (you establish duration that's long for your environment)

  • deadlocks - check Deadlock graph and Lock:Deadlock chain

You will get trace files every day and you can monitor the action - see if some scheduled event is causing issues. You can see more details about profiling production in this question: Using SQL Profiler on a database that's in production.

share|improve this answer

Try setting traceflags, as desribed in this books online article: http://msdn.microsoft.com/en-us/library/ms178104%28SQL.90%29.aspx

DBCC TRACEON(1204)
DBCC TRACEON(1222)

-- new to SQL 2005
DBCC TRACEON(-T1222)

share|improve this answer

System Center Operations Manager (SCOM) with the SQL Server Management Pack can alert on deadlocks if you enable logging for message 1205. You can enable it with the following SQL query: EXEC sp_altermessage 1205, 'WITH_LOG', 'true'.

Also, see my answer to "Why isn't the objectname and indexname populated in deadlock graph?" for a PowerShell script I wrote that will pull deadlock information from the event buffer and write the deadlock graphs as xdl files that can be opened in SQL Management Studio 2012 or higher or viewed in notepad to see more detail.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.