Sys.dm_db_tuning_recommendations Makes Suggestions if Automatic Tuning is Not Enabled

I naturally think about the new sys.dm_db_tuning_recommendations DMV when I’m working with the new Automatic Tuning feature in SQL Server 2017.

But I came across a post by Grant Fritchey recently, in which he remarked in the conclusion…

 Even if you don’t use [Automatic Tuning], you can take advantage of the information in sys.dm_db_tuning_recommendations as a starting point for adjusting your own plans.

WHAAAAAAT?

I had assumed that recommendations would only show up in  sys.dm_db_tuning_recommendations if I’d enabled automatic tuning for the database. I hadn’t even thought to test looking at the DMV if Query Store was set up but Automatic Tuning was disabled.

What does the recommendation look like if tuning is disabled?

I had to know, so off to my test database! I did a fresh restore of my database, configured and enabled Query Store, and then made sure that automatic tuning was off:

ALTER DATABASE current
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = OFF);
GO

I verified that at this point,  sys.dm_db_tuning_recommendations didn’t have any rows.

Then I ran a workload where a query gets a “faster” plan that executes 102 times, suffers a recompile (whoops!) and then runs again with a “slow” plan that executes another 102 times.

Sure enough, I got a recommendation

Even though automatic tuning wasn’t enabled, SQL Server picked up on the performance changes. I got a recommendation in sys.dm_db_tuning_recommendations.

• reason: Average query CPU time changed from 2127.84ms to 66291.9ms
• state: {“currentValue”:“Active”,“reason”:“AutomaticTuningOptionNotEnabled”}

The details also include the query id in question, and the plan_id of the “fast plan”.

Do the suggestions show up in Standard Edition?

Automatic Tuning is an Enterprise Edition feature in SQL Server.

If you’re like me, your next question was whether suggestions might appear in Standard Edition, even if you can’t formally enable Automatic Tuning.

On my Standard Edition test instance, I can query sys.dm_db_tuning_recommendations, but no suggestions appear when I run my sample workload.

Here’s the workaround for Standard Edition

Don’t worry, Query Store still has your back, you just have to find your own suggestions.

• Open the new ‘Queries with High Variation’ built in report
• Set the graph to Metric: CPU Time (ms), Based on Std Dev

My problem query pops right to the top of the list with its fast and slow plans right in the graph.

This does not suck!

It turns out I really should have already known this…

Erin Stellato mentioned this in her excellent session on Query Store and Automatic Tuning with Dejan Krakovic at the SQL PASS Summit last year. It just didn’t sink in the first time I heard it!

I love a good suggestion!

I agree with Grant: I think the automatic tuning suggestions are a great place to start.

Even if you like to have the Automatic Tuning enabled, the fact that it’s adjusting plans for you should be a trigger to looking at the queries and finding a way to keep the plans from being so volatile and needing adjustment.

One note: suggestions disappear when the instance is restarted or the database goes offline – so if you’d like to keep ‘em, you need to query them out of sys.dm_db_tuning_recommendations and persist them somewhere yourself.