Enable Trace Flags in SQL Server

Question

Enable Trace Flags in SQL Server

Steve Jones - SSC Editor

SSC Guru

Points: 736212
More actions
March 14, 2017 at 9:53 pm

#77587

Comments posted to this topic are about the item Enable Trace Flags in SQL Server
When specifying a trace flag with the -T option, use an uppercase "T" to pass the trace flag number. A lowercase "t" is accepted by SQL Server, but this sets other internal trace flags that are required only by SQL Server support engineers.(Parameters specified in the Control Panel startup window are not read.): https://technet.microsoft.com/en-us/en-en/library/ms190737%28v=sql.120%29.aspx

espadartico Mr or Mrs. 500 Points: 559 More actions · Answer 1

Steve Jones - SSC Editor - Tuesday, March 14, 2017 9:53 PM
Comments posted to this topic are about the item Enable Trace Flags in SQL Server

Hi Steve,

Great article, I have just one question, I thought that the trace 4199 would turn on the fixes at the optimizer level, not allow you to use the old cardinality estimator.

Thanks.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

For which version? For SQL 2016, you shouldn't be using this, as the new cardinality estimator is on.

For 2014, this should help: https://support.microsoft.com/en-us/help/974006/sql-server-query-optimizer-hotfixtrace-flag-4199-servicing-model

Essentially, if you enable this with a database in compat level 120, this turns on the new optimizer for the database.

espadartico Mr or Mrs. 500 Points: 559 More actions · Answer 3

I got confused here, in the article when you are showing the QUERYTRACEON example you say: "As an example, if I want to use the old query optimizer's cardinality from SQL Server 2014", are you refering to a SQL Server 2016 with a compatibility level of 120?

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

Sorry, that was just an example, and I was thinking one thing and writing the other. This was an article on trace flag changes, not the specifics of what an individual trace flag does, which I didn't verify in this case.

That was the wrong trace flag. 9481 was the one I wanted.

espadartico Mr or Mrs. 500 Points: 559 More actions · Answer 5

No problem. I just panic a bit since we have that trace flag on our SQL 2014 servers.

Thanks for the explanation.

Konstantin Taranov SSCertifiable Points: 6498 More actions · Answer 6

Hi, Steve, thanks for very useful article (especially for screenshots). Please, add to Enable Trace Flags at Startup section remark:

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 7

Steve Jones - SSC Editor

SSC Guru

Points: 736212

March 20, 2017 at 7:43 am

#1933990

Thanks, Konstantin. Added.

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 8

Is there any negative impact to adding trace flags to a server startup proc? More of as a failsafe that anything...

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 9

Not by adding them to a startup proc. The trace flags themselves can alter behavior, so they should be tested in your environment.

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 10

Thanks Steve, was just seeking confirmation. I realize this isn't a "standard" or best practice per say, but always have added it in to facilitate server builds across large distributed teams. In our shop we all use an agreed upon MSSQL build document and the trace flags get set in the configuration portion during the build itself. They are also added to the startup proc in case a DBA misses a step or simple "forgets". In that scenario, after all the databases come online, the start proc will fire and enable any flags that might have been missed.

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 11

I don't think there are any issues with re-enabling a trace flag that is enabled in the Configuration Manager/Services applet. This certainly gives you a second place where things are enabled, but I'd doc this, as if someone disables a trace flag during startup, and then you re-enable it with the proc, this can cause issues. For example, MS Support says turn this off and the DBA does, but forgets about the startup proc.

Maybe you want to add a RAISERROR WITH LOG in the startup proc as well to note that you've set a trace flag.