Optimización de consultas después de la migración
Supongamos que es un administrador de bases de datos de un bufete de abogados. Durante el fin de semana, ha migrado algunas bases de datos a SQL Server 2019, incluida la base de datos de administración de las relaciones con el cliente (CRM). El lunes a primera hora está en la oficina y espera que el equipo de ventas llegue en un par de horas y comience a realizar y recibir llamadas telefónicas de los clientes. El equipo de ventas depende de la base de datos de CRM y, para que pueda interactuar de forma eficaz con los clientes, la aplicación debe responder al instante.
Después de migrar las bases de datos, ha mantenido el nivel de compatibilidad en la configuración anterior. Las aplicaciones de CRM están conectadas al nuevo servidor. Quiere medir el rendimiento de las consultas en el sistema nuevo con respecto a una carga de trabajo típica de un lunes, antes de modificar el nivel de compatibilidad. Mantener el rendimiento de las consultas es un aspecto fundamental al actualizar las bases de datos.
En lugar de esperar a que el equipo de ventas descubra que la aplicación CRM es lenta, quiere un proceso para medir de forma automática la carga de trabajo y el rendimiento. Este proceso debería proporcionar la seguridad de que puede corregir las consultas lentas después de cambiar el nivel de compatibilidad.
Almacén de consultas
La característica Almacén de consultas se publicó en SQL Server 2016 para recopilar información sobre las consultas de forma continuada. El Almacén de consultas recopila información que se puede usar para solucionar problemas de consultas de bajo rendimiento, y funciona con instalaciones locales y en la nube. Los planes de consulta en caché se almacenan en la caché de procedimientos, pero SQL Server solo almacena el plan de ejecución más reciente.
Es probable que los planes de ejecución de una consulta cambien con el tiempo debido a cambios en el esquema o la adición o eliminación de índices. Además, las presiones de memoria pueden hacer que los planes se expulsen de la caché de planes.
De forma predeterminada, Almacén de consultas no está habilitado cuando se crea o se migra una base de datos de a SQL Server 2017 o SQL Server 2019. Sin embargo, puede habilitar la característica Almacén de consultas antes de actualizar el nivel de compatibilidad de la base de datos migrada. Habilite el Almacén de consultas; para ello, haga clic con el botón derecho en la base de datos en el Explorador de objetos, seleccione Propiedades y, después, seleccione la página Almacén de consultas. En la lista de selección Modo de operación (solicitado), elija entre Desactivado, Solo lectura o Lectura y escritura. También puede habilitar el Almacén de consultas mediante Transact-SQL. Para habilitar el Almacén de consultas para una base de datos denominada CustomerServices, debe ejecutar el comando siguiente:
ALTER DATABASE CustomerServices SET QUERY_STORE = ON
Al compilar inicialmente una consulta, el texto de la consulta y el primer plan se pasan al Almacén de consultas. Si se vuelve a compilar una consulta, el plan se actualiza en el Almacén de consultas. Cuando se crea un plan, el Almacén de consultas lo agrega y lo guarda junto con los planes anteriores, así como las estadísticas de ejecución.
A medida que se ejecuta la consulta, Almacén de consultas guarda las estadísticas en tiempo de ejecución. Durante la compilación o la recompilación, SQL Server comprueba si existe un plan en el Almacén de consultas que se deba aplicar a la consulta. Cuando un plan forzado difiere del plan en la caché de procedimientos, la consulta se vuelve a compilar.
Hasta que cambie el nivel de compatibilidad, la base de datos no se expone a los cambios más recientes del Optimizador de consultas. Puede recopilar una carga de trabajo típica y crear una línea de base para medirla en el nivel de compatibilidad existente. Después de recopilar información de consulta suficiente para la línea de base de rendimiento, cambie el nivel de compatibilidad de la base de datos a la versión del servidor.
Para crear una línea de base adecuada, asegúrese de recopilar datos suficientes de un período de actividad profesional típico. Al pasar al nivel de compatibilidad del servidor actual, la carga de trabajo se expone a la versión más reciente del Optimizador de consultas. Probablemente el rendimiento de las consultas mejorará, ya que el Optimizador de consultas nuevo debe generar mejores planes.
En ocasiones, SQL Server usará un plan de consulta que no es óptimo, por lo que de repente se ejecuta con lentitud sin motivo aparente. Puede solucionar este problema si se fuerza un plan anterior en el Almacén de consultas. El uso de Almacén de consultas es similar al de la sugerencia de consulta USE PLAN, pero no requiere ningún cambio en las aplicaciones de usuario.
Después de cambiar el nivel de compatibilidad, el Almacén de consultas continúa proporcionando compatibilidad con consultas para mantener el mejor rendimiento. Realiza la medición y auditoría de Consultas con regresión, Consumo general de recursos, Consultas que más recursos consumen, Consultas con planes forzados, Consultas con gran variación y Consultas con seguimiento.
Ajuste automático de consultas
El rol del programa de estimación de la cardinalidad (CE) en SQL Server es calcular el número de filas devueltas en una consulta. El optimizador de consultas usa el resultado del CE para generar un plan de ejecución. Hasta SQL Server 2014, el programa de estimación de la cardinalidad apenas había sufrido cambios. En SQL Server 2014 se revisaron los algoritmos del CE para mejorar las estimaciones y ofrecer los resultados de consulta más rápidos. Pero el nivel de compatibilidad de la base de datos determina qué CE se va a usar.
Aunque el rendimiento de las consultas debería mejorar considerablemente a partir de SQL Server 2014, una base de datos migrada con un nivel de compatibilidad actualizado puede sufrir un rendimiento degradado. La característica Ajuste automático de consultas se introdujo en SQL Server 2017. Úsela junto con el Almacén de consultas para buscar y corregir de forma automática las consultas con regresión. Después de migrar la base de datos y capturar la carga de trabajo de la base de datos en el nivel de compatibilidad anterior, actualice el nivel de compatibilidad y siga ejecutando el Almacén de consultas.
Ajuste automático usa los datos del Almacén de consultas para buscar consultas que empiezan a mostrar regresión debido a cambios en el programa de estimación de la cardinalidad después de cambiar el nivel de compatibilidad. Ajuste automático experimenta en las consultas con regresión para mejorar el rendimiento, lo que permite crear guías de plan en las que se han realizado mejoras.
Procedimientos para optimizar el rendimiento de las consultas en SQL Server 2019
En el ejemplo del bufete de abogados, como ha actualizado los servidores de base de datos a SQL Server 2019, puede usar tanto el Almacén de consultas como el Ajuste automático para optimizar el rendimiento y asegurarse de que el cambio en el nivel de compatibilidad no provoca regresiones. Recomendamos el siguiente proceso:
- Actualice el servidor de base de datos a SQL Server 2019, pero no cambie el nivel de compatibilidad.
- Habilite el Almacén de consultas.
- Permita que el Almacén de consultas recopile datos de rendimiento durante el horario comercial normal.
- Actualice el nivel de compatibilidad de la base de datos.
- Compruebe si Almacén de consultas ha encontrado regresiones. En ese caso, puede forzar el último plan de consulta correcto conocido para volver al nivel de rendimiento superior.
El Almacén de consultas automatiza el proceso de supervisión del rendimiento de las consultas. Se trata de una herramienta útil que se puede ejecutar en segundo plano para asegurarse de que la base de datos entregue continuamente los resultados en el tiempo más rápido posible. Junto con el Almacén de consultas, el Asistente para la optimización de consultas usa los datos del Almacén de consultas para buscar consultas con regresión en las bases de datos actualizadas.
Después de migrar una base de datos y actualizar el nivel de compatibilidad a SQL Server 2014 o superior, se usa el nuevo programa de estimación de la cardinalidad para adivinar el número de filas devueltas por una consulta. Use este Asistente para buscar y corregir consultas con regresión mediante el cambio al nuevo CE.