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

Trace Flags to turn on for SQL instances < 2012 Expand / Collapse
Author
Message
Posted Friday, January 18, 2013 12:51 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 15, 2014 8:27 AM
Points: 87, Visits: 556
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
Posted Tuesday, January 22, 2013 5:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:39 PM
Points: 183, Visits: 733
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 »

Add to briefcase

Permissions Expand / Collapse