Infrastructure at your Service

Stéphane Haby

Trace Flag 4199 or not in SQL Server 2016?

Some Trace Flag like T1117 and T1118 are no more needed as you can read on David’s blog.
But that’s not all, you have also the T4199…

What’ does the Trace Flag T4199?

This Trace Flag enables all query optimizer fixes.
A lot of DBAs have enabled this Trace Flag globally during the build of a new server.
If you want to take advantage of an enhancement or a fix, the T4199 becomes a necessity…

But now, with SQL Server 2016, this is an old story!

SQL Server 2016 eliminates the T4199

In SQL Server 2016, you don’t need to enable this Trace Flag, forget your old habit!
It is automatically included when you change the COMPATIBILY LEVEL to 130:

USE [master]
GO
ALTER DATABASE [MyDatabase] SET COMPATIBILITY_LEVEL = 130
GO

Advise: If you find a unexpected/poor plan, use the Query Store to analyze and force a plan!
More information on the KB974006

2 Comments

  • SIMBOZEL says:

    i Stéphane,
    i know a new feature with SQL Server 2016 SP1, eliminate parameter sniffing by positionning this option on dedicated database:
    ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;

    best regards

    • Stéphane Haby says:

      Hi,
      Yes, you’re right and wrong 😉 –> it is the parameter ENABLE_QUERY_OPTIMIZER_HOTFIXES the equivalent of the trace flag 4199.
      The equivalent of the parameter DISABLE_PARAMETER_SNIFFING is the Trace flag 4136.
      The article I wrote being before the release of the sp1 so the parameter did not exist yet … On occasion, I will make a blog on this option.
      Regards,
      STH

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Stéphane Haby
Stéphane Haby

Delivery Manager and Senior Consultant