1

Currently, we are using -T9481. If we run DBCC TRACEOFF 9481 to use a new cardinality estimator, will effect immediately.

Suppose I have a query that runs frequently and has an execution plan in the cache before turning off T9481. if the same query executes after -T9481 turns off, will it generate a new execution plan immediately or will it use the existing query plan from the cache which was generated with the old cardinality estimator until it flushed out?

Try to understand whether the new query plan will generate for all quires once turnoff -T9481 or still used old plans generated before runoff -T9481 until these plans are flushed out.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
user19231705
  • 15
  • 2
  • You can easily test this can't you? – Stu Jan 16 '23 at 09:36
  • Seems like something you could easily check via the cached plan DMVs. – AlwaysLearning Jan 16 '23 at 09:42
  • Regardless of whether or not the OP can easily test this I think it is useful having the answer searchable online. With testing it is possible to make mistakes (like having additional white text in the query text between batches) that leads you to the wrong conclusion. – Martin Smith Jan 16 '23 at 11:01
  • Thank you Martin. I tested it before posting. The only issue some times its using old plan and sometimes it's generating a new plan. I am not quite sure. I did not find any MS documentation about this. After testing everything finalized to remove T9481, we came to know this behaviour. – user19231705 Jan 16 '23 at 12:55

1 Answers1

3

I tested the following setup on 2019.

CREATE TABLE T1(C1 INT);

INSERT INTO T1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9);

CREATE TABLE T2(C1 INT, C2 VARCHAR(MAX));

GO


CREATE VIEW V1
AS
SELECT T1.C1
FROM T1 LEFT OUTER JOIN T2 ON T1.C1 = T2.C1
        LEFT OUTER JOIN T2 T3 ON T3.C1 = T2.C1
        LEFT OUTER JOIN T2 T4 ON T4.C1 = T2.C1

With cardinality estimator model version 70 it estimates 9 rows will be returned, and with cardinality estimator model version 150 it estimates 20.0778 rows will be returned.

Running the following with "Actual execution plan enabled" the first 2 plans have CardinalityEstimationModelVersion="150" in the XML and the 20 row estimate and the second 2 plans CardinalityEstimationModelVersion="70" and the 9 row estimate.

This shows that after the trace flag is changed the execution plans from cache can still be reused.

DBCC freeproccache
DBCC TRACEOFF (9481, -1);
EXEC ('SELECT * FROM V1;')
DBCC TRACEON (9481, -1);
EXEC ('SELECT * FROM V1;')


dbcc freeproccache
EXEC ('SELECT * FROM V1;')
DBCC TRACEOFF (9481, -1);
EXEC ('SELECT * FROM V1;')

(The reason for using EXEC above is to ensure statement parsing and compilation is after the trace flag set and also to easily ensure that statements being executed are exactly the same including any white space)

Martin Smith
  • 438,706
  • 87
  • 741
  • 845