sys.dm_db_tuning_recommendations (Transact-SQL)

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 más información, consulte Supervisión y optimización del rendimiento en Azure SQL Database y Azure SQL Instancia administrada.

En Azure SQL Database, las vistas de administración dinámica no pueden exponer información que afectaría 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
razón	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 siguientes campos 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 transact-SQL. 0 = La recomendación no se puede ejecutar en la base de datos (por ejemplo: información solo 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 ni 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 siguientes campos 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 - Tiempo medio 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 es siempre TSql. - script - Script de Transact-SQL que se debe ejecutar para forzar el plan recomendado.

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:

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 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 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:

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 forzar el plan manualmente mediante el script de la details columna.
UnsupportedStatementType	No se puede forzar el plan 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 la FORCE_LAST_GOOD_PLAN opción.
VerificationAborted	El proceso de comprobació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 en el 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 aplica manualmente el plan mediante sp_query_store_unforce_plan procedimiento (Transact-SQL). Dado que el usuario revierte 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 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)