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!!!
Tell me more
×
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. It's 100% free, no registration required.
|
|
||||
|
|
|
You can use Event Notifications to get notified whenever a deadlock happens on the server. http://www.sqlservercentral.com/articles/deadlock/65614/ Some additional links. http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/07/30/the-anatomy-of-a-deadlock.aspx http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/11/25/anatomy-of-a-deadlock-part-deux.aspx http://rusanu.com/2009/05/16/readwrite-deadlock/ |
|||
|
|
|
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. |
|||
|
|
I would go with automatic server side tracing of 2 main issues:
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. |
||||
|
|
|
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) |
||||
|
|
