asked
SQL 2012 upgrade gives deadlock issues
We have recently migrated the SQL 2008 DB to SQL 2012. And thereafter we are facing lot of Deadlocks on Database. Please suggest on how can we solve this ?
can you please post the exact error deadlock message xml. You can get this from profiler trace
There's nothing in SQL Server 2012 that is inherently worse about deadlocks than previous versions of SQL Server. So, the things that are different might be the issue. The query optimizer in SQL Server 2012 is different. That means that queries that might have run adequately on previous versions of SQL Server could experience slower performance on 2012. But, that's almost always the case when looking at queries that were slow performers in those other versions of SQL Server too. They just performed well enough. Deadlocks are fundamentally a performance problem. So, the answer is to go through all the proper steps to ensure your queries are well-constructed, you have proper indexing in place, your statistics are up to date, and all the rest.
I wouldn't set the Max memory to the max value. That will cause contention with the operating system. You need to leave some for the OS. AS to the causes, I just can't say without a lot more information to go on. Yeah, it could, in part, be system configurations. But, the more likely cause are the queries, indexes, etc., that affect performance. Again, deadlocks are first and foremost a performance issue. If all transactions are instantaneous, you never see deadlocks. It's the excessive CPU/Memory use and scans against tables that lead to slow performance and blocking that can then lead to attempting to take locks on resources in different order that leads to deadlocks.
We used to have deadlocks and performance issues in older version as you said, but never so worse like this.Problem is we have lot of stored procedures contains many table updates in single transaction. Simultaneously the Application is hitting the other stored procs, which have complicated select queries on same tables. Comparatively , the deadlocks & Timeouts are more. Could this be due to any Server settings? We checked on Max server memory and changed it to Max possible value ( with respect to RAM).
16 People are following this question.
