3

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.

3
  • I have a feeling that I'm stating the obvious here, but since I have some difficulties following the example, I'll mention just in case: If you turn on a traceflag globally (-1), you have to turn it off with -1 as well. Sep 21 '20 at 13:13
  • I was somehow expecting an answer along your lines. So I can't turn off the trace flag just for a/my session? I don't want to turn it off globally, just for the duration of my query.
    – John K. N.
    Sep 21 '20 at 13:19
  • 1
    Ah, I see. You want it on globally, but not for your own session? I don't think trace flags work that way. If it is on at any level, then it is on. Like OR logic, I guess. But I've leave it to somebody that has more insights to jump in here. Sep 21 '20 at 13:36
3

As Tibor mentioned in the comments, if a trace flag is enabled globally, it cannot be disabled at the session level.

So that's some bad news for you.

But I also have some good news for you.

Trace flag 2371 is in your list. With SQL Server 2016 or higher database compatability level in the databases of concern, that can come out of your list. With the latest compatibility levels, that behavior is default (stats update-qualifying threshold of SQRT(1000*rows) rather than 20%+512 as previous default).

And some more good news. If SQL Server 2016 RTM CU6 or higher, I personally have dropped trace flag 4199 from my recommendations in lieu of latest database compatibility level and query_optimizer_hotfixes database scoped configuration. There was an unfortunate blip in kb3171555 where a fix was enabled in 2016 CU1 with trace flag 4199 but wasn't enabled until CU6 with the database scoped configuration query_optimizer_hotfixes. Ouch! But to my knowledge that hasn't happened again since and T4199 and query_optimizer hotfixes have stayed in sync! Yay! https://support.microsoft.com/en-us/help/3171555/kb3171555-adds-trace-flag-9358-to-disable-batch-mode-sort-operations-i

All right... one more piece of good news. Trace flag 2312 is to enforce the default cardinality estimater, rather than the legacy cardinality estimater. Guess what? That can be done at the database scoped configuration level, too! Legacy_cardinality_estimation - turn that off.

OK. That leaves trace flag 2335. Rats. No good news right now about that one. But it can be enabled at query level with QUERYTRACEON. So maybe in a nonprod, disable globally and compare plans with and without QUERYTRACEON 2335?

Trace flag 2371 - can come out. It affects the threshold for stats update, but doesn't directly affect plan choice or quality.

Trace flags 2312 and 4199? Go ahead and replace them with the equivalent database scoped config options. Then, if you want to compare plans, you can use a pass-through database. Create a new database, and turn on Legacy CE and/or turn off query_optimizer_hotfixes. Compare plans in the other database to plans from the pass-through database (which fully qualify table and view references) in order to gauge the difference that optimizer hotfixes and/or CE version make.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.