5

We recently upgraded the compatibility level (CL) of our production databases from 100 to 120.

Within a couple of days, users started complaining of increased wait times and general slowness. It was decided to revert the CL back down to 100. While things did get better, query times did not go back to historical averages.

We then cleared the plan cache to remove any 120 query plans still hanging around, and restarted the SQL Server service in configuration tools. After all this, our average times are still noticeably higher, though significantly better than when we initially switched to CL 120.

Is there anything else I can do, or is there a step I missed, to revert back to the old Cardinality Estimator?

I don't think this had any impact, but part of the same changes that we made was to enable CLR with the CL upgrade, and to disable it again when trying to revert back.

We are on SQL Server 2014 Service Pack 1, no CUs yet. We have a limited update schedule. CU 6 is scheduled for next month. We update stats and check for indexes to be rebuilt nightly.

Improve this question
0

1 Answer 1

Reset to default
3

Is there anything else I can do, or is there a step I missed, to revert back to the old Cardinality Estimator?

In SQL Server 2014, reverting to OLD CE can be done in 2 ways

  • Setting the database compatibility mode to less than 120.
  • (or) Enabling trace flag 9481 globally or at query level using option querytraceon

Now that you have reverted to old CE, your next steps in troubleshooting are finding out/isolating queries that are poorly performing - using XEvents or server side trace.

You should tune those queries.

My Suggestions :

  • Dont revert to old CE, instead isolate poor performing queries and tune them. Use option (querytraceon 9481) as query hint if you are not able to tune and want specific queries to use the OLD CE.

  • Since you are on SQL Server 2014 + SP1, you should enable trace flag 4199.

Also refer to

Improve this answer
1
  • 1
    Agree with this approach 100%. We recently migrated and were able to use the new CE which improved 90% of our queries. Several of them regressed terribly we simply hunted those down and enabled the Trace flag to revert those specifically.
    – Zane
    Aug 29, 2017 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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