Aug 232012
 

Have any of you ever heard of trace flag 4199 for SQL Server?

This one blows my mind… It basically allows the query optimizer to use all previous bug fixes in it’s job of query optimization!

WOW!

Maybe I should use HTML blink here to make my amazement stand out that much more… nah.

MS – why not enable this by default and set a flag to disable it if you have problems?  I know the whole backwards compatibility thing… but sheesh!

It’s only for the minimum versions of SQL Server 2005 SP3 CU6, SQL Server 2008 CU7, or SQL Server 2008R2. I think it applies for SQL Server 2012, as it’s listed in MSDN, but do not see any additional details on it. I’ll test it as I do more SQL Server 2012 deployments for improvements and will note anything here when/if it arises.

I’m adding this as a strong option to my new build templates for SQL Server 2008R2, possibly 2012, and will be going to existing installations that I manage and evaluating if it will make an improvement or not. I’d advise that you do too!

  One Response to “SQL Server Trace Flag 4199”

  1. […] David K. Lee – Convergence of Data and Infrastructure – SQL Server Trace Flag 4199 https://www.davidklee.net/2012/08/23/sql-server-trace-flag-4199/ […]