Compare sql-2012 and sql-2016 settings

  • Hi everyone,

    W are just doing a migration from sql 2012 enterprise  to sql 2016 standard. This involves migrating lots of sql jobs running on sql 2012. I would like to make sure that performance doesn't degrades after we migrate. I would like to check and compare all configurations ( Sever  as well as DB level) on two machines. can someone help with quick sql script which I can ran on both machines and see if there is any differences?



  • is this a better way to do this?


    SELECT *

    FROM sys.configurations

    ---where value_in_use = 1

    ORDER BY name ;


  • You would want to look at default traceflags and other options also which can be done at the operating system level.

    I would recommend getting a trial version of Aireforge ( and running that against your old and new server to check all the settings both from the OS and Serverside are the same.


    One major thing to note is that if you are moving from SQL2012 to 2014 or above is the new cardinality estimator.  If performance is your major concern I would leave your database in 110 compatibility level, enable query store, let it go for a few weeks then if your are required to update the compatibility level switch it to 130 and track regressed queries.

    The new CE can make things faster, remain the same or drastically hinder performance for SOME queries

  • If the hardware is not the same, the same settings might not be correct. I would baseline a workload, with expected timings and then re-run that, being ready to tune once I get things set up.

    I'd also make sure I use sp_helprevlogin and ensure all logins get moved, along with databases. Think about linked servers and jobs as well, as those are the instance items that sometimes cause issues.

  • Thanks for your suggestion. I was aware of the cardinality factor and applied this already to new sql 2016 machine.

  • Most of the performance  is good but one of my SSIS package that is deployed in both old ( 2012) and new (2016) has significant performance differences.  Tht SSIS package uses lots of temp table. I also change  the cardinality to ON and compatibility level to 2012 (110) for tempdb since these temp tables are being stored in tempdb. I will wait for sometime and see if I see any performance benefits.

    Any other suggestions/recommendations ?

  • Did you get this sorted in the end?

    Also, did you perform an instance configuration comparison with Aireforge Compare as it should have highlighted any differences in settings, users and hardware.

    Solving data & infrastructure issues via xTEN[/url] and our application, Aireforge[/url].

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply