Hi experts,
This instance is SQL 2012 Standard and I have patched it to SP4. We're still getting lots of deadlocks, 5 per hour on average.
Just now noticed that the main user databases (the one experiencing all the deadlocks) is set to Compatibility Level 2005, not 2012. (My guess is that it's still 2005 because they restored from a 2005 instance when the migrated.)
** Is there a good chance that by flipping the Compatibility Level to 2012, that might reduce the number of transaction deadlocks? Mostly Office apps use this db and they use the RBAR technique - row-by-agonizing-row.
Thanks for your opinions.
What do you understand about rbar?
I don't imagine that the compatibility level would affect deadlocking, and you'll have other side effects that may impact performance by changing it. Typically I prefer to set databases to the highest compatibility level available, it generally doesn't hurt, but just be aware that it might.
What I would recommend for deadlocks is setting the database to Read Committed Snapshot Isolation, that should reduce some locking contention. Another thing to look for is transactions created by application code, this sometimes happens with ORMs or other code that's doing RBAR; they'll start a transaction and loop through the result set. If that is the case, there's no point in you changing database settings, the code has to be changed (remove transaction from the app, and have the data retrieved via a stored procedure if possible).
Did you look at the deadlock material I posted in your other thread? The deadlock query that Wayne Sheffield wrote will provide the isolation level of the deadlocked elements. If you see it as Serializable, that's a very good indication that the app code/ORM is creating the transaction that's causing the deadlocks. Again, until the app side is corrected, you're wasting your time on the database side trying to fix deadlocks.
2 Likes
Until you deal with the exact issue of an Access database with a sql backend, you will be putting chewing gum on a hole in a submarine.
You can archive all the data you want, change compatibility etc, the issue is application side primarily.
1 Like
Service packs are to fix the bugs in the product that are identified in its previous release. They are not going to help if the application itself is creating deadlocks.
1 Like
Thanks for your detailed reply robert_volk. This database (small 20 GB data size) is shared by the front end Access app as well as is used for reporting. The reporting functionality is via views, some of which are indexed, and stored procs. In the next 2 months, I plan to move the reporting to a separate db dedicated to just reporting. With the above info, in the short-term, do you still think we could have fewer deadlocks by enabling Read Committed Snapshot Isolation? Thanks again.
The idea behind archiving is to get to a smaller data footprint, thereby enabling queries to complete faster. But yes the underlying Access r-b-a-r issue is still there.
Which is just addressing a symptom , not the disease itself
Yes, I did review the information. Thanks very much. Oh, Wayne Sheffields domain name is no longer active Waynesheffield.com
Don't just enable RCSI without adjusting the tables for performance afterward. That setting has some pretty big overhead that you can't just ignore.
First, it will add 14 bytes to every row. If your rows are at all tightly packed on pages, that will cause lots of page splits, which will hurt overall performance (but ironically could help with deadlocking if it's caused by single-row lookups).
Second, SQL will have to keep a copy of all modified rows in tempdb for a certain period of time. Once a SELECT query starts running, SQL will have to keep the original copy(ies) of all modified (DELETEd &/or INSERTed &/or UPDATEd) rows until it finishes. Naturally this will add significantly to the load on tempdb. Make sure you allow for that.
1 Like
Now there's a shocker. I hope he moved it and didn't just kill it. The man had a huge amount of knowledge posted there.
That link still works on Wayne's site, plus other links work for me too.
He hasn't blogged anything there in a while though. 
Hmm it was down for 2 days. I think his domain expired for a short while. Good to see it back up.