Trace Flag 4199 - Enable globally?

Question

This may fall under the category of opinion, but I'm curious if people are using trace flag 4199 as a startup parameter for SQL Server. For those that have used it, under what circumstances did you experience query regression?

It certainly seems like a potential performance benefit across the board, I'm considering enabling it globally in our non-production environment and letting it sit for a couple months to ferret out any issues.

Are the fixes in 4199 rolled into the optimizer by default in 2014 (or 2016)? Although I understand the case for not introducing unexpected plan changes, it seems odd to keep all these fixes hidden between versions.

We're using 2008, 2008R2 and mostly 2012.

Answer 1

Personally, whenever I build a new server for a new project I always enable TF4199 globally. The same applies when I upgrade existing instances to newer versions.

The TF enables new fixes that would affect the behaviour of the application, but for new projects the risk of regression is not an issue. For instances upgraded from previous versions, the differences between old and new version are a concern on their own and having to deal with plan regression is expected anyway, so I prefer fighting with it with TF4199 enabled.

AS far as existing databases is concerned, there is only one way to know: test it. You can capture a workload on the existing setup and replay it after enabling the flag. RML Utilities can help you automate the process, as described in this answer.

Obviously, the flag affects the whole instance, so you'll have to test all the databases sitting there.

Answer 2

My search on the topic brought me here, so I'd just like to share my recent experience on the topic.

I was running SQL 2014, so I figured that I would be safe from having to care about 4199 for a little bit... but it just wasn't true...

How to Diagnose if you need 4199

If your query appears to run poorly, particularly when you feel it shouldn't, then try adding the following to the end of it too see if it fixes all your problems, as you might need 4199 ("Enable all Query Optimizer fixes.")

SELECT SomeColumn
FROM SomeTable    
OPTION(QUERYTRACEON 4199)

In my situation, I had a top 10 clause blowing up a query that ran fine without, which is what made me think something fishy was happening, and that 4199 might help.

About 4199

Any SQL Server Query Optimizer bug/performance fixes that are created after the new major version release actually get hidden and blocked. This is in case they might actually harm some other theoretically perfectly optimized program. So, install updates as you might, the actual query optimizer changes are not enabled by default. Therefore, once a single fix or enhancement has been done, 4199 becomes a necessity if you want to take advantage of it. As many fixes show up, you'll eventually find yourself turning this on when one of them affects you. These fixes usually are tied to their own trace flags, but 4199 is used as the master "Turn every fix on."

If you know which fixes you need, you could enable them piece-meal instead of using 4199. If you want to enable all fixes, use 4199.

Ok, So you want 4199 Globally...

Just Create a SQL Agent Job which runs every morning with the following line to enable the trace flag globally. This ensures if anyone turned them off or etc, that they get turned back on. This job step has pretty simple sql:

DBCC TRACEON (4199, -1);

Where -1 specifies the Global part in DBCC TRACEON. For more info see:

https://msdn.microsoft.com/en-us/library/ms187329.aspx?f=255&MSPPError=-2147217396

"Recompiling" Query Plans

In my most recent attempt I had to enable 4199 globally, and then also remove existing cached query plans:

sp_recompile 'dbo.SomeTable'

https://msdn.microsoft.com/en-us/library/ms181647.aspx?f=255&MSPPError=-2147217396

Where the recompile stored procedure finds any query plans relating to the database object (such as a table) and deletes those query plans, requiring the next attempt to run a similar query to compile them.

So, in my case 4199 kept the bad query plans from being created, but I also had to remove those that were still cached via sp_recompile. Pick any table from the known query affected and you should be good to try that query again, assuming you have now enabled 4199 globally and cleared the offending cached query plan.

In Conclusion on 4199

As you utilize indexes, a smart query plan optimization becomes important to actually using those indexes intelligently, and assuming that over time some fix to the query optimization process will be released, you're generally in safe water to just run with 4199 globally enabled, as long as you realize that some new fix might not actually play as nicely with a highly optimized database that was such optimized in the prior environment before said fix. But what does 4199 do? It just enables all fixes.

Answer 3

I wanted to share my experience with trace flag 4199.

I just finished diagnosing a performance issue on a customer system running SQL Server 2012 SP3. The customer was moving a reporting database away from their production OLTP server onto a new server. The customer's goal was to remove competition for resources with the OLTP queries. Unfortunately, the customer said the new reporting server was very slow.

A sample query run on the OLTP system completed in 1.6 seconds. The query plan did an index seek on a ~200 million row table that was part of a view.

On the new server, the same query completed in 10 minutes 44 seconds. It performed an index scan on the same ~200 million row table.

The reporting server data was a copy of the OLTP data, so it did not appear to be a difference in the data.

I was stumped until I recalled that our software (which runs their OLTP system) enabled some trace flags on startup. One of them, 4199, I recalled was a query optimizer fix.

I tested enabling trace flag 4199 on the customer's new reporting server, and the reporting query completed in 0.6 seconds. (Wow!) I disabled the trace flag, and the query was back to completing in 10 min 44 sec. Enabled the flag: back to 0.6 seconds. Apparently, enabling the trace flag enabled the optimizer to use an index seek into the view on the 200 million row table.

In this case, the query optimizations enabled by trace flag 4199 made an enormous difference. Your experiences may vary. However, based on this experience, it definitely seems worth enabling to me.