sys.dm_db_tuning_recommendations (Transact-SQL)

Applies to: SQL Server 2017 (14.x) and later Azure SQL Database Azure SQL Managed Instance

Returns detailed information about automatic tuning recommendations. For more information, see Automatic tuning

For more information, see Monitoring and performance tuning in Azure SQL Database and Azure SQL Managed Instance.

In Azure SQL Database, dynamic management views can't expose information that would affect database containment or expose information about other databases the user has access to. To avoid exposing this information, every row that contains data that doesn't belong to the connected tenant is filtered out.

Column name	Data type	Description
name	nvarchar(4000)	Unique name of recommendation.
type	nvarchar(4000)	The name of the automatic tuning option that produced the recommendation, for example, FORCE_LAST_GOOD_PLAN
reason	nvarchar(4000)	Reason why this recommendation was provided.
valid_since	datetime2	The first time this recommendation was generated.
last_refresh	datetime2	The last time this recommendation was generated.
state	nvarchar(4000)	JSON document that describes the state of the recommendation. Following fields are available: - currentValue - current state of the recommendation. - reason - constant that describes why the recommendation is in the current state.
is_executable_action	bit	1 = The recommendation can be executed against the database via Transact-SQL script. 0 = The recommendation can't be executed against the database (for example: information only or reverted recommendation)
is_revertable_action	bit	1 = The recommendation can be automatically monitored and reverted by Database engine. 0 = The recommendation can't be automatically monitored and reverted. Most executable actions will be revertable.
execute_action_start_time	datetime2	Date the recommendation is applied.
execute_action_duration	time	Duration of the execute action.
execute_action_initiated_by	nvarchar(4000)	User = User manually forced plan in the recommendation. System = System automatically applied recommendation.
execute_action_initiated_time	datetime2	Date the recommendation was applied.
revert_action_start_time	datetime2	Date the recommendation was reverted.
revert_action_duration	time	Duration of the revert action.
revert_action_initiated_by	nvarchar(4000)	User = User manually unforced recommended plan. System = System automatically reverted recommendation.
revert_action_initiated_time	datetime2	Date the recommendation was reverted.
score	int	Estimated value/effect for this recommendation on the 0-100 scale (the larger the better)
details	nvarchar(max)	JSON document that contains more details about the recommendation. Following fields are available: planForceDetails - queryId - query_id of the regressed query. - regressedPlanId - plan_id of the regressed plan. - regressedPlanExecutionCount - Number of executions of the query with regressed plan before the regression is detected. - regressedPlanAbortedCount - Number of detected errors during the execution of the regressed plan. - regressedPlanCpuTimeAverage - Average CPU time (in micro seconds) consumed by the regressed query before the regression is detected. - regressedPlanCpuTimeStddev - Standard deviation of CPU time consumed by the regressed query before the regression is detected. - recommendedPlanId - plan_id of the plan that should be forced. - recommendedPlanExecutionCount- Number of executions of the query with the plan that should be forced before the regression is detected. - recommendedPlanAbortedCount - Number of detected errors during the execution of the plan that should be forced. - recommendedPlanCpuTimeAverage - Average CPU time (in micro seconds) consumed by the query executed with the plan that should be forced (calculated before the regression is detected). - recommendedPlanCpuTimeStddev Standard deviation of CPU time consumed by the regressed query before the regression is detected. implementationDetails - method - The method that should be used to correct the regression. Value is always TSql. - script - Transact-SQL script that should be executed to force the recommended plan.

Remarks

Information returned by sys.dm_db_tuning_recommendations is updated when database engine identifies potential query performance regression, and isn't persisted. Recommendations are kept only until the database engine is restarted. Use the sqlserver_start_time column in sys.dm_os_sys_info to find the last database engine startup time. Database administrators should periodically make backup copies of the tuning recommendation if they want to keep it after server recycling.

The currentValue field in the state column might have the following values:

Status	Description
Active	Recommendation is active and not yet applied. User can take recommendation script and execute it manually.
Verifying	Recommendation is applied by Database Engine and internal verification process compares performance of the forced plan with the regressed plan.
Success	Recommendation is successfully applied.
Reverted	Recommendation is reverted because there are no significant performance gains.
Expired	Recommendation has expired and can't be applied anymore.

JSON document in state column contains the reason that describes why is the recommendation in the current state. Values in the reason field might be:

