Earlier today in Ignite conference, Microsoft announced general availability of SQL Server 2017 on Oct 2. This is fantastic news. Lots of people are waiting for the latest version of SQL Server for a while. The matter of fact, many of my customers are using an earlier version of SQL Server are waiting to upgrade as soon as SQL Server 2017 is available to download. In this blog post I will talk about the feature I have been eagerly waiting to try out – Automatic Tuning.
Just a year ago, MS has released SQL Server 2016 and now they are releasing SQL Server 2017. MS is indeed on a rapid development cycle where they are releasing SQL Server versions. There are two opinions about rapid releases.
There is one set of group who is pretty excited as they get to experiment with new features of SQL Server without waiting for years. Organizations who have signed up with Software Assurance with MS also get this latest version of SQL Server for free. Whereas there is another set of individuals who do not like to upgrade as frequently. They believe in a stable, robust product which does not change that often.
Here is the question for you-
Do you like rapid development cycle for SQL Server?
Now let us talk about the most discussed feature of SQL Server 2017- Automatic Tuning.
Everybody has their own favorite feature in SQL Server. My favorite feature in SQL Server is an Automatic Tuning. I know the name of this feature is very exciting and many would think this feature as a silver bullet to their performance problems. In reality, this feature is actually a combination of Automatic Plan Correction and Automatic Index Management.
Automatic Plan Correction
SQL Server evaluates many different execution plans before it selects the most optimal plan and executes the query. Sometimes, SQL Server changes the query execution plan and rarely the most optimal execution plan is not included in the execution plan. In this scenario query will start performing poorly. This is called query plan regression. In this scenario, SQL Server applies Automatic Plan Correction and selects the optimal execution plan from the past which has historically provided the best performance for the query. This entire process is called Automatic Plan Correction.
Automatic Index Management
This feature is currently available in Azure SQL database and now will be available in SQL Server 2017. SQL Server engine learns about workload and creates optimal indexes which are needed for your query. It also identifies duplicate indexes along with unused indexes and drops it for you.
On a lighter note this feature reminds me the scenario when one of my customers every single day created all the missing indexes and dropped unused indexes based my earlier script. Instead of taking top few rows, he created every single index my query suggested and dropped as well. He was in a whirlpool of performance problems.
My Thoughts on Automatic Tuning
Honestly, this feature has got all my attention. I do Comprehensive Database Performance Health Check for living and this feature is going to make my life easier big time. Automatic Tuning is a very interesting word, I hope it performs as well as its name. I believe this feature will take care of the two big things – a) Plan Regression and b) Index Tuning. I think this will allow SQL Server Performance Expert like to focus on many other high impact areas.
I do not believe that anyone is currently thinking that after upgrading to SQL Server 2017 their all the performance problems will go away due to automatic tuning. This particular feature is indeed will play a very important role but there will be always a need of SQL Server DBA who will be focusing on SQL Server Performance.
Let me know your thoughts in the comment sections, I would love to know your thought about this feature.
Reference: Pinal Dave (https://blog.sqlauthority.com)
10 Comments. Leave new
will be available for users to download from Microsoft website at Oct 2 ?
The missing index feature in SQL server did not take into consideration that there are existing indexes with the same keys , it will recommend that index again with additional key columns. I hope the automatic tuning feature does check for existing indexes and suggests refactoring the existing index rather than creating completely new indexes
Please correct the picture, it says twice automatic plan correction twice.
Fixed – thanks for pointing this error out.
These are very interesting features in MS SQL server but there must be lots of limitations just like in-memory feature introduced in MS SQL 2014 release.
And they should take extra time but come up with an stable release rather than showing up a new release every year.
We are still not able to completely utilize the in memory feature due to it’s limitations.
is it also releasing for windows or only or Linux only this time ?
It is for Windows and Linux both!
> Do you like rapid development cycle for SQL Server?
Yes, of course, but I’m left wondering why the need to rev the major release from 2016 to 2017? Couldn’t they have accomplished the same goal with a service pack for SQL Server 2016? Service Packs are easier to “sell” to management versus major upgrades.
Hello Dave,
it’s the first time I comment on your blog, but I’ve been following you for some time.
I was wandering if you have any updated insights on the effectiveness of automatic tuning.
I’m a developer of a cloud application using azure sql database.
We’ve been using the sql azure missing index advisor (index get also automatically applied) and had positive results so long (with varying effectiveness).
Now I’ve learnt about the automatic plan correction and was planning to enabling it.
I’m aware that theese kind of features can’t replace a proper DBA, but we would like to get the most from this kind of tools, and I would like to know from your experience if it’s a good thing to enabled in order to get a positive impact, or if they may actually cause any kind of performance regression.
Thank you in advance,
Alberto.