Cómo identificar y resolver consultas de ejecución lenta en SQL Server

Probablemente, el problema más común cuando se mantienen SQL Server son las consultas de ejecución lenta. No es inusual que un DBA obtenga información de que la base de datos de la aplicación o el usuario está lenta, o incluso que los usuarios están teniendo mensajes de tiempos de espera expirados cuando están trabajando con SQL Server o aplicaciones de SQL Server. Generalmente, cuando tales problemas relacionados con el desempeño de SQL Server son encontrados, el primer paso para resolver tal problema es identificar rápidamente qué consultas lentas en SQL Server son la causa de tal problema. El siguiente paso es determinar por qué estas consultas se ejecutan lentamente y cuál es la causa de tal comportamiento.

Hay muchas maneras nativas de identificar las consultas de ejecución lenta en SQL Server, y para aquellos que quieren saber más acerca de cómo hacer eso, por favor consulten los Recursos útiles aal final de este artículo para ver enlaces a artículos relevantes.

Este artículo no verá a detalle el bloqueo de consultas, dado que este es un asunto esencialmente diferente. Debería ser notado, sin embargo, que si se determina que la consulta está realmente bloqueada (más comúnmente usando los procedimientos almacenados sp_who, sp_who2 o sp-whoisactive ), entonces es la consulta la que causó el bloqueo en sí misma que requeriría una solución de problemas.

ApexSQL Monitor

ApexSQL Monitor es una herramienta de monitoreo de desempeño de SQL Server sin agente capáz de monitorear un rango extensivo de métricas del Sistema Operativo Windows, SQL Server y la base de datos de SQL Server para múltiples SQL Server simultáneamente. Puede monitorear el desempeño de SQL Server en instancias múltiples locales y remotas de Windows OS y SQL Server y asistir en identificar problemas de desempeño de SQL Server. Puede monitorear consultas ejecutadas y recolectar suficiente información para asegurar una detección rápida y fácil de las consultas lentas en SQL Server o aquellas que están usando la mayor parte de los recursos. Adicionalmente, su sistema de alertas es capaz de notificar al usuario cuando los parámetros especificados van más allá de los valores de umbral predefinidos o definidos por el usuario, o si van más allá de los umbrales de línea de base calculados.

ApexSQL Monitor permite diferentes maneras para identificar qué consulta está ejecutándose lentamente y por qué. En este artículo el foco estará en usar la característica de desempeño de Consultas, diseñada específicamente para mostrar las consultas de ejecución más lenta. Para hacerlo más fácil para el usuario, la degradación del desempeño de consultas problemáticas puede ser evaluada por ApexSQL Monitor no sólo por el tiempo de ejecución, necesario por la consulta para completar la tarea, sino también por las siguientes categorías que deberían permitir al usuario a determinar mucho más precisamente la causa potencial de la lentitud de las consultas:

  • Consultas más lentas por uso promedio de CPU. Esto puede indicar al usuario qué consultas utilizan la más alta cantidad de CPU, lo cual podría ser una indicación de que los recursos del CPU son usados irracionalmente.

  • Consultas más lentas por actividad promedio de I/O. I/O es el parámetro muy importante que tiene que ser tomado en cuenta cuando la lentitud en la ejecución de la consulta es evidente. Los subsistemas I/O, los cuales usualmente traducen la comunicación con el disco duro, son aún la parte más lenta e incluso con discos modernos de alta velocidad SSD, esta es aún una de las causas más comunes de problemas de desempeño.

  • Consultas más lentas por tiempo promedio de ejecución. Este es un indicador directo que muestra consultas lentas en SQL Server. El tiempo de ejecución promedio es un parámetro más confiable que el tiempo de ejecución de consultas individuales, dado que los valores altos son indicadores del patrón de lentitud en la ejecución de consultas particulares, más que sólo una anomalía que ocurrió una vez o dos.

