Hi everyone. I have a 4-year-old system that is running SQL Server 2008 R2 on the backend and we are accessing the data through a MS Access front end. We are utilizing ODBC to connect to the server. I recently started receiving a TON of deadlock issues for my MS Access users and I really can't figure out why. I'm wondering if someone can point me in the right direction to figure out what is going on and how to solve it once and for all. It does not appear to be occurring on any one process or any smaller set of tables. It seems to occur across the broad spectrum of the whole database.
Wondering if its processing power or memory, or something. Restarting the server tends to make things work a little better, but the problems just keep coming back. The user experience on the front end is deadlocks when they're trying to read data and/or write data.
Any help someone can give would be awesome. I would think there is some way to log the deadlocks or something so I can see what the symptoms are surrounding their occurrence.
Thanks in advance!
Trace flags 1222 and 1204 will log the deadlock entries
So my Point Number One is that because this has only just started happening it is therefore related to a change that occurred at that time. So first figure out what that change was ... and work back from there.
The quick solution to this is probably to turn on Read Committed Snapshot (Isolation) for the database (no other software changes needed). This can have disasterous consequences for TEMPDB and Performance, but IME usually only if the software is crap to start with and does lots of Row-By-Agonising-Row (RBAR) type loops and cursors, and perhaps also lots of Mega-Updates. There are also a few edge-cases (wanting to reuse data that you yourself have just changed, in certain specific ways) which would require software change, but I think it is fair to say that the need for such software changes have been extremely rare in places that have moved to RCSI - so that might work for you. Better to find what changed / the cause though.
We've used RCSI for years with great effect, but our stuff is all OLTP and we don't do no stinking RBAR!!
Deadlocks are already captured in the system health XE session.
You can query them using this script: http://blogs.technet.com/b/sqlpfeil/archive/2013/05/27/did-you-know-dead-locks-can-be-track-from-the-system-health-in-sql-2012.aspx
Once you have identified the source of your deadlocks, try to find out why they are happening. Hint: deadlocks are less likely to happen when queries run fast and hold locks for a short time. Do you have some missing index that could speed up some query? Did any index change recently? Did some table grow to exceed a tipping point and changed some plans? Investigate this and you'll fix it.
Hi everyone:
I was able to see a lot of deadlock activity from the Activity Monitor and see that most of the deadlocks revolved around a particular process. I have now worked to improve that process, but it essentially had to do with the fact that we use MS Access as our GUI. This particular process has 1 - 4 users at any given time scanning barcodes to look up information and then scanning another barcode to perform an update on the table. The target table had quite a few records on it, which aren't a problem for the SQL server, but that many records bog down the Access GUI because it queries the linked ODBC tables using the Access Jet Engine. So, those individual queries were taking a long time and each time they would perform a request from the SQL server, the way Access seeks a lock and the time it took to select the appropriate records made it so locks were stacking up all over the place causing all kinds of problems.
Making matters a little worse, when the user would update the record from their GUI, there is a trigger on the SQL server on that table that would perform a conditional insert into another table depending on whether or not a particular record already existed on the trigger destination table. That trigger destination table did not have the proper indexing to make those lookups fast enough to know whether or not it should write the table to the trigger destination table, so this slowed things down a little bit as well.
Anyway, I think cleaning up this process has solved the problem. spaghettidba said it right : "Hint: deadlocks are less likely to happen when queries run fast and hold locks for a short time. Do you have some missing index that could speed up some query? Did any index change recently? Did some table grow to exceed a tipping point and changed some plans?"
Thanks everyone for your help!