sys.dm_db_tuning_recommendations (Transact-SQL)

Se aplica a: SQL Server 2017 (14.x) y versiones posteriores de Azure SQL Database Azure SQL Instancia administrada

Devuelve información detallada sobre las recomendaciones de ajuste automático. Para obtener más información, consulte Ajuste automático.

Comentarios

La información devuelta por sys.dm_db_tuning_recommendations se actualiza cuando el motor de base de datos identifica la posible regresión del rendimiento de las consultas y no se conserva. Las recomendaciones solo se conservan hasta que se reinicie el motor de base de datos. Use la columna sqlserver_start_time en sys.dm_os_sys_info para encontrar la hora del último inicio del motor de base de datos. Los administradores de bases de datos deben realizar periódicamente copias de seguridad de la recomendación de optimización si quieren mantenerlo después del reciclaje del servidor.

El currentValue campo de la state columna puede tener los siguientes valores:

El documento JSON de state la columna contiene el motivo por el que se describe por qué es la recomendación en estado actual. Los valores del campo de motivo pueden ser:

Las estadísticas de la details columna no muestran las estadísticas del plan en tiempo de ejecución (por ejemplo, la hora de CPU actual). Los detalles de la recomendación se toman en el momento de la detección de regresión y describen por qué Motor de base de datos regresión de rendimiento identificada. Use regressedPlanId y recommendedPlanId para consultar Almacén de consultas vistas de catálogo para buscar estadísticas exactas del plan de tiempo de ejecución.

Ejemplos de uso de la información de recomendaciones de optimización

Ejemplo 1

El código de ejemplo siguiente obtiene el script transact-SQL generado que fuerza un buen plan para cualquier consulta determinada:

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';

Ejemplo 2

A continuación se obtiene el script transact-SQL generado que fuerza un buen plan para cualquier consulta determinada e información adicional sobre la ganancia estimada:

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;

Ejemplo 3

A continuación se obtiene el script transact-SQL generado que fuerza un buen plan para cualquier consulta determinada e información adicional que incluya el texto de la consulta y los planes de consulta almacenados en Almacén de consultas:

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;

Para obtener más información sobre las funciones JSON que se pueden usar para consultar valores en la vista de recomendaciones, consulte Compatibilidad con JSON en Motor de base de datos.

Permisos

Requiere VIEW SERVER STATE permiso en SQL Server.

Requiere el VIEW DATABASE STATE permiso para la base de datos en Azure SQL Database.

Permisos para SQL Server 2022 y versiones posteriores

Requiere el permiso VIEW SERVER PERFORMANCE STATE en el servidor.

Pasos siguientes

• Ajuste automático
• sys.database_automatic_tuning_options (Transact-SQL)
• sys.database_query_store_options (Transact-SQL)
• Compatibilidad con JSON
• sys.dm_os_sys_info (Transact-SQL)