Cómo investigar problemas de desempeño de SQL Server causados por consultas lentas con estadísticas de espera

Estrategia de resolución de problemas de desempeño

Las siguientes recomendaciones son parte de una estrategia efectiva para investigar problemas de desempeño de SQL Server causados por consultas lentas:

  • Cuando un problema de desempeño es reportado por un usuario final, obtenga información acerca del problema con la mayor cantidad de detalles posible.
  • Identifique qué consultas están corriendo en el momento cuando el problema es detectado.
  • Identifique por cuánto tiempo han estado corriendo esas consultas.
  • Identifique qué consultas están en espera.
  • Encuentre el tiempo de espera general para consultas para el periodo de tiempo de interés.
  • Identifique qué tipos de esperas están afectando las consultas con un tiempo de espera alto.
  • Revise si los tiempos de espera para consultas sospechosas son altos sólo durante el periodo cuando el problema ha sido experimentado, o si los tiempos de espera altos de valores similares son un comportamiento normal de esa consulta en el pasado.

Muchos problemas de desempeño de SQL Server pueden ser explicados e identificados con el uso de estadísticas de espera de SQL Server, “haciendo preguntas” y obteniendo respuestas de SQL Server.

Cuando un DBA pregunta a SQL Server “¿por qué SQL Server está funcionando lentamente?”, la manera de SQL Server para explicar esto es respondiendo “porque la consulta es lenta, ya que tiene que esperar más tiempo de lo esperado en señales y/o recursos para completar la ejecución”. Y cuando el DBA luego pregunta “OK, entonces, ¿qué signos y/o recursos tienen que ser esperados por la consulta y por cuánto tiempo?”, SQL Server responderá proveyendo la información acerca qué tipos de esperas de signos y/o recursos están involucrados incluyendo a cantidad de tiempo de espera que es acumulado para cada tipo de espera.

Quick tip icon

Consejo rápido:

El concepto de la estadística de espera de SQL Server considera dos categorías básicas: esperas de recursos y de signos. El proceso de SQL Server genera esperas de recursos mientras que espera a que algunos recursos específicos se vuelvan disponibles durante la ejecución. Las esperas de signos son generadas mientras el proceso está esperando a que la CPU esté disponible para continuar con la ejecución. En este caso, el proceso está esperando por una “señal” desde una CPU que está disponible para el proceso afectado, y por tanto el nombre “esperas de signos”.

Investigar problemas de desempeño de SQL Server usando ApexSQL Monitor

ApexSQL Monitor es una aplicación de monitoreo de desempeño de SQL Server que es capáz de rastrear la ejecución de consultas individuales y recolectar los datos de estadísticas de espera para esas consultas, así como mucha información relacionada a la ejecución de consultas que pueden ser de beneficio adicional durante la investigación de los problemas de desempeño de SQL Server causados por el desempeño de consultas lentas.

En lugar de forzar DBAs para preguntar a SQL Server aquellas preguntas todo el tiempo, y esperar por respuestas, y procesar y almacenar “respuestas” en algún lugar, a modo de asegurar un tipo de análisis básico al menos, ApexSQL Monitor puede hacer ese trabajo automáticamente por el DBA. La herramienta identificará todas las consultas que están corriendo más lentamente de lo esperado, recolectará tiempos de espera y tiempos acumulados durante la ejecución de la consulta, almacenará esa información en la base de datos de repositorio central tanto tiempo como sea requerido, y finalmente analizará y mostrará esos datos en una manera que se asegurará que las consultas más problemáticas sean fácilmente identificadas. Adicionalmente, ApexSQL Monitor puede levantar alertas y mandar notificaciones cuando el umbral de tiempos de espera definido por el DBA es excedido. Aparte de eso, ApexSQL Monitor recolectará y almacenará información acerca de otros recursos relacionados con el desempeño de las consultas usados por esa consulta particular durante la ejecución, como tiempo de CPU, lecturas lógicas y físicas, escrituras lógicas e I/O.

Quick tip icon

Consejo rápido:

Cuando tiempos altos para las esperas de consultas son detectados en SQL Server, revise lo siguiente antes de iniciar una investigación y análisis más profundos:

  • Asegúrese de que los mensajes del registro de errores de SQL Server están verificados para ingresos fallidos, para cualquier tipo de corrupción y para stackdumps.
  • Revise que el valor de Max Server Memory en SQL Server sea adecuado y que no sea muy bajo.
  • Cuando la reducción automática de la base de datos está establecida, puede causar varios problemas de desempeño, así que revise eso.
  • Revise el registro de eventos de Windows para ver indicaciones acerca de problemas de hardware como la RAM, el almacenamiento y/o problemas de energía.

Configuración de esperas de consultas

El primer paso que tiene que ser hecho es definir qué cantidad de tiempos de espera acumulados para cada ejecución de consultas puede afectar al desempeño de SQL Server. Esto debería ser definido para evitar una recolección innecesaria de datos de esperas de consultas que no afectarán el desempeño de SQL Server en ninguna manera, y por tanto evadiendo el ruido.

