We upgraded our SQL 2012 to SQL 2016 SP1. We were under the impression that there will be lot of performance improvements in terms of query execution time and other applications response time etc.. We haven't seen any improvement.
Question 1: Does SQL 2016 upgrade the database to SQL 2016 apart from upgrading SQL database engine upgrade.
Question 2: We are not seeing the Database compatibility level changed automatically after the upgrade to 130. Does the upgrade change the compatibility level or we have to change manually to 130?.
Thanks in advance for the reply.
Hi Venkat,
There are a lot of performance improvements with SQL Server 2016. Watch Bob Ward's talk at https://groupby.org/2016/11/sql-server-2016-it-just-runs-faster/
To answer your first question, if you mean SSMS 2016, that is a separate download from installing the SQL Server 2016 database engine. I guess I'm not sure what you're asking. Updating the SQL Server instance does not automatically upgrade any databases on that instance.
Again, for your second question, the database compatibility level does not get upgraded when you upgrade the database engine. You would need to change that yourself (after testing, of course).
Mike
Thanks a lot for the reply Mike.
You are right the question1 was about the database.
I was told we have to detach and attach the database to get it up to the SQL 2016 level after the engine upgrade and then we may see the performance improvements. Not sure if that is correct?.
-Venkat
Venkat,
I've not heard that before. Whether you use the detach and attach method or backup and restore, you'll need to update the compatibility levels to fully realize the performance enhancements of SQL Server 2016. Also, it's important to note that not every single database will benefit from the move to 2016. In some cases, it'll help; in others, it'll stay the same; in some very rare cases, it'll be worse.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com