Trace Flag 4199: No Per-Session Override if You Enable it Globally

You can enable and disable trace flags either globally or per-session in SQL Server.

This makes it seem like perhaps if you enable optimization trace flag 4199 globally for all sessions, you might be able to disable it per-session.

But that’s NOT how it works.

The first clue is in DBCC TRACESTATUS

Here’s the code that enables Trace Flag 4199 globally on my instance:

DBCC TRACEON (4199, -1);
GO

I can confirm that the trace flag is enabled with this command:

DBCC TRACESTATUS;
GO

Enabling the trace flag globally doesn’t change my “Session” setting. Technically the trace flag isn’t enabled for my session– but it’s going to be enabled for me because it’s enabled globally.

Running DBCC TRACEOFF for my session doesn’t change the status

I can try disabling the trace flag for my session with this code:

DBCC TRACEOFF (4199);
GO

I haven’t put the -1 in there, so I’m  just disabling this for my session.

But no matter how many times I run this, the output from DBCC TRACESTATUS remains the same: the flag is enabled globally, and not enabled for my session.

The only way to turn this off is to turn it off globally

If I want to see how my queries optimize without trace flag 4199, I can disable it globally– but this impacts every query running against the instance:

DBCC TRACEOFF (4199, -1);
GO

SQL Server 2016 makes this easier to work with by providing database scoped Query Optimizer Hotfixes

In SQL Server 2016, you can now enable the very same optimizer hotfixes controlled by Trace Flag 4199 at the database scope by using ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES=ON.

If you have the setting configured at the database level, it’s much easier to test what would happen if the setting was NOT enabled, because you can compile your query from a different database.

Want to prove it to yourself? Here’s some demo code

I set up a repro for a bug fixed by Trace Flag  4199 / QUERY_OPTIMIZER_HOTFIXES for SQL Server 2016. Here’s code you can run to prove to yourself that if 4199 is enabled globally, you can’t disable it for your session: https://gist.github.com/LitKnd/612f6de6fb2bbc31100ee6f45df19d04

Selectively EnableTrace Flag 4199 and QUERY_OPTIMIZER_HOTFIXES in SQL Server 2016

Trace Flag 4199 has been in SQL Server for a while. I’ve long thought of this as the “Bucket of Optimizer Hotfixes” trace flag: enabling it turns on a variety of hotfixes that have been implemented over the years.

When query tuning, I’ve often tested whether or not Trace Flag 4199 makes a difference to the query I’m tuning. Most of the time it doesn’t make a difference. I’ve had some rare occasions where it’s made a query faster. I’ve never personally found a case where enabling the flag slowed a query down.

Past Trace Flag 4199 fixes go mainstream in SQL Server 2016 with compatibility level 130

One of the weird thing about the fixes under 4199 was that they never used to get merged into the mainstream codebase. The amount of things changed by the flag just kept growing. This was fixed in SQL Server 2016. If you’re using database compatibility level 130, all the fixes for prior SQL Server versions are enabled, and the optimizer will use them.

I think this is a great thing. Merging in those fixes makes understanding what 4199 does much less confusing — and Trace Flag 4199 is still being used for new fixes, too!

Implementing Trace Flags for optimizer hotfixes has always been a bit klunky

Trace Flag 4199 has a bit of baggage when  it comes to implementation. Prior to SQL Server 2016, if you wanted to enable these optimizer hotfixes, you have three choices:

  1. You can implement the trace flag globally, for the whole instance as a startup flag, or using DBCC TRACEON with -1. But that seems like overkill if you only have one query that needs the trace flag, and the trace flag implements a whole bunch of changes.
  2. You can implement the trace flag for a single session, using DBCC TRACEON without the -1. You’ve got to be really careful if you do this and use session pooling, and running DBCC TRACEON requires sysadmin permissions, so this is rarely used in production code.
  3. You can implement the trace flag for a single query using OPTION QUERYTRACEON, but there are some permission issues. If this is running as an adhoc query, it requires sysadmin permissions. There is a workaround where you can use it with lower permission in a stored procedure, but that doesn’t work for every application.

New database scoped option for Query Optimizer Hotfixes

In SQL Server 2016 RTM, we got the option to enable Query Optimizer Hotfixes for a given database. This is great to have a finer-grained scope. You enable this for a database with the following code:

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;
GO

New ‘USE HINT’ option for  Query Optimizer Hotfixes (and more)

Even better, in SQL Server 2016 SP1, we got the option to enable this for a given query in a hint, with code like this:

/* No sort here -- works same as DB setting (makes sense) */
SELECT IntCol
FROM dbo.LetsTalkAboutQueryOptimizerHotfixes
WHERE PartitioningCol < '2017-10-02'
ORDER BY PartitioningCol DESC, CharCol DESC
OPTION (USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES'));
GO

This gets around that pesky high-permissions problem that the QUERYTRACEON hint uses. A couple of things to keep in mind:

  • USE HINT can be used for more than just Query Optimizer Hotfixes. There’s a list in the documentation on hints, and you can also query them from the sys.dm_exec_valid_use_hints DMV.
  • HINT names are case sensitive.

What if I enable Query Optimizer Hotfixes at the database level, and want to test how a query would run if it was turned off?

Let’s say I have a few queries that get a performance boost from query optimizer hotfixes on SQL Server 2016. For whatever reason, I don’t choose the USE HINT route– I enable Query Optimizer Hotfixes at the database level.

Things are going fine, but when I’m tuning a slow query in that database, I wonder– would I get a different plan if I didn’t have Query Optimizer Hotfixes on?

But there isn’t a USE HINT option to disable query optimizer hotfixes just for my query.

Simple fix for testing: just compile your query from another database!

I have Jeremiah Peschka to thank for this simple solution: just use tempdb and compile your query against your database with three part names.

Since Query Optimizer Hotfxes is scoped to the database, using another database where the setting isn’t on to compile your query gives you a totally different plan, compiled with that database’s settings for query optimizer hotfixes.

Want to play around with it? Here’s a some repro code!

To make sure this was as good as it sounded, I set up a repro for a bug fixed after SQL Server 2016 RTM which requires enabling TF4199 or QUERY_OPTIMIZER_HOTFIXES as part of the solution.

This bug impacts partitioned tables that have only a single partition. The bug is that the optimizer inserts a SORT operator into the plan without realizing that the index it’s using provides the sorting.

I’ve enabled QUERY_OPTIMIZER_HOTFIXES at the database level, and here’s how my query plan looks… the bug is fixed:

But what would my plan look like with Query Optimizer Hotfixes off? When I compile the query from tempdb (which doesn’t have the setting enabled) using three part naming, the bug is there and the plan has a SORT operator:

 

You could selectively implement or test Query Optimizer Hotfixes by creating stored procedures or views in a separate database from the tables, and querying them

Let’s say you’ve got a very large database, and you want to do a side-by-side testing some code — one version with query optimizer hotfixes on, one with it off. But the database is so large that restoring two copies isn’t so attractive.

You can create a second, empty database, and create copies of your stored procedures or views that use three part names to access the tables in your primary database. Then you can enable or disable Query Optimizer hotfixes at the database scope in either database, and it’s easy to compare.

I think this is mostly an interesting option for testing more than production code. That’s just because in production code, if you want to selectively implement this, then the USE HINT option seems simpler.

But it’s always nice to have options!

Want to play around with this yourself?

Grab the code to repro these execution plans from this Gist.

Remember that you need to be running SQL Server 2016 for this to work, and you need SP1 if you’d like to play around with OPTION (USE HINT (‘ENABLE_QUERY_OPTIMIZER_HOTFIXES’)).