El segundo paso es determinar cuál es el tiempo de espera total que una consulta puede acumular a través de múltiples ejecuciones durante el día. Esto es también importante, ya que esto será usado para determinar los umbrales bajo, medio y alto para levantar alertas cuando una consulta acumular tiempos de espera encima de los valores esperados.

Para establecer lo mencionado anteriormente:

  1. Seleccione la instancia de SQL Server en el panel izquierdo SQL Server instances.
  2. Haga clic en el botón Metrics en la sección Configure del menú principal.
  3. En la pantalla Metrics, seleccione la pestaña Queries, donde las opciones de configuración para las esperas de consultas serán mostradas.

Quick tip icon

Consejo rápido:

El monitoreo de consultas está habilitado automáticamente para cada instancia de SQL Server añadida, y ApexSQL Monitor comenzará a recolectar los datos de esperas de consultas inmediatamente después de añadir la instancia SQL Server para monitorear.

Este monitoreo puede ser deshabilitado para la instancia SQL Server seleccionada en la pestaña Queries de la pantalla Metrics, deseleccionando la casilla Enable query monitoring.

Cambie el valor en la opción Store querieswithwait time biggerthan para evitar recolectar información acerca de las consultas con un tiempo de espera menor que el definido explícitamente por el usuario. El tiempo por defecto de ApexSQL Monitor es 1 segundo, para asegurar la resolución alta de recolección de consultas. Tal valor por defecto es establecido para permitir a los DBAs que son menos experimentados o aún no suficientemente familiarizados con SQL Server recolectar suficientes datos inicialmente para tomar decisiones con conocimiento de causa, ya sea que desean/necesitan cambiar el ese valor.

Los umbrales de Desencadenadores de alertas para bajo, medio y alto están establecidos a 5,000, 20,000 y 60,000 ms respectivamente, por defecto. Esos son los valores de umbral “seguros” que deberían evitar las alertas excesivas de esperas de consultas después de que el monitoreo de desempeño ha empezado, pero también informan proactivamente cuando algunas consultas con tiempo de espera excesivos son detectadas.

Por favor, note que estos valores son altamente dependientes del sistema/SQL Server que está siendo monitoreado, y si muchas o muy pocas alertas son detectadas al analizar las consultas recolectadas, esos valores deberían ser cambiados para acomodar los específicos de cada sistema/SQL Server monitoreado (baje los valores si no hay alertas incluso cuando SQL Server está experimentando problemas, o eleve los valores en caso de que alertas de falso positivo sean gatilladas).

Ahora que la característica de esperas de consultas está configurada apropiadamente, el DBA puede revisar los datos de esperas de consultas recolectados y las notificaciones recibidas como relevantes y suficientes para investigar los problemas de desempeño de SQL Server vía la característica de esperas de consultas.

Para propósitos de este artículo, un caso de uso del mundo real será presentado para mostrar cómo la característica de esperas de Consultas puede ayudar a identificar la causa raíz del problema de desempeño de SQL Server.

Usando la característica de esperas de Consultas en la resolución de problemas de desempeño

El DBA recibió un ticket de un usuario diciendo que la aplicación se ralentizó significativamente durante 45 minutos entre 12 PM y 1 PM, causando la interrupción en el flujo de trabajo normal y el procesamiento de datos. La primera reacción del DBA es revisar qué consultas estaban corriendo cuando el problema de desempeño reportado fue registrado e identificar las consultas que no pueden ser la causa raíz del problema:

  1. Seleccione la instancia SQL Server en el panel izquierdo contra la que el usuario corre su aplicación.
  2. Para acceder a la pestaña Query waits, seleccione Charts en el menú principal y luego la pestaña Query waits.
  3. Elija el día en que el problema de desempeño fue reportado (por defecto después de acceder la pestaña, el día actual será usado para mostrar los datos de esperas de Consultas).

En este caso particular, dado que el periodo específico debería ser investigado, seleccione la hora específica cuando el problema de desempeño fue reportado, en este caso 12 PM.

El gráfico mostrará qué consultas hay con los tiempos de espera más altos. Las consultas con los tiempos de espera más altos acumulados durante el periodo elegido de tiempo serán mostrados en orden descendiente de izquierda a derecha.

Está claro por el gráfico que hay una consulta que se destaca en ese periodo particular de tiempo, y esta información inicial permite reducir el problema de desempeño. El siguiente paso debería ser investigar esta consulta a detalle.

La tabla en la derecha en la pestaña Querywaits muestra todas las ejecuciones de consultas individuales en el periodo de tiempo seleccionado. Ya que ahora queremos investigar la consulta “Q#1” a detalle, es recomendado usar la cuadrícula para filtrar todas las consultas excepto “Q#1”.

Después de filtrar, sólo la consulta “Q#1” será mostrada en la cuadrícula. Desde esta vista, es fácil obtener algo de información adicional, esencial para la investigación del problema y la localización del cuello de botella. Los tiempos de espera de la consulta “Q#1” para este periodo parecen ser altos con más de 5 segundos para cada ejecución.

Para obtener más detalles acerca de la ejecución individual de consultas, selecciona “+” al principio de la fila.

