For those of you that keep track of what I discuss in The Heap™ you might have been following my endeavours whilst migrating a database from SQL Server 2008 R2 to SQL Server 2016 and battling with performance issues. Eventually we had to configure the following trace flags:

Trace Flags ON

+-----------+--------+--------+---------+
| TraceFlag | Status | Global | Session |
+-----------+--------+--------+---------+
|      2335 |      1 |      1 |       0 |
|      2371 |      1 |      1 |       0 |
|      4199 |      1 |      1 |       0 |
+-----------+--------+--------+---------+

2355 : Assume fixed amount of memory
2371 : Linear Update Statistics Threshold   
4199 : Enable QO Fixes  

Testing Statements

In order to regularly test the performance of certain statements, I will run them with additional trace flags either ON or OFF. This is achieved with the following piece of code which is part of the statement I'll execute:

DECLARE @DynaFlags  as int = 0 -- BitWise
DECLARE @DynaFlOFF  as int = 0 -- Bitwise
DECLARE @DynaStmts  as int = 0 -- BitWise

SET @DynaFlags = 0
SET @DynaFlOFF = 1
/* =============================================================================
    Setting Trace Flags ON or OFF
    https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver15
   ============================================================================= */
IF @DynaFlags & 1 = 1 DBCC TRACEON(2312)    -- [Global | Session | Query]   Force use of Cardinality Estimator 12.x (2014) if database running in compatability level lower
-- if @DynaFlags & 2 = 2 DBCC TRACEON(0)    
-- if @DynaFlags & 4 = 4 DBCC TRACEON(0)    
-- if @DynaFlags & 8 = 8 DBCC TRACEON(0)

IF @DynaFlOFF & 1 = 1 DBCC TRACEOFF(2335)
-- IF @DynaFlOFF & 2 = 2 DBCC TRACEOFF(....)
-- IF @DynaFlOFF & 4 = 4 DBCC TRACEOFF(....)
[...]

SET @DynaStmts = 3
if @DynaStmts &       1 = 1     SELECT 1,       '#000 SuperUser Statements'

DBCC TRACESTATUS()
SET STATISTICS IO ON
SET STATISTICS TIME ON

IF @DynaStmts & 1 = 1 BEGIN
select distinct d.id from.....
END

Trace Flags OFF

In some cases I would like to reverse the effect of having turned on the trace flags globally in order to see how the statements would perform. I thought of turning off the trace flags using the following command:

DBCC TRACEOFF(2335)

…and then executing the relevant SELECT .... statement(s). Before I run the actual statements I check the status of the trace flags with:

DBCC TRACESTATUS()

However, this is returning the same as above:

+-----------+--------+--------+---------+
| TraceFlag | Status | Global | Session |
+-----------+--------+--------+---------+
|      2335 |      1 |      1 |       0 |
|      2371 |      1 |      1 |       0 |
|      4199 |      1 |      1 |       0 |
+-----------+--------+--------+---------+

Question

Isn't it possible to turn off trace flags that are set globally at session level before I execute a statement?

Or

Have I found a bug?

I had a look at the query execution plan and the only mention of the trace flags are as follows:

            <TraceFlags IsCompileTime="true">
              <TraceFlag Value="2335" Scope="Global" />
              <TraceFlag Value="2371" Scope="Global" />
              <TraceFlag Value="4199" Scope="Global" />
            </TraceFlags>
            <TraceFlags IsCompileTime="false">
              <TraceFlag Value="2335" Scope="Global" />
              <TraceFlag Value="2371" Scope="Global" />
              <TraceFlag Value="4199" Scope="Global" />
            </TraceFlags>

Concept Works for DBCC TRACEON()

If I set the @DynaFlags=1 to turn on the TF 2312 in my example code, then it works fine as the DBCC TRACESTATUS() returns:

+-----------+--------+--------+---------+
| TraceFlag | Status | Global | Session |
+-----------+--------+--------+---------+
|      2312 |      1 |      0 |       1 | <<== here
|      2335 |      1 |      1 |       0 |
|      2371 |      1 |      1 |       0 |
|      4199 |      1 |      1 |       0 |
+-----------+--------+--------+---------+

QUERYTRACEON()

This is not an option in my current configuration. I'm trying to automatically set trace flags on or off and then execute the relevant statements to examine performance.