Every new version of SQL Server ships with exciting new features. One of the simplest but important features is
Automatic Tuning. Before covering this new feature, let’s analyze the old ways so we can understand what is really automated and what is not.
The old method
Usually, when your production database is having any type of performance issue, you resort to applying a scientific method to try to find out what is going on. The typical method consists of a few actions: you observe, ask questions, generate a hypothesis, perform an experiment, reach a conclusion and then report the results. A good example could be a slow report. You observe the behavior of the report, note down how long the duration is and try to compare with old performance baselines so you get a good idea of how big of a performance degradation you are dealing with. Then you ask questions such as: Was a patch applied lately? Did we change code or table structures? After that, you try to generate a hypothesis. One hypothesis could be that you have a missing index. To perform an experiment, you will need to review the execution plan of the query, check if you have bad operators such as Index Scans and then analyze how to fix them. You then reach a conclusion that a new index is needed and finally, share the new index script with the application team. In the end, you will be able to compare two execution plans. The old plan without the index having bad performance vs. the new plan with the index running faster.
The new alternative
With automatic tuning, SQL Server will track down Query Store data in the form of historical statistics of transactions. Based on this, it will be able to tell when a query is suddenly under-performing. For example, if a new stat breaks the average of a given stored procedure, it will be marked as a “regressed query,” meaning a TSQL that just started to present a bad behavior compared to old performance data. Automatic tuning will be able to compare the old plan (within the average) vs. the new plan (above the average) and identify if the difference is significant enough. If it is significant, then it will proceed and force the best plan possible. As you can see, we kind of skipped the scientific method as it was automated, and a solution rolled to production without human intervention.
Warning notes
If you resort to using automatic tuning, be sure to track down all the plans that are being forced by the feature. You might want to dig deeper and try to fix the issue by following the usual scientific method eventually. You can see the forced plan as a temporal band-aid; however, is important that you take care of the situation. You don’t want to end up with a web of forced plans which are difficult to navigate, don’t know what they are doing and are hard to duplicate and copy to lower environments such as QA or DEV.
How to enable
As a pre-requisite, Query Store must be turned on. Then, to enable automatic tuning, you will use this command: ALTER DATABASE <DBNAME> SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON ); In-depth details in the
MSDN link
Conclusions
Automatic tuning is available with enterprise edition and it is enabled per database. When using it, make sure to define a process on how to monitor forced plans and what actions you need to take afterwards. You can set up a customized alert to send an email every time a plan is automatically forced. Also,
extended events are available to track down and keep a log of all these changes.
