SQL Server Automatic Tuning and sys.dm_db_tuning_recommendations
SQL Server has made available in recent versions automatic tuning! Interested to find out how it can benefit your project? Read on to find out!
Join the DZone community and get the full member experience.Join For Free
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.
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.
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
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.
Published at DZone with permission of Grant Fritchey, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.