Careful Testing the 2014 Cardinality Estimator with Trace Flags

When you migrate to SQL Server 2014, rather than turning on the new cardinality estimator right away, I recommend sticking with the old cardinality estimator initially by leaving your database in SQL Server 2012 compatibility mode. Let the database settle down for a few weeks, let folks air out their grievances, and give yourself some time to make sure the system is working at least as well as the old system.

Once you’re on 2014, you can check out the new CE’s execution plans by playing around with trace flag 2312. That flag turns on the new CE in one of two ways:

  1. At the query level using OPTION (QUERYTRACEON 2312), or
  2. At the session level using DBCC TRACEON(2312)

The latter is easier if you have to test a lot of queries at once, or if you need to test a stored procedure. Just turn on the trace flag, and it’s on for only your session.*

* – I lied. It’s not.

If you set trace flag 2312 for your session, and then run a stored procedure whose execution plan isn’t already cached, that stored procedure gets compiled using the new cardinality estimator, and the resulting plan is put into cache.

Subsequent sessions will use that plan even if they don’t have trace flag 2312 enabled.

Here’s how to prove it on a SQL Server 2014 server in a 2012 (or earlier) compat mode database:

  1. Pick a rarely-executed stored procedure. (If you’re doing this in production, don’t use a stored procedure that will cripple your server if it happens to get compiled with the 2014 CE.)
  2. Recompile it using sp_recompile ‘myprocname’.
  3. In a new SSMS window, run DBCC TRACEON(2312) to enable the new CE in your session.
  4. Click Query, Include Actual Query Plans.
  5. Execute the stored procedure.
  6. Right-click on the query plan and click Show Execution Plan XML.
  7. In the first few lines, look for the string “CardinalityEstimationModelVersion” – if it’s 120, that’s the new CE. 70 is the old one. You’ll see 120 as shown in the example below.
  8. Close that query window, and start a new one. Run DBCC TRACESTATUS to verify that you don’t get a line for 2312, indicating you’re still on the old CE.
  9. Make sure Query, Include Actual Query Plans is on.
  10. Execute the stored procedure again.
  11. Right-click on the query plan and click Show Execution Plan XML. Even though you’re in the old compat mode, you’ll be looking at a 120-level CE plan.
  12. Recompile that stored proc using sp_recompile ‘myprocname’ just to make sure it gets out of cache.
Execution plan XML - note the CardinalityEstimationModelVersion at the top right.

Execution plan XML – note the CardinalityEstimationModelVersion at the top right.

So how do you test stored procedures with the new CE?

You could test on a different server with the databases set to the new 2014 compatibility level, but of course when you’re testing execution plans and query performance, the servers need to be identical. That’s not always possible.

If you have to test both the old & new CEs on the same server, you’ll need to create new stored procedures (like myprocname_2014) with OPTION (QUERYTRACEON 2312) on every single query in the stored proc – or, at least, all of the queries where you’re concerned about performance variances.

Kendra says: Well, there’s always plan guides. Kidding! I’M JUST KIDDING!

Previous Post
What Permissions does QUERYTRACEON Need?
Next Post
Faster queries using narrow indexes and CROSS APPLY

