Procedimientos recomendados de Sugerencias del almacén de consultas

Se aplica a: SQL Server 2022 (16.x) y versiones posteriores De Azure SQL DatabaseAzureSQL Managed InstanceSQL Database 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 el almacén de consultas, consulte Optimización del rendimiento mediante el almacén de consultas.
• Para obtener información sobre cómo detectar información procesable y ajustar el rendimiento con el almacén de consultas, consulte Optimización del rendimiento mediante el almacén de consultas.
• Para conocer los procedimientos recomendados generales en el Almacén de consultas, consulte Procedimientos recomendados 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.

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 beneficiarse de ETL sin volver a implementar 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 regularmente encuentra la necesidad de aplicar indicaciones del almacén de consultas a una consulta, considere hacer una reescritura más extensa de la consulta. 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 coincidentes 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

Sugerencias del almacén de datos de consultas puede ser un método valioso cuando un nivel de compatibilidad de base de datos más reciente no está disponible debido a la especificación del proveedor o retrasos en la fase de 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:

EXEC 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 cuando las consultas no se pueden modificar directamente después de una migración o actualización de una instancia 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 devuelven con un nivel de compatibilidad superior mediante el informe de consultas devueltas del almacén de datos 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 aplicaciones.

Por ejemplo, para bloquear la ejecución futura de query_id 39, ejecute la siguiente instrucción:

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

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.
• 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 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. Sugerencias del almacén de consultas 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 decisión mejor que la sugerencia. Este escenario es la consecuencia más común de forzar el comportamiento del plan.

Reevalúe periódicamente la estrategia de Sugerencias del Almacén de consultas

Reevalúe la estrategia de Sugerencias del Almacén de datos 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 aplicación de la sugerencia de consulta RECOMPILE 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.

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

Contenido relacionado

• Guardar un plan de ejecución en formato XML
• Mostrar y guardar planes de ejecución
• Configuración del grado máximo de paralelismo (MAXDOP) en Azure SQL Database