Procedimientos recomendados de Sugerencias del almacén de consultas

Se aplica a: SQL Server 2022 (16.x) y versiones posteriores Azure SQL Database Azure SQL Managed Instance Base de datos SQL en Microsoft Fabric

En este artículo se detallan los procedimientos recomendados para usar Sugerencias de Almacén de consultas. Sugerencias del almacén de datos de consultas permite dar forma a los planes de consulta sin modificar el código de la aplicación.

Para obtener más información sobre cómo configurar y administrar con el Almacén de consultas, consulte Supervisión del rendimiento mediante el Almacén de consultas.
Para obtener información sobre cómo detectar información accionable y optimizar el rendimiento con el Almacén de consultas, consulte Optimización del rendimiento con el Almacén de consultas.
Para obtener procedimientos recomendados generales en el Almacén de consultas, consulte Procedimientos recomendados para supervisar cargas de trabajo con el Almacén de consultas.

Casos de uso para Sugerencias del Almacén de consultas

Considere los siguientes casos de uso como ideales para Sugerencias del almacén de consultas. Para obtener más información, consulte Cuándo usar Sugerencias del almacén de datos de consultas.

Caution

Como el optimizador de consultas de SQL Server suele seleccionar el mejor plan de ejecución para las consultas, se recomienda que solo los desarrolladores y administradores de bases de datos experimentados usen estas sugerencias y que lo hagan como último recurso. Para más información, consulte Sugerencias de consultas.

Cuando no se puede cambiar el código

El uso de Sugerencias del almacén de consultas permite influir en los planes de ejecución de las consultas sin cambiar el código de la aplicación ni los objetos de base de datos. Ninguna otra característica permite aplicar sugerencias de consulta de forma rápida y sencilla.

Puede usar sugerencias del Almacén de consultas, por ejemplo, para optimizar las cargas de trabajo de extracción-transformación-carga (ETL), sin volver a implementar el código. Aprenda cómo mejorar la carga masiva mediante indicaciones del Query Store en este vídeo de 14 minutos.

Las indicaciones de Query Store son métodos ligeros de optimización de consultas, pero si una consulta se convierte en problemática, deben abordarse con cambios de código más significativos. Si encuentra periódicamente la necesidad de aplicar sugerencias del Almacén de consultas a una consulta, considere la posibilidad de volver a escribir una consulta más grande. El optimizador de consultas de SQL Server normalmente selecciona el mejor plan de ejecución para una consulta. Solo se recomienda usar sugerencias como último recurso para desarrolladores experimentados y administradores de bases de datos.

Para obtener información sobre qué sugerencias de consulta se pueden aplicar, consulte Sugerencias de consulta admitidas.

Bajo una carga elevada de transacciones o con código crítico

Si los cambios de código no son prácticos debido a requisitos elevados de tiempo de actividad o de carga transaccional, Query Store Hints puede aplicar sugerencias de consulta de forma rápida a las cargas de trabajo de consulta existentes. Agregar y quitar Sugerencias del almacén de datos de consultas es fácil.

Sugerencias del Almacén de consultas se puede agregar y quitar en lotes de consultas para ajustar el rendimiento de las ventanas con tiempo de ráfagas de carga de trabajo excepcionales.

Como reemplazo de las guías de plan

Antes de las sugerencias del Almacén de Consultas, un desarrollador tendría que confiar en guías de planes para realizar tareas similares, que pueden ser complejas de usar. Sugerencias del Almacén de consultas está integrado con las características del Almacén de consultas de SQL Server Management Studio (SSMS) para la exploración visual de las consultas.

Con las guías de plan es necesario realizar búsquedas en todos los planes mediante fragmentos de código de consulta. La característica Sugerencias del almacén de consultas no requiere consultas con coincidencias exactas para tener un impacto en el plan de consulta resultante. Sugerencias del almacén de consultas se puede aplicar a un query_id en el conjunto de datos del almacén de consultas.

Sugerencias del almacén de consultas reemplaza las sugerencias de nivel de instrucción codificadas de forma rígida y las sugerencias existentes de la guía de plan.

Considerar un nivel de compatibilidad más reciente

