Production Trace Flags for a Standard Build

Question

Post reply

Production Trace Flags for a Standard Build

alanspeckman

Hall of Fame

Points: 3344
More actions
January 23, 2013 at 7:03 am

#288293

Based on the trace flags in Victor Isakov's and Julie Koesmarno's presentation found here, I want to find out what everyone uses in the industry.

Viewing 4 posts - 1 through 3 (of 3 total)

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

alanspeckman Hall of Fame Points: 3344 More actions · Answer 1

This is my final list of Trace Flags I'm going to set in my production servers, new builds.

3226 - log backups are not written to errorlog(makes for a clean log for troubleshooting)

1224 - ms recommend - disables lock escalation based on the number of locks

1222 - deadlocks in xml format

1118 - allocate full extents for all dbs – mainly for tempdb, but also applies to all dbs. No downside to turning on, Paul Randal recommends on for all instances > SQL 2000

3502 - turns on checkpoint information – Now automatic in 2012, can show IO subsystem problems normally not showing up.

3604 - detailed info in the error log on checkpoints - needed when using 3502

I came up with this list based on the following references.

http://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/how-to-monitor-checkpoints-137148

http://blogs.msdn.com/b/psssql/archive/2012/06/01/how-it-works-when-is-the-flushcache-message-added-to-sql-server-error-log.aspx

http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/[/url]

http://www.victorisakov.com

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

None by default, traceflags are something I evaluate on a case-by-case scenario.

I'm interested to see where MS recommends 1224, since that can be a little risky in terms of lock memory usage. In the MSDN docs they recommend using 1224 over 1211, but it's not a recommendation that 1224 be used, just that it be used rather than 1211 if you are going to use a lock escalation traceflag

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

alanspeckman Hall of Fame Points: 3344 More actions · Answer 3

I think you are right, I am mis-interpreting these slides. Victor talks about using them in Production, but he states the following:

“WARNING: These trace flags should be used under the guidance

of Microsoft SQL Server support. They are used in this post for

discussion purposes only and may not be supported in future versions.”

But, from the same set of slides, what made me think of enabling these trace flags in Prod as a practice was this: "Might want to consider enabling them on your “standard build”"

My reasoning is 2 fold.

1. If the trace flag is now automatically on, as in 834, in 2008, what is wrong with using it in 2005?

Same logic applies to this article for 2012. If this information is written automatically now in 2012, what is wrong with enabling trace flags 3502/3504 on SQL 2008 R2?

2. If the trace flag either limits errorlog output making dianosing problems easier, as in 3226, or provides more detailed errorlog data but doesn't impact performance, as in 3502/3504, what is wrong with turning it on?

I agree on 1224, that one is dangerous to run without guideance from MS Support.

Lastly, I'll also reference a book by Adam Jorgensen and Steven Wort, "Professional Microsoft SQL Server 2012 Administration":

quote - "Your instance of SQL Server should not have trace flags turned on indefinitely, unless you have been instructed by Microsoft Support to do so."