Post reply

Trace Flags to turn on for SQL instances < 2012

  • January 18, 2013 at 12:51 pm

    #268834

    I need some opinions here.

    There are 2 trace flags

    3504 [/url]- 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[/url], 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/[/url]

    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

  • January 22, 2013 at 5:57 am

    #1579585

    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

Viewing 2 posts - 1 through 1 (of 1 total)

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