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.