Procedimientos recomendados de Sugerencias del almacén de consultas

Artículo
05/30/2023

Se aplica a: SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance

En este artículo se detallan los procedimientos recomendados para usar las sugerencias del almacén de consultas. Sugerencias del almacén 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.

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 consultas.

Precaución

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 beneficiar de las ETL sin volver a implementar código. Aprenda a mejorar la carga masiva con Sugerencias del almacén de consultas con este vídeo de 14 minutos:

Sugerencias del almacén de consultas consiste en métodos ligeros de optimización de consultas, pero si una consulta se convierte en problemática, debe abordarse con cambios de código más importantes. Si encuentra regularmente 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. 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 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 de tiempo de actividad elevados o a la carga transaccional, Sugerencias del almacén de consultas puede aplicar sugerencias de consulta a las cargas de trabajo de consulta existentes rápidamente. Agregar y quitar Sugerencias del almacén 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 Sugerencias del almacén de consultas, un desarrollador tendría que confiar en guías de plan 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 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 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 hayan devuelto a un nivel de compatibilidad superior mediante el informe de consultas devueltas del almacén de consultas, la herramienta Asesor de 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.

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 consultas existentes en los casos siguientes:

Después de saber de cambios importantes en la distribución de datos.
Cuando el objetivo de nivel de servicio (SLO) de Azure SQL Database, Azure SQL Managed Instance o máquina virtual de Azure SQL Database haya cambiado.
Donde la fijación del plan ha adquirido una larga duración. Sugerencias del almacén de consultas se usa mejor para correcciones a corto plazo.
Regresiones de rendimiento inesperadas.

Amplio potencial de impacto

Sugerencias del almacén de consultas afectará 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 accidental del rendimiento, las sugerencias del almacén de consultas 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 sugerencia RECOMPILE no es compatible con la parametrización forzada establecida en el nivel de base de datos. Si la base de datos tiene establecida la parametrización forzada y la sugerencia RECOMPILE forma parte de la cadena de sugerencias establecida en el almacén de consultas para una consulta, el motor de base de datos omitirá la sugerencia RECOMPILE y aplicará otras sugerencias que pueda aprovechar. Además, a partir de julio de 2022 en Azure SQL Database, se emitirá una advertencia (código de error 12461) indicando que se ha ignorado la sugerencia RECOMPILE.