SQL Server Automatic Tuning and sys.dm_db_tuning_recommendations

In Azure SQL Database for quite some time, and now available in SQL Server 2017, Microsoft has put a lot of the knowledge they’ve gleaned from running more databases that any of the rest of us ever will in order to work with automatic tuning.

Automatic Tuning

The core of automatic tuning at this point in time (because I’m sure it’s going to evolve) is the ability of the query engine to spot when a query has generated a new plan that is causing performance to degrade. This is known as a regression in the plan. It comes from bad parameter sniffing, changes in statistics, cumulative updates, or, the big notorious one, the cardinality estimator introduced in SQL Server 2014 (it’s been almost four years; I’m not calling it new anymore). Spotting a plan regression prior to SQL Server 2016 and the introduction of the Query Store was a major pain. However, now, it’s easy. You can spot them by reading the data collected. Further, Microsoft can spot them by reading the data collected — and it does!

If the engine sees that a plan is causing a regression (you have to have Query Store enabled for this), then it will write out a suggestion for fixing it to the new DMV, sys.dm_db_tuning_recommendations. If you further enable automatic tuning on your database, then SQL Server can automatically substitute the last good plan for you, fixing the problem. Further, SQL Server monitors this fix and if, over time, it’s clear that the forced plan is causing problems, it will unforce the plan — again, automagically.

Further, you can track the behavior of the automatic tuning by querying sys.dm_db_tuning_recommendations. It updates if it’s forced a plan or unforced a plan, and gives you all the reasons why.

sys.dm_db_tuning_recommendations

Querying the DMV itself is a snap. The issue comes from a few of the columns. The state column and the details column are both stored as JSON data. For those who know me well, you know I just had to spit on the floor after typing that. No, I do not have anything against JSON. I just hate how it has become a magic solution for all problems, everywhere. However, it has utility and programmers love it, so we’re going to be seeing it, evidently, everywhere.

JSON queries in SQL Server are pretty easy — much, much easier than XML queries — so you can readily pull the information you need. There’s only one snag: the data that returns from sys.dm_db_tuning_recommendations doesn’t include anything other than the plan_id values and the query_id value. To put together a meaningful report, you’ll have to do a little work. I’ve put together an initial example:

WITH DbTuneRec
AS (SELECT ddtr.reason,
           ddtr.score,
           pfd.query_id,
           pfd.regressedPlanId,
           pfd.recommendedPlanId,
           JSON_VALUE(ddtr.state,
                      '$.currentValue') AS CurrentState,
           JSON_VALUE(ddtr.state,
                      '$.reason') AS CurrentStateReason,
           JSON_VALUE(ddtr.details,
                      '$.implementationDetails.script') AS ImplementationScript
    FROM sys.dm_db_tuning_recommendations AS ddtr
        CROSS APPLY
        OPENJSON(ddtr.details,
                 '$.planForceDetails')
        WITH (query_id INT '$.queryId',
              regressedPlanId INT '$.regressedPlanId',
              recommendedPlanId INT '$.recommendedPlanId') AS pfd)
SELECT qsq.query_id,
       dtr.reason,
       dtr.score,
       dtr.CurrentState,
       dtr.CurrentStateReason,
       qsqt.query_sql_text,
       CAST(rp.query_plan AS XML) AS RegressedPlan,
       CAST(sp.query_plan AS XML) AS SuggestedPlan,
       dtr.ImplementationScript
FROM DbTuneRec AS dtr
    JOIN sys.query_store_plan AS rp
        ON rp.query_id = dtr.query_id
           AND rp.plan_id = dtr.regressedPlanId
    JOIN sys.query_store_plan AS sp
        ON sp.query_id = dtr.query_id
           AND sp.plan_id = dtr.recommendedPlanId
    JOIN sys.query_store_query AS qsq
        ON qsq.query_id = rp.query_id
    JOIN sys.query_store_query_text AS qsqt
        ON qsqt.query_text_id = qsq.query_text_id;

That should cover most people in most situations. You can see the query text and the plans themselves, as well as some of the more interesting bits of data from sys.dm_db_tuning_recommendations.

Conclusion

Automatic tuning really is an excellent new feature. Even if you don’t use it, you can take advantage of the information in sys.dm_db_tuning_recommendations as a starting point for adjusting your own plans. I’d suggest keeping an eye on the automatic tuning feature because it’s likely to expand to cover more of the mechanical aspects of tuning a query.