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.
Best Answer
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.