Will setting cardinality estimator trace flag 9481 on/off automatically recompile affected plans?

Question

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.

Answer 1

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)