July 6, 2020 at 3:39 am
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?
Thanks,
Rohit
July 6, 2020 at 4:24 am
is this a better way to do this?
SELECT *
FROM sys.configurations
---where value_in_use = 1
ORDER BY name ;
GO
July 6, 2020 at 9:03 am
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 (https://www.aireforge.com/) 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
July 6, 2020 at 2:19 pm
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.
July 6, 2020 at 5:20 pm
Thanks for your suggestion. I was aware of the cardinality factor and applied this already to new sql 2016 machine.
July 8, 2020 at 12:48 am
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 ?
September 26, 2020 at 10:15 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply