1

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.

Improve this question
4
  • 1
    You reference deadlock errors in the application log, do you see the deadlocks occuring in SQL Server?
    – user507
    Commented Oct 9, 2012 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
    Commented Oct 9, 2012 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 :(
    – Ross Bush
    Commented Oct 9, 2012 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.
    – Ross Bush
    Commented Oct 9, 2012 at 18:04

1 Answer 1

Reset to default
1

"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.

Improve this answer
1
  • 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 :)
    – Ross Bush
    Commented Jan 27, 2013 at 3:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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