Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deadlocks trace flag Expand / Collapse
Author
Message
Posted Monday, August 26, 2013 11:17 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 2:30 PM
Points: 515, Visits: 1,138
Hi,

We were experiencing some deadlocks on our database so I activated the deadlocks trace flag with DBCC TRACEON(1204, -1) to log the deadlock information.
Now that we've found the problem my question is if there's any problem, overhead, slower performance, .. on SQL Server if the flag is ON, since by default the flag if OFF..

Thanks,
Pedro




If you need to work better, try working less...
Post #1488467
Posted Monday, August 26, 2013 11:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 40,157, Visits: 36,543
Use 1222. 1204 is legacy, for SQL 2000 and before. Less info, harder to read, much harder to debug.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1488470
Posted Monday, August 26, 2013 2:08 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 2:30 PM
Points: 515, Visits: 1,138
GilaMonster (8/26/2013)
Use 1222. 1204 is legacy, for SQL 2000 and before. Less info, harder to read, much harder to debug.


Thanks Gail, but is it necessary to turn it off when not needed?

Pedro




If you need to work better, try working less...
Post #1488489
Posted Monday, August 26, 2013 2:20 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 40,157, Visits: 36,543
I used to run a server with that traceflag on 24/7. If you have so many deadlocks that the traceflag is causing noticable load, the traceflag is not the primary problem


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1488491
Posted Monday, August 26, 2013 2:23 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 2:30 PM
Points: 515, Visits: 1,138
GilaMonster (8/26/2013)
I used to run a server with that traceflag on 24/7. If you have so many deadlocks that the traceflag is causing noticable load, the traceflag is not the primary problem


We've already discovered the deadlock problem and so far we had no more.
The flag causes no extra load unless there are deadlocks? If so we'll leave it on so when another deadlock occurs the information will be available..

Thanks,
Pedro




If you need to work better, try working less...
Post #1488494
Posted Monday, August 26, 2013 2:30 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 40,157, Visits: 36,543
If you can measure any effect from that traceflag at all I'll be surprised.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1488495
Posted Monday, August 26, 2013 2:36 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 2:30 PM
Points: 515, Visits: 1,138
GilaMonster (8/26/2013)
If you can measure any effect from that traceflag at all I'll be surprised.


Cool... Thanks.
If the flag is "harmless" why isn't it enabled by default?
Just like the Optimized of ad-hoc queries....




If you need to work better, try working less...
Post #1488497
Posted Monday, August 26, 2013 2:58 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 40,157, Visits: 36,543
I didn't say it's harmless, I said you are highly unlikely to be able to measure an impact.

Now, what if you have something like Sharepoint that is known to deadlock (and it's not a problem), that traceflag was enabled by default leading to many, many MB of error log, potentially filling the drive?

Optimise for ad-hoc is not enabled by default for the same reason that any new option is not enabled by default, minimal surprises when upgrading versions. In scenarios where there's minimal ad-hoc query usage, or when I know that ad-hoc queries will always have plans able to be reused, I wouldn't enable it.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1488503
Posted Monday, August 26, 2013 4:33 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 2:30 PM
Points: 515, Visits: 1,138
Our software is an ERP with queries build in the program, so loads of ad-hoc queries...
The deadlock flag can be useful to catch future problems, and since they can be very tricky to catch, being the flag enabled, it's easy to get the deadlock detail...
If we notice performance problems due to the flag the i'll disabled it.

Thanks,
Pedro




If you need to work better, try working less...
Post #1488515
Posted Tuesday, August 27, 2013 7:42 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 7:17 AM
Points: 4,427, Visits: 4,174
With SQL 2008 R2 and higher you don't really need to enable this traceflag.
You can get the same (and more) information form the System_Health extended events session which logs deadlock events.


Markus Bohse
Post #1488773
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse