April 22, 2014 at 1:35 am
Is read committed snapshot isolation enabled on one database and not the other?
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc,snapshot_isolation_state FROM sys.databases
Also, when posting, please use full words rather than acronyms like "stp" (stored procedure? step? stop?) and "m/c" -as this makes it harder to understand the question.
April 22, 2014 at 4:06 am
Any deadlock trace is on? Any information related to deadlock in sql log?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 22, 2014 at 4:18 am
Deadlocks are fundamentally a performance issue. So, are the other databases the same size as the one that's getting the issue? Are the same indexes in place? Are the statistics the same? Have the statistics been updated in the same way? Any of these issues will change the behavior. That's before we get to things like the connection isolation level (such as using read committed snapshot, a great idea by the way), ANSI settings, cost threshold for parallelism, max degree of parallelism or some of the more obscure things that might affect execution plans and therefore performance. It's likely to be something from the list above.
As far as capturing deadlocks goes, the system_health extended event session is running by default in all servers 2008 or better and automatically captures deadlock graphs. You don't have to enable trace flags or specifically do anything to capture deadlock information any more.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply