Forzado de plan optimizado con Almacén de consultas
Se aplica a: 
SQL Server 2022 (16.x) Azure SQL Database
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 la estimación del tiempo invertido en la optimización (en función del árbol de entrada del optimizador de consultas) determina si se crea 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 el Motor de base de datos 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. Una estimación del tiempo relativo para reproducir el script de reproducción de optimización se compara con el tiempo dedicado a ejecutar el proceso de optimización normal. Esta estimación se basa en el número de tareas de optimización almacenadas en el script de reproducción de optimización y en 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:
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".Las instrucciones con la sugerencia RECOMPILE y las consultas distribuidas no son aptas.
Sin embargo, si el Almacén de consultas captura de forma independiente un plan de consulta cuyo ámbito se ha realizado mediante la fuerza del plan optimizado, el script de reproducción de optimización se crea para una segunda recompilación de esa misma consulta, sujeto a eventos de recompilación predeterminados. Obtenga más información sobre la recompilación en Recompilación de planes de ejecución.
Incluso si se generó 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 directivas de captura configuradas 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 quita 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. Cuando el forzado de plan optimizado está habilitado para una base de datos, puede deshabilitarlo para consultas individuales mediante la sugerencia de DISABLE_OPTIMIZED_PLAN_FORCING consulta. También puede deshabilitar la fuerza del plan optimizado para un plan de consulta que se fuerza en 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 a partir de una versión inferior de SQL Server tienen un plan optimizado habilitado de forma predeterminada.
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 consulta DISABLE_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 sp_query_store_force_plan procedimiento almacenado requiere un parámetro adicional. El parámetro adicional se denomina @replica_group_id. De forma predeterminada, la principal @replica_group_id tiene 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 adecuados al sp_query_store_force_plan procedimiento almacenado 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.
Ejemplos
Los ejemplos de código de Transact-SQL de este artículo utilizan la base de datos de ejemplo AdventureWorks2022 o AdventureWorksDW2022, que se pueden descargar desde la página principal de Ejemplos y proyectos de la comunidad de Microsoft SQL Server.
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 que habilitan Almacén de consultas en las opciones alter DATABASE SET.
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 sp_query_store_force_plan procedimiento almacenado espera que el @replica_group_id parámetro se pase como el tercer valor de parámetro al intentar forzar el plan optimizado deshabilitado en Almacén de consultas. Puede usarse para desactivar el forzado de planes optimizados para un plan forzado concreto en una réplica específica. Se usa un valor de @replica_group_id = 1 para deshabilitar la característica en la réplica principal.
EXECUTE 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.
D. Seleccionar todas las consultas en las que el Almacén de consultas ha deshabilitado el forzado de plan optimizado
En el ejemplo siguiente se consultan todos los planes forzados en Almacén de consultas donde is_optimized_plan_forcing_disabled se establece 1en . 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 consulta DISABLE_OPTIMIZED_PLAN_FORCING.
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