Take the 2-minute tour ×
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.

We recently upgraded a server to SQL Server 2008 R2. The server had been running SQL Server 2005. We are using

  • Transactional Replication
  • Management Data Warehouse and Data Collector on the Production server

We are starting to see deadlock errors in the application log. This did not happen in SQL 2005.

On the reporting database I can replicate a deadlock using an "ad-hoc" query with an inner join and table scan on an active table. However, I can run reporting procs that take minutes to complete with no problems.

Is sql 2008 more sensitive to deadlocks to sql 2005? I am going to start undoing some of the changes we have implemented and tested. Before I go that route I wanted to see if anyone else has noticed increased deadlocks when upgrading to 2008 R2.

share|improve this question
1  
You reference deadlock errors in the application log, do you see the deadlocks occuring in SQL Server? –  Shawn Melton Oct 9 '12 at 16:48
    
Did anything but the SQL version change with this? Schema or application changes or hardware changes? I've seen deadlocks go up when performance went down resulting in a higher chance that two queries are running at the same time. –  cfradenburg Oct 9 '12 at 17:20
    
Yes, the database has two triggers on very active tables that were moved from the reporting server to production(in an effort to normalize schema in the event of production going down...we could use the reporting DB as a failsafe). The statements in these triggers showed up in the deadlock graphs when the server went into a frenzy. But after a reboot last week we have been getting a few sporatic deadlocks from other resources not involved in the trigger updates. My initial thought is that the trigger logic has placed more strain on replication :( –  lrb Oct 9 '12 at 17:37
    
The applications logs to the window's log when a database exception occurs, that is where it was discovered. The log blew up 1.5 weeks ago with deadlock exceptions. During the blowup, I turned on the trace flags for lock errors and ran the profiler to catch a trace of the locks. Alas, we had to reboot the server. The lock trace flags are normally off in sql server. –  lrb Oct 9 '12 at 18:04

1 Answer 1

up vote 1 down vote accepted

"Sensitivity to deadlocks" is not version-specific. It is related to concurrency and contention. Please see this BOL reference on more information regarding deadlocking.

There are certain things that influence deadlocking, such as transaction isolation levels, and lock escalation. These are configurable settings that impact if and when deadlocking can/will happen in certain situations.

It will take a bit of investigation on your part to find out why you're seeing more deadlocking happening. But to answer your question, it isn't specific to the version of SQL Server.

As for the deadlock process, when SQL Server runs into a deadlock it will choose a victim so that the unresolvable concurrency situation can complete.

share|improve this answer
    
Thanks for the info...The problem turned out to be the index rebuild was not running every night. During the migration, the rebuild index job did not go over. After creating the rebuild index nightly script on the production server the deadlocks disappeared :) –  lrb Jan 27 '13 at 3:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.