A few weeks ago, we were talking to a new employee about how much time we spend with Query Store and they asked, “Why aren’t you using the Auto Tuning?”
THE WHAT NOW?!!!!
This awesome, Enterprise-only feature has been a bit of a trial and error for me.
Let’s start with turning it on, the only place I have found to turn it on is by using T-SQL:
ALTER DATABASE <DatabaseThatNeedsTuning> SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );
Now, there are a couple of cool things that we can check behind the scenes to see what is driving the auto-tuning. Once Auto-Tuning is enabled, it will collect information that can be viewed by running this query:
SELECT * FROM sys.dm_db_tuning_recommendations
The first column (name) is the QueryID with “PR_” added to it. I like to read through the columns, the “reason” the plan was chosen, the current “state” of the plan. When it was initiated and when it was reverted, all of this is fun for me to dig through and see what plans my system is finding that are better.
I also really like the Microsoft example with the JSON:
SELECT reason, score, script = JSON_VALUE(details, '$.implementationDetails.script'), planForceDetails.*, estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount) * (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage)/1000000, error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES','NO') FROM sys.dm_db_tuning_recommendations CROSS APPLY OPENJSON (Details, '$.planForceDetails') WITH ( [query_id] int '$.queryId', regressedPlanId int '$.regressedPlanId', recommendedPlanId int '$.recommendedPlanId', regressedPlanErrorCount int, recommendedPlanErrorCount int, regressedPlanExecutionCount int, regressedPlanCpuTimeAverage float, recommendedPlanExecutionCount int, recommendedPlanCpuTimeAverage float ) AS planForceDetails;
Now to the other stuff. It isn’t perfect. Sometimes I have to manually go in and pin plans that are better than what the system is finding. If I manually pin a plan, it will honor it and not unpin or try to find a better plan for that query. It has helped me spend a bunch less time on tuning, but since many of my servers are on Standard Edition I am still using Query Store a lot.
The song for this post is Oh My My by Blue October