Deadlocks trace flag

Question

Deadlocks trace flag

PiMané

SSChampion

Points: 13965
More actions
August 26, 2013 at 11:17 am

#277288

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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, MVP, M.Sc (Comp Sci)
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

PiMané SSChampion Points: 13965 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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, MVP, M.Sc (Comp Sci)
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

PiMané SSChampion Points: 13965 More actions · Answer 4

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

If you can measure any effect from that traceflag at all I'll be surprised.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

PiMané SSChampion Points: 13965 More actions · Answer 6

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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, MVP, M.Sc (Comp Sci)
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

PiMané SSChampion Points: 13965 More actions · Answer 8

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 9

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.

[font="Verdana"]Markus Bohse[/font]