Reason	Description
SchemaChanged	Recommendation expired because the schema of a referenced table is changed. New recommendation will be created if a new query plan regression is detected on the new schema.
StatisticsChanged	Recommendation expired due to the statistic change on a referenced table. New recommendation will be created if a new query plan regression is detected based on new statistics.
ForcingFailed	Recommended plan can't be forced on a query. Find the last_force_failure_reason in the sys.query_store_plan view to find the reason of the failure.
AutomaticTuningOptionDisabled	FORCE_LAST_GOOD_PLAN option is disabled by the user during verification process. Enable FORCE_LAST_GOOD_PLAN option using ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL) statement or force the plan manually using the script in the details column.
UnsupportedStatementType	Plan can't be forced on the query. Examples of unsupported queries are cursors and INSERT BULK statement.
LastGoodPlanForced	Recommendation is successfully applied.
AutomaticTuningOptionNotEnabled	Database Engine identified potential performance regression, but the FORCE_LAST_GOOD_PLAN option isn't enabled - see ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL). Apply recommendation manually or enable FORCE_LAST_GOOD_PLAN option.
VerificationAborted	Verification process is aborted due to the restart or Query Store cleanup.
VerificationForcedQueryRecompile	Query is recompiled because there is no significant performance improvement.
PlanForcedByUser	User manually forced the plan using sp_query_store_force_plan (Transact-SQL) procedure. Database engine won't apply the recommendation if user explicitly decided to force some plan.
PlanUnforcedByUser	User manually unforced the plan using sp_query_store_unforce_plan (Transact-SQL) procedure. Since the user explicitly reverted the recommended plan, database engine will keep using the current plan and generate a new recommendation if some plan regression occurs in future.
UserForcedDifferentPlan	User manually forced different plan using sp_query_store_force_plan (Transact-SQL) procedure. Database engine won't apply the recommendation if user explicitly decided to force some plan.
TempTableChanged	A temporary table that was used in the plan is changed.

Statistics in the details column don't show runtime plan statistics (for example, current CPU time). The recommendation details are taken at the time of regression detection and describe why Database Engine identified performance regression. Use regressedPlanId and recommendedPlanId to query Query Store catalog views to find exact runtime plan statistics.

Examples of using tuning recommendations information

Example 1

The following sample code gets the generated Transact-SQL script that forces a good plan for any given query:

SELECT name,
    reason,
    score,
    JSON_VALUE(details, '$.implementationDetails.script') AS script,
    details.*
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON(details, '$.planForceDetails') WITH (
        [query_id] INT '$.queryId',
        regressed_plan_id INT '$.regressedPlanId',
        last_good_plan_id INT '$.recommendedPlanId'
        ) AS details
WHERE JSON_VALUE(STATE, '$.currentValue') = 'Active';

Example 2

The following gets the generated Transact-SQL script that forces a good plan for any given query and additional information about the estimated gain:

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;

Example 3

The following gets the generated Transact-SQL script that forces a good plan for any given query and additional information that includes the query text and the query plans stored in Query Store:

WITH cte_db_tuning_recommendations
AS (
    SELECT reason,
        score,
        query_id,
        regressedPlanId,
        recommendedPlanId,
        current_state = JSON_VALUE(STATE, '$.currentValue'),
        current_state_reason = JSON_VALUE(STATE, '$.reason'),
        script = JSON_VALUE(details, '$.implementationDetails.script'),
        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
            )
    )
SELECT qsq.query_id,
    qsqt.query_sql_text,
    dtr.*,
    CAST(rp.query_plan AS XML) AS RegressedPlan,
    CAST(sp.query_plan AS XML) AS SuggestedPlan
FROM cte_db_tuning_recommendations AS dtr
INNER JOIN sys.query_store_plan AS rp
    ON rp.query_id = dtr.query_id
        AND rp.plan_id = dtr.regressedPlanId
INNER JOIN sys.query_store_plan AS sp
    ON sp.query_id = dtr.query_id
        AND sp.plan_id = dtr.recommendedPlanId
INNER JOIN sys.query_store_query AS qsq
    ON qsq.query_id = rp.query_id
INNER JOIN sys.query_store_query_text AS qsqt
    ON qsqt.query_text_id = qsq.query_text_id;

For more information about JSON functions that can be used to query values in the recommendation view, see JSON Support in Database Engine.

Permissions

Requires VIEW SERVER STATE permission in SQL Server.

Requires the VIEW DATABASE STATE permission for the database in Azure SQL Database.

Permissions for SQL Server 2022 and later

Requires VIEW SERVER PERFORMANCE STATE permission on the server.

Next steps

• Automatic tuning
• sys.database_automatic_tuning_options (Transact-SQL)
• sys.database_query_store_options (Transact-SQL)
• JSON Support
• sys.dm_os_sys_info (Transact-SQL)