Forzado de plan optimizado con Almacén de consultas

  • Artículo

Se aplica a: SQL Server 2022 (16.x)

La optimización de consultas es un proceso compuesto de varias fases por el que se genera un plan de ejecución de consultas "lo suficiente bueno". En algunos casos, la compilación de consultas (una parte de la optimización de consultas) puede suponer gran parte del tiempo de ejecución de consultas general y consumir recursos del sistema significativos. El forzado optimizado de planes forma parte de la familia de características del procesamiento inteligente de consultas. El forzado optimizado del plan reduce la sobrecarga de compilación para repetir consultas forzadas y requiere que el almacén de consultas esté activado y en modo "lectura-escritura". Una vez generado el plan de ejecución de consultas, los pasos de compilación específicos se almacenan para que puedan reutilizarse como un script de reproducción para optimización. Un script de reproducción de optimización se almacena como parte del XML del plan de presentación comprimido en Almacén de consultas, en un atributo OptimizationReplay oculto.

Implementación del forzado de plan optimizado

Cuando una consulta pasa por el proceso de compilación por primera vez, un umbral basado en una estimación del tiempo empleado en la optimización (según el árbol de entrada del optimizador de consultas) determinará si se va a crear un script de reproducción de optimización.

Una vez finalizada la compilación, hay varias métricas en tiempo de ejecución disponibles para evaluar si la estimación anterior era correcta. Si se confirma que se ha cruzado el umbral, el script de reproducción de optimización es apto para la persistencia. Estas métricas en tiempo de ejecución incluyen el número de objetos a los que se ha accedido, el número de combinaciones, el número de tareas de optimización ejecutadas durante la optimización y el tiempo de optimización real.

La hipotética ventaja de usar un script de reproducción de optimización también se compara con la sobrecarga de almacenar el script de reproducción de optimización. Se compara una estimación del tiempo relativo para reproducir el script de reproducción de optimización con el tiempo dedicado a ejecutar el proceso de optimización normal, según el número de tareas de optimización almacenadas en el script de reproducción de optimización y el número de tareas de optimización ejecutadas durante la compilación normal. Si la reproducción del script de reproducción de optimización revela una ventaja sustancial en la reducción del tiempo de compilación, se conserva el script de reproducción de optimización.

Consideraciones

Cuando se habilita la característica de forzado de plan optimizado, los criterios de idoneidad para dicho plan son estas:

  1. Solo son aptos los planes de consulta que pasan por la optimización completa, lo que se pueden comprobar mediante la presencia de la propiedad StatementOptmLevel="FULL".
  2. Las instrucciones con la sugerencia RECOMPILE y las consultas distribuidas no son aptas.

Con todo, si Almacén de consultas captura de forma independiente un plan de consulta que se quedó fuera a raíz del forzado de plan optimizado, el script de reproducción de optimización se creará para una segunda recompilación de esa misma consulta, todo ello sujeto a eventos de recompilación predeterminados. Obtenga más información sobre la recompilación en Recompilación de planes de ejecución.

Aun cuando se haya generado un script de reproducción de optimización, es posible que no se conserve en el Almacén de consultas si no se cumplen los criterios de las directivas de captura configuradas del Almacén de consultas, en particular el número de ejecuciones de esa instrucción y sus tiempos de compilación y ejecución acumulados. En este caso, el script de reproducción de optimización no válido se quitará de la memoria de forma asincrónica.

Habilitación y deshabilitación del forzado de plan optimizado

El forzado de plan optimizado de una base de datos se puede habilitar o deshabilitar. Si el forzado de plan optimizado de una base de datos está habilitado, puede deshabilitarlo en consultas individuales mediante la sugerencia de consulta DISABLE_OPTIMIZED_PLAN_FORCING. El forzado de plan optimizado también se puede deshabilitar en un plan de consulta que sea forzado en el Almacén de consultas.

Habilitación y deshabilitación del forzado de plan optimizado de una base de datos

El forzado de plan optimizado está habilitado de manera predeterminada en las nuevas bases de datos creadas en SQL Server 2022 (16.x) y versiones posteriores. El Almacén de consultas debe estar habilitado en cada base de datos en la que se use el forzado de plan optimizado. Las instancias actualizadas con bases de datos existentes o bases de datos restauradas desde una versión inferior de SQL Server tendrán activado de manera predeterminada el forzado de planes optimizado.

Para habilitar el forzado de plan optimizado en el nivel de base de datos, use la configuración con ámbito de base de datos ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON. Debe habilitar el Almacén de consultas si aún no está habilitado. Vea un código de ejemplo en Ejemplo A u obtenga más información sobre el Almacén de consultas en Supervisión del rendimiento mediante el Almacén de consultas.

Para deshabilitar el forzado de plan optimizado en el nivel de base de datos, use la configuración con ámbito de base de datos ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = OFF.

Deshabilitación del forzado de plan optimizado con una sugerencia de consulta

