sys.dm_db_tuning_recommendations (Transact-SQL)

Artículo
05/23/2023

Se aplica a: SQL Server 2017 (14.x) y versiones posteriores Azure SQL Database Azure SQL Managed Instance

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

Para obtener más información, consulte Supervisión y ajuste del rendimiento en Azure SQL Database y Azure SQL Managed Instance.

En Azure SQL Base de datos, las vistas de administración dinámica no pueden exponer información que afecte a la contención de la base de datos ni exponer información sobre otras bases de datos a las que el usuario tiene acceso. Para evitar exponer esta información, se filtran todas las filas que contienen datos que no pertenecen al inquilino conectado.

Nombre de la columna	Tipo de datos	Descripción
name	nvarchar(4000)	Nombre único de la recomendación.
type	nvarchar(4000)	Nombre de la opción de ajuste automático que generó la recomendación, por ejemplo, `FORCE_LAST_GOOD_PLAN`
reason	nvarchar(4000)	Motivo por el que se proporcionó esta recomendación.
valid_since	datetime2	La primera vez que se generó esta recomendación.
last_refresh	datetime2	La última vez que se generó esta recomendación.
state	nvarchar(4000)	Documento JSON que describe el estado de la recomendación. Los campos siguientes están disponibles: - `currentValue` : estado actual de la recomendación. - `reason` : constante que describe por qué la recomendación está en estado actual.
is_executable_action	bit	1 = La recomendación se puede ejecutar en la base de datos a través del script de Transact-SQL. 0 = La recomendación no se puede ejecutar en la base de datos (por ejemplo: solo información o recomendación revertida)
is_revertable_action	bit	1 = El motor de base de datos puede supervisar y revertir automáticamente la recomendación. 0 = La recomendación no se puede supervisar y revertir automáticamente. La mayoría de las acciones ejecutables serán revertibles.
execute_action_start_time	datetime2	Fecha en que se aplica la recomendación.
execute_action_duration	time	Duración de la acción de ejecución.
execute_action_initiated_by	nvarchar(4000)	`User` = Plan forzado manualmente por el usuario en la recomendación. `System` = Recomendación aplicada automáticamente por el sistema.
execute_action_initiated_time	datetime2	Fecha en que se aplicó la recomendación.
revert_action_start_time	datetime2	Fecha en que se revierte la recomendación.
revert_action_duration	time	Duración de la acción de reversión.
revert_action_initiated_by	nvarchar(4000)	`User` = Plan recomendado no aplicado manualmente por el usuario. `System` = Recomendación de reversión automática del sistema.
revert_action_initiated_time	datetime2	Fecha en que se revierte la recomendación.
score	int	Valor/efecto estimado para esta recomendación en la escala de 0 a 100 (cuanto mayor sea)
details	nvarchar(max)	Documento JSON que contiene más detalles sobre la recomendación. Los campos siguientes están disponibles: `planForceDetails` - `queryId` : query_id de la consulta con regresión. - `regressedPlanId` - plan_id del plan con regresión. - `regressedPlanExecutionCount` - Número de ejecuciones de la consulta con plan con regresión antes de que se detecte la regresión. - `regressedPlanAbortedCount` - Número de errores detectados durante la ejecución del plan con regresión. - `regressedPlanCpuTimeAverage` - Promedio de tiempo de CPU (en micro segundos) consumido por la consulta con regresión antes de que se detecte la regresión. - `regressedPlanCpuTimeStddev` - Desviación estándar del tiempo de CPU consumido por la consulta con regresión antes de que se detecte la regresión. - `recommendedPlanId` - plan_id del plan que se debe forzar. - `recommendedPlanExecutionCount`- Número de ejecuciones de la consulta con el plan que se debe forzar antes de que se detecte la regresión. - `recommendedPlanAbortedCount` - Número de errores detectados durante la ejecución del plan que se debe forzar. - `recommendedPlanCpuTimeAverage` - Promedio de tiempo de CPU (en micro segundos) consumido por la consulta ejecutada con el plan que se debe forzar (calculado antes de que se detecte la regresión). - `recommendedPlanCpuTimeStddev` Desviación estándar del tiempo de CPU consumido por la consulta con regresión antes de que se detecte la regresión. `implementationDetails` - `method` : método que se debe usar para corregir la regresión. El valor siempre `TSql`es . - `script` - Script de Transact-SQL que se debe ejecutar para forzar el plan recomendado.

