January 24, 2017 at 2:21 pm
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.
January 24, 2017 at 2:32 pm
Venkat Palaniappan - Tuesday, January 24, 2017 2:21 PMWe 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
Mike Scalise, PMP
https://www.michaelscalise.com
January 24, 2017 at 2:43 pm
Mike Scalise - Tuesday, January 24, 2017 2:32 PMVenkat Palaniappan - Tuesday, January 24, 2017 2:21 PMWe 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
January 24, 2017 at 2:52 pm
Venkat Palaniappan - Tuesday, January 24, 2017 2:43 PMMike Scalise - Tuesday, January 24, 2017 2:32 PMVenkat Palaniappan - Tuesday, January 24, 2017 2:21 PMWe 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
January 24, 2017 at 5:20 pm
Venkat Palaniappan - Tuesday, January 24, 2017 2:21 PMWe 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.
Improvements may vary - but many will only show when the compatibility level is 130.
Changing it to 130 can cause some queries to perform worst so it is advisable to try it out.
In the case that most perform worst you can try and set the query cardinality estimation to legacy
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON
see https://msdn.microsoft.com/en-us/library/mt629158.aspx%5B/url%5D for more options at database level.
Doing this will allow the benefits of level 130 while using the old query model
My advise is that you use backup/restore onto your 2016 instance and then change the compatibility level to 130 and do all require testing.
As far as I remember when you either attach or restore the database onto the 2016 there will be some things changed - that being the case and even if you don't change it to level 130 you will most likely then be unable to attach it again to 2012 hence the use of a backup.
January 25, 2017 at 2:53 am
Venkat Palaniappan - Tuesday, January 24, 2017 2:21 PMWe 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.
SQL Server 2014 onwards employs the new cardinality estimator. In some caees you may see a performance dip. Changing the compatability levekl is one way to enable the new estimator so test carefully before just upgrading
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply