SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deadlocks trace flag


Deadlocks trace flag

Author
Message
PiMané
PiMané
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2724 Visits: 1351
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...
GilaMonster
GilaMonster
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212393 Visits: 46259
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é
PiMané
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2724 Visits: 1351
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...
GilaMonster
GilaMonster
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212393 Visits: 46259
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é
PiMané
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2724 Visits: 1351
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...
GilaMonster
GilaMonster
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212393 Visits: 46259
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é
PiMané
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2724 Visits: 1351
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...
GilaMonster
GilaMonster
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212393 Visits: 46259
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é
PiMané
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2724 Visits: 1351
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
MarkusB
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10081 Visits: 4208
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search