La información y los detalles acerca de las consultas más lentas es recuperada directamente desde las vistas de administración dinámicas de SQL Server (DMV). Como el nombre indica, estas son dinámicas, lo que significa que la información recolectada por las DMVs se elimina con cada reinicio de SQL Server. En ambientes normales de trabajo, cuando SQL Server está activo y corriendo 24 horas, los 7 días de la semana, esto funciona muy bien, pero para los casos raros donde SQL Server se reinicia frecuentemente, los datos recolectados vía DMVs pueden no ser formados usando muestras representativas y confiables, así que algunos de estos tienen que ser tomados cautelosamente para el propósito de resolución de problemas.

ApexSQL Monitor permite evaluar consultas que se están ejecutando lentamente con un solo clic, pero la información mostrada, aunque muy completa y detallada, es sólo la primera parte del proceso de resolución de problemas.

Para encontrar consultas lentas en SQL Server, lo siguiente tiene que ser hecho:

  1. Seleccione la instancia SQL Server que debería ser inspeccionada en el panel izquierdo de ApexSQL Monitor.

  2. Seleccione el botón Charts en la pestaña Home.

  3. Seleccione la pestaña Query performance en la pantalla principal.

Usualmente, no hay necesidad de mostrar más que las 20 consultas más lentas, así que esto fue configurado al valor por defecto. Otros pueden querer bajar eso a 10 o incluso 5 consultas para los SQL Servers que no están experimentando problemas serios, pero no hay un límite real impuesto a cuántas consultas lentas pueden ser recuperadas y mostradas. Después de que el número de consultas es cambiado, el botón Refresh debe ser presionado para recuperar un nuevo conjunto de consultas lentas de SQL Server de acuerdo al número definido de consultas.

Otra característica importante que debería ser mencionada es que ApexSQL Monitor no recuperará sólo las 20 consultas más lentas (si 20 fue establecido, por ejemplo), sino que las ordenará de acuerdo a sus valores para cada categoría de gráfico. ApexSQL Monitor realmente recuperará las 20 consultas más lentas en SQL Server para cada una de las tres categorías separadamente, lo cual significa básicamente que el número de consultas mostradas será 60. El gráfico en sí mismo puede mostrar no más de 10 de las consultas más lentas, mientras que el resto de las consultas será mostrado en la cuadrícula debajo del gráfico. Esta cuadrícula puede ser ordenada fácilmente por la categoría deseada simplemente haciendo clic en el título de la columna adecuada.

Tener una consulta que tiene un tiempo de ejecución alto no significa que esto es algo malo en sí mismo y no significa necesariamente que esto es el problema. Puede ser que esa consulta está sólo procesando un gran conjunto de datos regularmente, así que necesita tiempo para completar la tarea. Con eso dicho, para mejorar la identificación del problema potencial de las consultas lentas en SQL Server, ApexSQL Monitor puede mostrar los siguientes datos acerca de las consultas ejecutadas que son de interés primario para propósitos de identificación del problema:

  • Conteo de ejecución
  • Tiempo de ejecución promedio
  • Tiempo de CPU promedio
  • Promedio de I/O por segundo
  • Promedio de lecturas físicas
  • Promedio de lecturas lógicas
  • Promedio de escrituras lógicas

En adición a los datos listados, ApexSQL Monitor puede mostrar la fecha y hora cuando la consulta es ejecutada por última vez, así como el plan de consultas para esa consulta.

Tener datos confiables y completos que ApexSQL Monitor provee acerca del desempeño de las consultas es muy importante, pero esto es sólo el primer paso en identificar y resolver cualquier problema potencial. Lo que es igualmente importante es la interpretación apropiada de esos datos provistos por ApexSQL Monitor.

Tiempo de ejecución alto, una sola ejecución

Así que veamos unos pocos escenarios típicos donde la interpretación apropiada de los datos es crucial. En el siguiente ejemplo hay una consulta específica que tiene un tiempo de ejecución promedio alto, pero cuando se ve más de cerca a los detalles de esa consulta, puede verse que su conteo de ejecuciones es 1 (es ejecutada sólo una vez).