Las sugerencias del Almacén de consultas pueden ser un método valioso cuando no está disponible un nivel más reciente de compatibilidad de base de datos debido a la especificación del proveedor o a mayores retrasos en las pruebas, por ejemplo. Cuando haya un nivel de compatibilidad superior disponible para una base de datos, considere la posibilidad de actualizar el nivel de compatibilidad de la base de datos de una consulta individual para aprovechar las últimas optimizaciones de rendimiento y características de SQL Server.

Por ejemplo, si tiene una instancia de SQL Server 2022 (16.x) con una base de datos en el nivel de compatibilidad 140, puede seguir usando sugerencias del almacén de consultas para ejecutar consultas individuales en el nivel de compatibilidad 160. Puede usar la siguiente sugerencia:

EXECUTE sys.sp_query_store_set_hints
    @query_id = 39,
    @query_hints = N'OPTION(USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160''))';

Para ver un tutorial completo, consulte Ejemplos de sugerencias del almacén de datos de consultas.

Considerar un nivel de compatibilidad anterior después de la actualización

Otro caso en el que las sugerencias del Almacén de consultas pueden ayudar es donde las consultas no se pueden modificar directamente después de una migración o actualización de instancias de SQL Server. Use las sugerencias del almacén de consultas para aplicar un nivel de compatibilidad anterior a una consulta hasta que pueda reescribirse o modificarse para que funcione correctamente en el nivel de compatibilidad más reciente. Identifique las consultas atípicas que se hayan devuelto con un nivel de compatibilidad superior mediante el informe de consultas devueltas del almacén de consultas, la herramienta Asistente para la optimización de consultas durante una migración u otra telemetría de aplicaciones a nivel de consultas. Para más información sobre las diferencias entre los niveles de compatibilidad, revise las Diferencias entre los niveles de compatibilidad.

Después de la prueba de rendimiento del nuevo nivel de compatibilidad y de implementar las sugerencias del almacén de consultas de este modo, puede actualizar todo el nivel de compatibilidad de la base de datos manteniendo las consultas problemáticas clave en el nivel de compatibilidad anterior, sin ningún cambio en el código.

Bloquear la ejecución futura de consultas problemáticas

Puede usar la sugerencia de consulta para bloquear la ABORT_QUERY_EXECUTION ejecución futura de consultas problemáticas conocidas, por ejemplo, consultas no esenciales que provocan un consumo elevado de recursos y afectan a las cargas de trabajo críticas de la aplicación.

Note

La sugerencia de consulta ABORT_QUERY_EXECUTION solo está disponible en Azure SQL Database, AZURE SQL Managed Instance^AUTD y SQL Server 2025 (17.x).

Por ejemplo, para bloquear la ejecución futura de query_id 39, ejecute sys.sp_query_store_set_hints de la siguiente manera:

EXECUTE sys.sp_query_store_set_hints
    @query_id = 39,
    @query_hints = N'OPTION (USE HINT (''ABORT_QUERY_EXECUTION''))';

Para desbloquear la misma consulta, ejecute sys.sp_query_store_clear_hints:

EXECUTE sys.sp_query_store_clear_hints @query_id = 39;

Para obtener más información, vea Ejemplos de sugerencias del Almacén de consultas.

Se aplican las siguientes consideraciones:

Al especificar esta sugerencia para una consulta, falla un intento de ejecutar la consulta con el error 8778, gravedad 16, la ejecución de la consulta se ha anulado porque se especificó la sugerencia ABORT_QUERY_EXECUTION.
Para desbloquear una consulta, puede borrar la sugerencia pasando el valor query_id al parámetro @query_id en el procedimiento almacenado sys.sp_query_store_clear_hints.
- Este procedimiento almacenado borra todas las indicaciones de una consulta. Si desea conservar las sugerencias existentes al desbloquear la consulta, use sys.sp_query_store_set_hints, quite la ABORT_QUERY_EXECUTION sugerencia pero mantenga otras sugerencias.

Puede usar vistas del sistema para buscar consultas en el Almacén de consultas que están bloqueadas, como en la consulta de ejemplo siguiente:

SELECT qsh.query_id,
       q.query_hash,
       qt.query_sql_text
FROM sys.query_store_query_hints AS qsh
     INNER JOIN sys.query_store_query AS q
         ON qsh.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS qt
         ON q.query_text_id = qt.query_text_id
WHERE UPPER(qsh.query_hint_text) LIKE '%ABORT[_]QUERY[_]EXECUTION%';