28 Comments. Leave new

  • Good article, and another argument for a dedicated test environment. It doesn’t need to necessarily be exactly the same as your production environment, but an unchanging test environment lets you do A-B tests like this without impacting production.

    We’re doing exactly this to test the new CE:

    Install SQL 2014 on test server
    Restore a DB that’s set to CL 110
    Run our workload and get the numbers recorded

    Then restore that DB again
    Set the CL to 120
    If there are notes about foreign keys/indexes getting disabled, we re-enable them
    Then run the workload again, get the numbers recorded

    Then compare! Since it’s all on the same test server the only thing that changes is the CL.

    Reply
  • Could you not just get the plan handle for this proc and delete it? Then the next run would use the old CE?

    DBCC FREEPROCCACHE (120_Plan_Handle)

    Reply
  • We ran into a heck of a lot of problems with the 2014 cardinality estimator when we started to upgrade to 2014 from 2008. Especially with more complex workloads such as what usually runs in our data warehouse. Ultimately we went forward with setting the databases at 120 compatibility level and setting the trace flag 9481 globally.

    For more info on trace flag 9481
    https://support.microsoft.com/en-us/kb/2801413

    Reply
  • SQL product team has fixed some CE related performance issues with SQL2014 SP1.

    “FIX: Query performance issues when new cardinality estimator is enabled in SQL Server 2014”
    https://support.microsoft.com/en-us/kb/3044519

    Reply
  • Hi Brent,
    I’ve read that trace flag 4199 must be used in order to turn “ON” query optimizer new features & bug fix on new SQL version. Otherwise SQL is not using them.

    I guess new CE is not in this list… or maybe I am a little confused.

    Thanks
    Alberto

    Reply
  • Here is my un-success story. We installed SQL Server 2014 without enough testing. Basically because the DBA team was dragging on forever and didn’t do much of a job anyway. Dev team had like 2 weeks to test everything because of the deadline. Anyway, we are stuck with 2014 and it’s killing me with its cardinality estimator. Basically, the logic behind it seems to be that every inner join is a filter. Lookups? Never heard of them! If you have a join the estimated number of rows is divided by 10. And I am working with a warehouse. I have a transformation query that returns 40M rows. SQL 2014 expects 15K. Of course, the plan is infested with nested loops and lazy spools, and query runs now for 20 minutes where it used to be 5. Not one query. All of my large queries have suffered. Turning back the estimator to 2012 solves the problem but requires sysadmin privileges which I don’t have on production server. I wish there was a real switch for the estimator where it could be downgraded without losing the rest of 2014 functionality.

    Reply
  • Any idea what the algorithm is for 2016?

    For two columns, “lowest cardinality * SQRT(next highest cardinality) * cardinality” doesn’t seem to be it, in my tests, anyway.

    I have had a scout around, but nada.

    Reply
  • Chris Carson
    July 27, 2016 5:24 pm

    I’ve run into an issue with the Cardinality Estimator. Microsoft says the bug was fixed in SP1, but they didn’t mention that trace flag 4199 was required to activate the fix.

    Upgraded test server to 2014 SP2, ran ProblemQuery and cardinality estimator problem was present. Made the problem go away by using OPTION( QUERYTRACEON 9481 ).

    Problem shows as *fixed* when using OPTION( QUERYTRACEON 4199 ) — there was a substantial difference in the plans using when using TF 9481 and TF 4199.

    If I understand it correctly, TF 9481 disables the new CE, but TF 4199 enables the new and improved CE installed with Service Pack 1.

    Reply
    • Chris Carson
      July 27, 2016 5:27 pm

      OK.. my mistake. Microsoft *does* mention using TF 4199 to activate the fixed cardinality estimator released in SP1.

      Reply
  • Gursanjit Singh Bajwa
    May 29, 2018 9:58 am

    I’ve been testing SQl 2016 and SQl 2017 with a standard Information_Schema query. Absolutely fails on SQl 2016/2017 but works fine on SQL 2014. Our production environment cannot upgrade because of this. This is the standard query used by an adhoc reporting tool. It takes a few seconds in SQl 2014, but over a few minutes in each of 2016 and SQL 2017. Only grace is Trace flag 9481 but cannot do that for the entire production system, unless there are code changes for just adhocs.

    SELECT INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.TABLE_SCHEMA, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.TABLE_NAME, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.COLUMN_NAME as CONSTRAINT_KEYS, INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA as REFERENCE_SCHEMA, INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME as REFERENCE_TABLE, unique_usage.COLUMN_NAME as REFERENCE_COLUMN FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ON INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.CONSTRAINT_NAME = INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE unique_usage
    ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = unique_usage.CONSTRAINT_NAME

    Reply
  • GURSANJIT SINGH BAJWA
    May 29, 2018 12:27 pm

    The performance is most likely because we have about 1300 Tables in our schema and 800 views. Is there a way you can test it with these number of objects? We have done extensive testing on our SQL 2014, SQL 2016 and SQL 2017. Only the trace flag 9481 seems to help.

    Reply
  • GURSANJIT SINGH BAJWA
    May 29, 2018 1:12 pm

    Thanks Brent! I wasn’t trying to get free consulting. I was just raising an issue with Microsoft’s SQL Server 2016 and SQL 2017, the queries on these standard system schema views are using incorrect plans. It quickly becomes evident with the number of objects. Appreciate your input as always!

    Reply
  • We have changed database compatibility mode to 120 in UAT first and it’s working as expected however after a month when we made same changes in production, CPU utilization went to 100% and our application few workflow went down. many SP calls timed out .

    As we can’t afford much down time, we reverted back the changes, database compatibility mode to 110, after the change suddenly CPU utilization came back to 45%.

    We want to set database compatibility mode to 120 to improve many SP performance, which we are facing now.

    Any suggestion would be great help!!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}