March 14, 2017 at 9:53 pm
Comments posted to this topic are about the item Enable Trace Flags in SQL Server
March 15, 2017 at 12:11 pm
Steve Jones - SSC Editor - Tuesday, March 14, 2017 9:53 PMComments 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.
March 15, 2017 at 5:30 pm
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.
March 16, 2017 at 4:16 am
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?
March 16, 2017 at 9:23 am
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.
March 16, 2017 at 9:42 am
No problem. I just panic a bit since we have that trace flag on our SQL 2014 servers.
Thanks for the explanation.
March 20, 2017 at 4:12 am
Hi, Steve, thanks for very useful article (especially for screenshots). Please, add to Enable Trace Flags at Startup section remark:
March 20, 2017 at 7:43 am
Thanks, Konstantin. Added.
July 21, 2020 at 1:45 pm
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
July 21, 2020 at 2:11 pm
Not by adding them to a startup proc. The trace flags themselves can alter behavior, so they should be tested in your environment.
July 21, 2020 at 2:42 pm
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
July 21, 2020 at 3:29 pm
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.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply