SQL Server 2017 – Automatic Tuning

SQL Server 2016 introduced the Query Store, which makes a big difference in assisting us in monitoring and understanding what is happening in our SQL Server environments. If you are running SQL Server 2016, and you have enabled the Query Store, this feature captures the compile time and run time statistics of statements being executed. With this feature and the data being collected and held for us we can find out those execution plans that have regressed due to any number of reasons, and with this information we are able to identify a previous execution plan that performs better and force any future statement executions to utilize the force execution plan. With that in place we should then be looking at identifying what caused the regression and resolve the issue so that we are then able to unforce the execution plan thus allowing the DB engine to make the correct decisions for the statement being executed.

With the release of SQL Server 2017, this is now taken one (1) step further and the DB engine is looking at the information captured in the Query Store and making decisions for us when regression is detected and it will automatically force the last good plan.

How do we achieve this automatic tuning in SQL Server 2017?  First off we want to check to see if it is on or not:

-- Verify that actual state on FLGP is ON:

SELECT name, desired_state_desc, actual_state_desc, reason_desc
FROM sys.database_automatic_tuning_options;

Next we can enable the Automatic Tuning on the User Database:

-- Enable automatic tuning on the database:

ALTER DATABASE current
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON);

To use a some lyrics from The Madden Brothers – “We Are Done”.  Well for the setup anyway. With this in place the system will keep track of the executions of statements and determine for you if an execution plan needs to be forced. An execution plan will be forced if there has been some form of regression in statement being executed. Just because this feature is making our life easier for us to assist in maintaining the performance of our environment we need to understand when, what and why the execution plan regressed. With this information we need to undertake investigations to find out the When and Why, so we can determine the What to do to rectify before un-forcing the forced execution plan. To achieve this we need to continue with good DBA practices of monitoring our systems so that we are aware of what is going on.

Like all new features, before you blindly implement these into production ensure that you understand the ramifications and test in non-production environments and gain approval for the changes to be applied into production.