Teniendo esto en mente, es poco probable que esta consulta particular afectara el desempeño de SQL Server, simplemente porque es ejecutada sólo una vez, e incluso cuando el tiempo de ejecución es alto, tal consulta ad-hoc no debería afectar a SQL Server.

Tiempo promedio de ejecución alto, tiempo promedio de CPU alto y promedio de I/O por segundo

Por otro lado, si esa consulta particular, aparte de tener un tiempo promedio de ejecución alto, también tiene un valor alto para el uso promedio de CPU y/o promedio de I/O por segundo, está claro que la consulta ha afectado toso el sistema globalmente y eso, como tal, tiene el potencial de afectar seriamente el desempeño de SQL Server si se ejecutó más de una vez o durante un pico de uso de SQL Server.

Tener valores tan altos para múltiples parámetros, incluso cuando se ejecuta una sola vez, es una clara indicación de que la consulta requiere una revisión más cercana y una resolución potencial de problemas.

Tiempo alto de ejecución, múltiples ejecuciones

Otro ejemplo común es la consulta que tiene un tiempo promedio de ejecución alto, pero también tiene un número alto de ejecuciones.

Este es un escenario típico que involucra un tipo de consulta donde una exploración de problemas debería ser realizada inmediatamente. A menudo el primer paso es coordinar con los desarrolladores de la aplicación para determinar si la frecuencia de la ejecución, si no está optimizada en sí misma, puede ser reducida.

Revise el “Último tiempo de ejecución” para asegurarse de que la consulta es aún relevante y no simplemente un evento de una vez que es poco probable que ocurra de nuevo.

Ahora, una vez que la consulta que es la potencial causa del problema de desempeño en SQL es identificada, el administrador de la base de datos puede continuar la resolución de problemas. El primer y obvio paso de la resolución de problemas es revisar el script T-SQL de esa consulta. El script T-SQL completo es provisto por ApexSQL Monitor, permite una vista general rápida para problemas críticos y obvios.

Plan de ejecución de consultas

Por otra parte, para consultas más complejas, ApexSQL Monitor está equipad con la habilidad de mostrar el plan de ejecución real para la consulta que requiere solucionar problemas. Al lado derecho en la cuadrícula de desempeño de consultas de cada consulta, hay un enlace Query plan. Haciendo clic en el enlace, el usuario de ApexSQL Monitor podrá ver la presentación gráfica del plan de ejecución real para la consulta seleccionada.

En nuestro ejemplo tenemos que correr la consulta contra la base de datos AdventureWorks2014, y es visible que esta consulta particular tiene un tiempo promedio de ejecución bastante alto. Así que, haciendo clic en el enlace Query plan, el plan de ejecución real para esa consulta será mostrado.

Este plan de ejecución de consultas en particular muestra el mensaje Missing index (Impact 92.2646): CREATE NONCLUSTERED INDEX [] ON [Person].Address] ([City]), lo cual es el inicio potencial de la solución de problemas. Pero hay que tener en mente que este mensaje no debería ser tratado como un error o como el mensaje de aviso. Este es simplemente un consejo y una sugerencia ofrecida al usuario acerca de la acción que puede ser tomada para mejorar el desempeño de ejecución por alrededor de 92% en este caso particular.

En este ejemplo, crear un índice puede mejorar el desempeño, pero no es aconsejable crear todos los índices perdidos reportados en el plan de ejecución de consultas automáticamente. Es muy importante saber que la recomendación hecha en el plan de ejecución de consultas es creada para mejorar esa consulta particular. No revisa el desempeño del ambiente holísticamente, incluyendo como los nuevos índices podrían afectar otras consultas. Igual que los nuevos índices pueden mejorar el desempeño, ellos también pueden tener algunas desventajas también.

Recursos útiles:

Traductor: Daniel Calbimonte

diciembre 24, 2016