Si la característica de forzado de plan optimizado está habilitada en una base de datos, puede deshabilitarla en una consulta individual mediante la sugerencia de consultaDISABLE_OPTIMIZED_PLAN_FORCING.

Vea un ejemplo de cómo aplicar esta sugerencia de consulta en Ejemplo E.

Forzado de un plan con el Almacén de consultas, pero con el forzado de plan optimizado deshabilitado

El procedimiento sp_query_store_force_plan incluye un parámetro disable_optimized_plan_forcing. Para usar este parámetro, el procedimiento almacenado sp_query_store_force_plan requiere un parámetro adicional. El parámetro adicional se denomina replica_group_id. De manera predeterminada, el replica_group_id principal tendrá un valor de uno (1) incluso en el caso de que no haya réplicas secundarias configuradas.

Busque un ejemplo de aplicación de los parámetros apropiados al procedimiento almacenado sp_query_store_force_plan en el Ejemplo C.

La vista de catálogo sys.query_store_plan incluye columnas que indican si el plan tiene un script de reproducción de optimización asociado, y agrega un nuevo estado a la columna de motivo de error existente específica del script de reproducción de optimización asociado. Obtenga más información en sys.query_store_plan (Transact-SQL)..

Ejemplos

A Habilitar el Almacén de consultas y el forzado de plan optimizado en una base de datos

En el siguiente código se habilita el Almacén de consultas en una base de datos y, después, se habilita el forzado de plan optimizado en la base de datos. Obtenga más información sobre las opciones para habilitar el Almacén de consultas en Opciones de ALTER DATABASE SET (Transact-SQL).

Antes de ejecutar el código, conéctese a la base de datos de usuario adecuada.

ALTER DATABASE CURRENT SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_STORAGE_SIZE_MB = 1024,
    INTERVAL_LENGTH_MINUTES = 60
);
GO

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON;
GO

B. Seleccionar todas las consultas que tienen un script de reproducción de optimización

En el código de ejemplo siguiente se seleccionan todos los elementos query_id que tienen un script de reproducción de optimización en el Almacén de consultas. Antes de ejecutar el código, conéctese a la base de datos de usuario adecuada.

SELECT q.query_id,
    t.query_sql_text,
    p.plan_id,
    TRY_CAST(p.query_plan AS XML) AS query_plan,
    p.is_forced_plan,
    p.count_compiles
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_query AS q
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_query_text AS t
    ON q.query_text_id = t.query_text_id
WHERE p.has_compile_replay_script = 1;
GO

C. Forzar un plan y deshabilitar el forzado de plan optimizado en el Almacén de consultas

En el siguiente código se fuerza un plan en el Almacén de consultas, pero se deshabilita el forzado de plan optimizado. Antes de ejecutar el código, reemplace @query_id y @plan_id por una combinación adecuada para la instancia. El procedimiento almacenado sp_query_store_force_plan esperará que el parámetro @replica_group_id se pase como el tercer valor de parámetro cuando se intente desactivar el forzado de plan optimizado en el almacén de consultas. Puede usarse para desactivar el forzado de planes optimizados para un plan forzado concreto en una réplica específica. Un valor de 1 - @replica_group_id=1 se usará para desactivar la característica en la réplica principal.

EXEC sp_query_store_force_plan @query_id=148, @plan_id=4, @replica_group_id=1, @disable_optimized_plan_forcing=1;
GO

Obtenga más información en sp_query_store_force_plan (Transact-SQL)..

D. Seleccionar todas las consultas en las que el Almacén de consultas ha deshabilitado el forzado de plan optimizado

En el siguiente ejemplo se consultan todos los planes forzados en el Almacén de consultas donde is_optimized_plan_forcing_disabled se ha establecido en 1. Antes de ejecutar el código, conéctese a la base de datos de usuario adecuada.

SELECT q.query_id,
    t.query_sql_text,
    p.plan_id,
    TRY_CAST(p.query_plan AS XML) AS query_plan,
    p.is_forced_plan,
    p.count_compiles
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_query AS q
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_query_text AS t
    ON q.query_text_id = t.query_text_id
WHERE p.is_optimized_plan_forcing_disabled = 1;
GO

E. Deshabilitar el forzado de plan optimizado en una consulta

En el siguiente ejemplo se deshabilita el forzado de plan optimizado en una consulta mediante la sugerencia de consultaDISABLE_OPTIMIZED_PLAN_FORCING. En este ejemplo se utiliza la base de datos de ejemplo AdventureWorks.

SELECT ProductID,
    OrderQty,
    SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID,
    OrderQty
ORDER BY ProductID,
    OrderQty
OPTION (USE HINT('DISABLE_OPTIMIZED_PLAN_FORCING'));
GO

Pasos siguientes

Obtenga más información sobre el Almacén de consultas y el forzado de plan optimizado en los siguientes artículos: