Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trace Flags to turn on for SQL instances < 2012


Trace Flags to turn on for SQL instances < 2012

Author
Message
alanspeckman
alanspeckman
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 766
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
ffarouqi
ffarouqi
SSC Veteran
SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)

Group: General Forum Members
Points: 297 Visits: 1240
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
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