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://learn.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.