Observaciones

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 mantienen hasta que se reinicia 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:

Estado	Descripción
`Active`	La recomendación está activa y aún no se ha aplicado. El usuario puede tomar el script de recomendación y ejecutarlo manualmente.
`Verifying`	La recomendación se aplica mediante el motor de base de datos y el proceso de comprobación interno compara el rendimiento del plan forzado con el plan con regresión.
`Success`	La recomendación se aplica correctamente.
`Reverted`	Se revierte la recomendación porque no hay mejoras significativas en el rendimiento.
`Expired`	La recomendación ha expirado y ya no se puede aplicar.

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

Motivo	Descripción
`SchemaChanged`	Recomendación expirada porque se cambia el esquema de una tabla a la que se hace referencia. Se creará una nueva recomendación si se detecta una nueva regresión del plan de consulta en el nuevo esquema.
`StatisticsChanged`	La recomendación expiró debido al cambio estadístico en una tabla a la que se hace referencia. Se creará una nueva recomendación si se detecta una nueva regresión del plan de consulta en función de las nuevas estadísticas.
`ForcingFailed`	No se puede forzar el plan recomendado en una consulta. Busque en `last_force_failure_reason` la vista sys.query_store_plan para encontrar el motivo del error.
`AutomaticTuningOptionDisabled`	`FORCE_LAST_GOOD_PLAN` El usuario deshabilita la opción durante el proceso de comprobación. Habilite la `FORCE_LAST_GOOD_PLAN` opción mediante la instrucción ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL) o fuerce el plan manualmente mediante el script de la `details` columna.
`UnsupportedStatementType`	El plan no se puede forzar en la consulta. Algunos ejemplos de consultas no admitidas son cursores y `INSERT BULK` instrucciones.
`LastGoodPlanForced`	La recomendación se aplica correctamente.
`AutomaticTuningOptionNotEnabled`	Motor de base de datos identificó una posible regresión de rendimiento, pero la `FORCE_LAST_GOOD_PLAN` opción no está habilitada; consulte ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL). Aplique la recomendación manualmente o habilite `FORCE_LAST_GOOD_PLAN` la opción.
`VerificationAborted`	El proceso de verificación se anula debido al reinicio o Almacén de consultas limpieza.
`VerificationForcedQueryRecompile`	La consulta se vuelve a compilar porque no hay ninguna mejora significativa del rendimiento.
`PlanForcedByUser`	El usuario ha forzado manualmente el plan mediante sp_query_store_force_plan procedimiento (Transact-SQL ). El motor de base de datos no aplicará la recomendación si el usuario decidió forzar explícitamente algún plan.
`PlanUnforcedByUser`	El usuario no ha aplicado manualmente el plan mediante sp_query_store_unforce_plan procedimiento (Transact-SQL). Dado que el usuario revirtió explícitamente el plan recomendado, el motor de base de datos seguirá usando el plan actual y generará una nueva recomendación si se produce alguna regresión del plan en el futuro.
`UserForcedDifferentPlan`	El usuario ha forzado manualmente un plan diferente mediante sp_query_store_force_plan procedimiento (Transact-SQL). El motor de base de datos no aplicará la recomendación si el usuario decidió forzar explícitamente algún plan.
`TempTableChanged`	Se cambia una tabla temporal que se usó en el plan.

Las estadísticas de la details columna no muestran las estadísticas del plan en tiempo de ejecución (por ejemplo, la hora actual de CPU). Los detalles de la recomendación se toman en el momento de la detección de regresión y describen por qué el motor de base de datos identificó la regresión de rendimiento. 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 recomendación, consulte Compatibilidad con JSON en el 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.