Para obtener el query_id valor, se debe registrar al menos una ejecución de consulta en el Almacén de consultas. Esta ejecución no tiene que ser correcta. Esto significa que se puede bloquear la ejecución futura de consultas agoadas o canceladas.
Si necesita bloquear o desbloquear todas las consultas con un hash de consulta específico, considere la posibilidad de usar un script de automatización. Por ejemplo, dbo.sp_query_store_modify_hints_by_query_hash es un procedimiento almacenado de ejemplo que llama en un bucle a los procedimientos almacenados del sistema sys.sp_query_store_set_hints o sys.sp_query_store_clear_hints para todos los valores de query_id que coinciden con un hash de consulta.
Si una consulta ya se está ejecutando al bloquearla, su ejecución continúa. Puede usar la instrucción KILL para anular la consulta.
- La ejecución de consultas eliminadas no se registra en el Almacén de consultas. Si la consulta aún no está en el Almacén de consultas, deberá permitir que la consulta se complete o se agote el tiempo de espera para obtener un query_id que pueda bloquear.
Cuando la sugerencia ABORT_QUERY_EXECUTION bloquea una consulta, las columnas execution_type y execution_type_desc de la vista sys.query_store_runtime_stats se establecen en 4 y Excepción respectivamente.
Al igual que con todas las sugerencias del Almacén de consultas, debe tener el permiso ALTER en la base de datos para establecer y borrar la sugerencia ABORT_QUERY_EXECUTION.

Consideraciones sobre Sugerencias del Almacén de consultas

Tenga en cuenta los siguientes escenarios cuando implemente Sugerencias del almacén de consultas.

Cambios en la distribución de datos

Las guías de plan, los planes forzados a través del almacén de consultas y Sugerencias del almacén de consultas invalidan la toma de decisiones del optimizador. La indicación del Query Store puede ser beneficiosa ahora, pero no en el futuro. Por ejemplo, si una sugerencia del Almacén de consultas ayuda a una consulta en la distribución de datos anterior, puede ser contraproducente si las operaciones DML a gran escala cambian los datos. Una nueva distribución de datos puede hacer que el optimizador tome una mejor decisión que la sugerencia. Este escenario es la consecuencia más común de forzar el comportamiento del plan.

Revise periódicamente su estrategia de indicaciones de Query Store.

Reevalúe la estrategia de Sugerencias del Almacén de consultas existentes en los casos siguientes:

Después de los cambios conocidos y significativos en la distribución de datos.
Cuando cambien los recursos disponibles para la base de datos. Por ejemplo, cuando cambia el tamaño de proceso de la máquina virtual de Azure SQL Database, SQL Managed Instance o SQL Server.
Donde la fijación del plan ha adquirido una larga duración. Las pistas de Query Store se utilizan mejor para correcciones a corto plazo.
Regresiones de rendimiento inesperadas.

Amplio potencial de impacto

Las sugerencias del Almacén de consultas afectan a todas las ejecuciones de la consulta, independientemente del conjunto de parámetros, la aplicación de origen, el usuario o el conjunto de resultados. En el caso de una regresión de rendimiento accidental, las sugerencias del Query Store creadas con sys.sp_query_store_set_hints se pueden quitar fácilmente con sys.sp_query_store_clear_hints.

Cargue cuidadosamente los cambios de prueba para sistemas críticos o confidenciales antes de aplicar Sugerencias del Almacén de consultas en producción.

No se admiten la parametrización forzada ni la sugerencia RECOMPILE

No se admite la RECOMPILE de la sugerencia de consulta con Sugerencias del almacén de consultas cuando la opción de base de datos PARAMETERIZATION está establecida en FORCED. Para obtener más información, consulte Directrices para usar la parametrización forzada.

La RECOMPILE sugerencia no es compatible con la parametrización forzada establecida en el nivel de base de datos. Si la base de datos usa la parametrización forzada y la RECOMPILE sugerencia forma parte de la cadena de sugerencias establecida en almacén de consultas para una consulta, el motor de base de datos omite la RECOMPILE sugerencia y aplica otras sugerencias si se especifican. Además, a partir de julio de 2022 en Azure SQL Database, se emite una advertencia (código de error 12461) que indica que se omitió la RECOMPILE sugerencia.