Sql-server – Reverting Compatibility Mode 120 to 100

compatibility-levelsql serversql server 2014

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.

Best Answer

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

Related Question