Trace Flags to turn on for SQL instances

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Trace Flags to turn on for SQL instances <...

Trace Flags to turn on for SQL instances < 2012

Rate Topic

Display Mode

Topic Options

Author

Message

alanspeckman

Posted Friday, January 18, 2013 12:51 PM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 1:59 PM
Points: 62, Visits: 384

I need some opinions here.

There are 2 trace flags
3504 - For internal testing. Will raise a bogus log-out-of-space condition from checkpoint()

and

1118 - Switches allocations in tempDB from 1pg at a time (for first 8 pages) to one extent

In this blog post, at the very bottom, Paul says 1118 applies to not just tempdb, but all user databases. In a 2012 edit of that post, he recommends turning it on for all SQL instance >= 2000 and < 2012.

3504 is another trace flag that is turned on by default in 2012(thank you Ben Miller for confirming this).
See Ben's recent experience with 2012 in this podcast.
http://peopletalkingtech.com/episode-016-ben-miller

Ben found some IO subsystem problems with 2012 that would not have showed up in the ErrorLog if he had used 2008 R2. I recommend listening to this podcast.

So, I built two 2008 R2 clusters for a Sharepoint 2010 farm. I'm worried about performance, contention in TempDB and I also want to get more Z's knowing the IO subsystem my SAN friends gave me is working well.

Should I enable these two trace flags on both clusters, one is a Content database cluster, the other is a Search database cluster, shared SAN drives?

Using the following information, I would say yes. Any thoughts?
TF 1118
http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/
see [Edit: 2012]

TF 3504
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
(thank you Ben)
http://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/how-to-monitor-checkpoints-137148

Thanks,
Al

Post #1409069

faisalfarouqi

Posted Tuesday, January 22, 2013 5:57 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:53 PM
Points: 74, Visits: 466

I'll only enable TF 1118...only if I have TempDB contention and even if splitting the files doesn't help. However, as Sql Server Best Practice Analyzer suggests you should add this trace flag as one of the start up parameters.

Regards,
Faisal

Post #1409979

« Prev Topic | Next Topic »

Permissions