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)