I've recently upgraded my SQL Server 2012 to 2016 (Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows 10 Pro 6.3 (Build 16299: ) (Hypervisor) ).

Like many people I've experienced performance problems with queries that would take sub-1 second pre-upgrade now taking 3-4 seconds.

I've looked around and the short term resolution seems to be to switch the DB Compatibility level back to 2012 turn ON the Legacy Cardinality Estimation - however this doesn't seem to make any difference to my queries.

Can anyone advise on what else I need to configure/change to get it back pre-upgrade performance levels whilst we modify the queries themselves to make them performant under the new CE - I've attached the DB properties/options tab below for reference



enter image description here

| improve this question | | | | |
  • Did you update statistics after upgrading? – sepupic Feb 6 '18 at 10:48
  • 1
    Can you include the output of sp_Blitz @OutputType = 'markdown' as described in this post? That'll give us much more diagnostic info: brentozar.com/archive/2017/10/getting-help-slow-server – Brent Ozar Feb 6 '18 at 11:09
  • Did you do an inplace upgrade, or did you move to a new machine? – Stijn Wynants Feb 6 '18 at 11:30
  • 2
    sepupic, no I didnt update statistics, Brent Ozar that pack of info was amazingly helpful, Stijn I did an inplace upgrade. From all the questions asked and the info from sp_Blitz, I rebuilt all indexes, and regenerated all the stats and added a Clustered Index that was missing and its now back to normal performance. – Bagpuss Feb 6 '18 at 13:00
  • For info, I also switch Compatibility back to 2016, and turned OFF the Legacy CE – Bagpuss Feb 6 '18 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.