La pestaña Stats es mostrada como la pestaña por defecto, después de expandir los detalles, y la información más importante para la resolución de problemas puede ser encontrada aquí. En el gráfico en la izquierda, los tipos de estadísticas de espera que fueron recolectadas para esta ejecución son mostradas, y como puede ser visto, el tipo de espera LCK_M_X es predominante para esta consulta con un alto tiempo de espera de 4.3 segundos, mientras que PAGEIOLATCH_EX es el segundo con 0.6 segundos. Esos dos tipos, LCK_M_X primariamente, son obviamente la causa de la ejecución lenta de la consulta y probablemente esto es lo que causó que la aplicación funcionara lentamente. El tipo de espera WRITELOG, con un tiempo de espera pequeño de 82 ms, es obviamente algo que debería ser dejado fuera de la resolución de problemas, ya que obviamente WRITELOG no tenía ningún impacto significativo en el tiempo total de espera para esta consulta.

Incluso aunque esto se ve como un caso claro donde la causa raíz es determinada, y ahora el foco debería ser investigar cada uno de esos dos tipos de esperas y causas para tales tiempos de espera altos, hay un paso más que tiene que ser hecho antes de ir más adelante con la resolución de problemas. El DBA debería revisar si esta es la desviación del comportamiento normal de la consulta, o talvez la consulta es regularmente lenta, lo cual significaría que puede que no sea la causa raíz para el problema de desempeño de SQL Server que es sujeto de esta investigación.

Teniendo esto en mente, el siguiente paso lógico sería revisar el historial de ejecución de esta consulta. Para hacer eso, seleccione la pestaña History.

Es obvio desde el gráfico History que el tiempo de la consulta tuvo un pico sólo y exactamente en el periodo cuando el problema de desempeño fue reportado.

Sólo para referencia, es usual que, durante la investigación de la consulta, todo apunte a una consulta específica hasta revisar la pestaña de historial. EL DBA podría fácilmente saltar a una conclusión incorrecta sin revisar el historial del comportamiento de la consulta y, por tanto, trabajar sobre una indicación de falso positivo del problema. Podría ser muy sorpresivo después de revisar el historial de la consulta, sólo para ver que tal valor alto es realmente el comportamiento regular de esa consulta particular y, como tal, es poco probable que sea la causa real del problema. Para ilustrar esto, vea el gráfico de abajo.

Este gráfico particular muestra que tales tiempos altos son estándar para la consulta sospechosa y que tiempos de espera altos similares son usuales a través de todo el periodo, incluyendo los periodos cuando SQL Server no era afectado con el problema de desempeño reportado. Este ejemplo sirve para demostrar la importancia de revisar el historial de la consulta y su comportamiento en diferentes periodos de tiempo; lo que inicialmente se ve como la consulta con algunos altos valores de tiempo de espera que vale la pena investigar y solucionar, podría ser rechazado como conclusión temprano en el proceso de investigación después de revisar los datos históricos.

Una última revisión que tiene que ser hecho es el comportamiento de la consulta en la semana previa en el mismo día cuando SQL Server no fue afectado. Esto es importante, ya que comparar el mismo día una semana antes, cuando SQL Server no estaba afectado con ningún problema de desempeño, confirmará que esos valores de estadísticas de espera específicos que fueron leídos durante el periodo cuando SQL Server experimentó problemas de desempeño, no son sólo más altos comparando a los valores de estadísticas de espera medidos en diferentes partes de ese día, sino que también que hay una discrepancia comparando al mismo día bueno en la semana previa. En este caso particular, el historial debería ser revisado para el 28 de julio.

La imagen de arriba muestra que los valores de la consulta “Q#1” están todos debajo de 2,000 ms y que no hay picos en ese periodo. Ahora podemos estar seguros de que el comportamiento y el pico para la consulta “Q#1” no es normal y que esta consulta en particular es la causa más probable de problemas de desempeño experimentados durante el rango de tiempo reportado.

Adicionalmente, ApexSQL Monitor puede proveerle algunos detalles más acerca de los tipos de estadísticas de espera involucrados en el problema de desempeño, así como algunos consejos útiles acerca de cómo resolver los problemas causados por estos tipos de espera vía los llamados diálogos de ayuda.

Para ver el diálogo de ayuda para el tipo de espera involucrado, simplemente haga clic en el nombre del tipo de Espera en la pestaña Details o History.

Esto abrirá el diálogo de ayuda con detalles y dispositivos. Abajo están los ejemplos de tipos de espera LXK_M_X y PAGEIOLATCH_EX involucrados en este artículo.

Para mantener la vista en esta consulta en el futuro, es recomendado nombrar esa consulta de tal manera que los problemas puedan ser identificados y reconocidos mucho más fácilmente si una situación similar se repite.

Para hacer eso:

  1. Vaya a la pestaña Rename.
  2. Ingrese el nombre deseado (nombres descriptivos o de acuerdo a la convención de la compañía son recomendados).
  3. Presione el botón Save.

Desde ahora en adelante, cada vez que esta consulta en particular sea mostrada en la pestaña Querywaits, será mostrada con el nuevo nombre asignado.

Recursos útiles:

julio 6, 2017