on April 19, 2017
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