June 10, 2014 at 4:19 am
Hi SQLGuru's,
I've been assigned a task to come up with a permanent fix for deadlocks happening on our instance (SQL 2012) and on daily basis. However, my question is can we really cure deadlocks...lif yes then pls. could you'll post information on how to get this cured. I understand people say that turn the flag on, capture the xx event, and then try to figure out what caused. However, I don't have to do this as we are using 3rd party tools for this and get daily alerts for it. We have a team of DBA's and we usually do not address these until it is happening frequently enough but can we really eliminate it or how can we change something from the application side if it's caused by the queries initiated by them...do we have control of what the stuff they do from the front end.
Pls. let me know how to deal with this...and I would really require some experience answers on this and not general guidelines.
Regards,
Faisal
June 10, 2014 at 4:36 am
In my experience you can not fix this at the backend; you need to make changes to the application. Application developers need to minimize the occurance of deadlocks by making sure their code access objects in the same order from different routines and queries. The DBA team helps the Devs by giving them the details of the deadlocks that have occurred so that they can go back to their code. See this article for some useful advice - http://technet.microsoft.com/en-us/library/ms191242%28v=sql.105%29.aspx
June 10, 2014 at 4:37 am
To start with... https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/
That should guide you through identifying and fixing most deadlocks. They do have to be investigated and fixed one by one, there's no magic switch you can set. Depending on the causes some can be fixed with indexes, most will require some code changes somewhere.
If there are still ones you're unsure of, post the deadlock graph (the one generated by traceflag 1222, not the older style), or the XML from the system health event session and we can look at those in more detail.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2014 at 4:53 am
Assuming we're talking SQL Server 2012, you don't have to set the traceflag any more. The system_health extended event session captures deadlock information automatically.
Deadlocks are fundamentally a performance problem. So, you have to tune the queries, adjust the indexes, and do the hard work required to make the deadlock stop.
One thing that can mitigate deadlocks is the use of read committed snapshot isolation level. It adds load to the tempdb, so testing before applying it is certainly in order, but because it reduces locking and blocking, it can help reduce the number of deadlocks you see.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 10, 2014 at 6:53 am
Here's an oldie but goodie for deadlock troubleshooting: http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx. Be sure to follow all 3 parts of the blog series.
Some deadlocks are a no-brainer to fix (once you have some knowledge and experience). Some can be a VERY difficult thing to decipher and even harder to fix permanently.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply