Bojan Petrovic

Las herramientas de supervisión de SQL Server para el rendimiento de e/S de disco

May 12, 2019 by

El objetivo del presente artículo es poder familiarizarse con las herramientas de supervisión del SQL Server y además lograr identificar cuáles son los problemas cotidianos del rendimiento del SQL Server.

El saber cómo manejar los problemas de rendimiento no es nada fácil, pero todo se trata de I/O, CPU, la memoria o las métricas relacionadas con la simultaneidad. Las herramientas de supervisión del SQL Server nos pueden ayudar a tener una mejor visión general. Es muy importante poder identificar cuál de estos es el causante del problema. Muchas veces puede ser la combinación de dos o más, o podría parecer uno, siendo que en realidad es el otro.

Comencemos mencionando algunos de los problemas de rendimiento comunes:

  • Un elevado I/O durante horas pico en los periodos de mantenimiento.
  • Sobrecarga de memoria lo que vuelve lentas las consultas.
  • Sobrecarga del CPU ralentizando el servidor de bases de datos.
  • Problema de simultaneidad de cierre/ bloqueo.

Un elevado número de I/O representa que los subsistemas de disco realizan demasiado trabajo. Especialmente si se tiene una carga elevada o en los periodos de mantenimiento. Los cuellos de botella de entrada/salida pueden ser los más complicados de solucionar porque normalmente se manifiestan a partir de problemas de memoria o CPU. Por otra parte, también se pueden manifestar a partir de consultas mal enunciadas y no tener una buena estrategia para hacer la indexación.

Vale aclarar que, la estrategia de indexación, es una gran pieza del rompecabezas que no se puede lograr obtener fácilmente. Pero aquí hay un artículo que con suerte lo encontrará informativo y útil: el mantenimiento del índice SQL

Tome en cuenta que una indexación adecuada y el ajuste de consultas pueden llegar a tener un impacto significativo en todas las áreas del rendimiento de SQL Server.

Todos los mencionados factores son una excelente razón para ver al final el I/O, pese a que está en primer lugar en la lista, muy importante no sólo para descartar cualquier otra cosa que puede causar problemas en el I/O.

La sobrecarga de la memoria puede transformarse en un problema cuando el sistema no tiene suficiente memoria RAM para el SQL Server ya que no puede asignar la cantidad suficiente de memoria o más comúnmente una mala base de datos y un diseño de consultas. La falta de memoria también puede ser causada por un elevado I/O. ya que, va en ambos sentidos. Esto es de nuevo, el por qué puede ser complicado solucionar problemas de rendimiento de SQL Server.

Es importante puntualizar que, entre estos dos, la sobrecarga de memoria se produce aleatoriamente, por ejemplo, cuando una consulta sin procesar extrae la totalidad de datos de una tabla y absorbe toda la memoria de SQL Server. Un elevado I/O sucede en momentos específicos. En general, es posible decir por ejemplo que, si todos los días a las 3 PM el SQL Server llega a fallar, entonces es muy probable que tenga un problema de I/O en lugar vez de problemas de memoria.

La sobrecarga del CPU puede venir de una variedad de problemas, pero usualmente, proviene de consultas complejas o factores externos. Primeramente, se debe enfrentar con los problemas externos. Podría ser una aplicación que se está ejecutando en el servidor de base de datos y está utilizando todos los recursos. Pero, como se mencionó antes, las sobrecargas del CPU también pueden ser la causa de un elevado I/O.

Para finalizar, pero sin despreciar su importancia, la simultaneidad puede causar problemas de rendimiento, por lo tanto, esto es un problema difícil de descifrar. Debido a que incluso si los niveles del I/O, memoria y CPU son normales, todavía se puede tener personas quejándose del tiempo espera y de lentitud en una aplicación. En este caso probablemente son problemas de cierre y bloqueo.

En resumen, hay que obtener el panorama general, también tenemos que tomar en cuenta todos los factores mencionados previamente para lograr solucionar los problemas de rendimiento. Para poder lograrlo, se tienen muchas herramientas de supervisión del SQL Server.

Algunas de las herramientas más comunes para la monitorización del rendimiento son:

  • Vistas de gestión dinámicas
  • Monitoreo del rendimiento
  • Eventos extendidos
  • Planes de ejecución
  • Recopilador de datos
  • Administrador de recursos

Están a disposición los DMV como parte de las herramientas de supervisión de SQL Server para poder solucionar todos los problemas que son comunes en cuanto a rendimiento y vamos a buscar en algunos de ellos los que se mencionan en este artículo. También se va a ver el monitor de rendimiento y los contadores que son más populares utilizados para poder identificar problemas más comunes de rendimiento del I/O. El perfil de eventos extendidos que se utiliza para poder registrar toda la información dentro de SQL Server. Los planes de ejecución también llegan a ser muy importantes junto con el asistente de configuración del motor de base de datos para ayudarnos con la optimización de consultas y el ajuste preciso. El recopilador de datos también puede usarse para supervisar el rendimiento y SQL Server el mismo que incluye su propio recopilador de datos, así que compila las estadísticas de rendimiento. Finalmente, se tiene el regulador de recursos que es adecuado para controlar los recursos en el nivel del SQL Server.

Generalmente cuando se llegan a solucionar problemas de rendimiento, no se puede utilizar solo una de las herramientas de supervisión de SQL Server. Esas herramientas son la representación de un ecosistema y se va a requerir de muchas otras herramientas combinadas para poder visualizar el panorama general. Entender la forma en que funciona cada una de las herramientas va a ser un largo camino cuando se trata de poder resolver problemas de rendimiento.

Vayamos a SSMS y echemos un vistazo a la manera de poder identificar problemas comunes de rendimiento de I/O mediante la utilización de algunas de las herramientas mencionadas previamente.

Inicialmente, veremos cómo podemos simular escrituras/lecturas y usar DVMs más comunes para extraer información útil de SQL Server. Pero antes de continuar, algo muy importante al resolver problemas es que casi todo debe tener una base de datos. Esto le proporcionará un mínimo o por lo menos un punto de partida utilizándolo para las comparaciones. Dicho de otro modo, tener una base de datos nos dará una idea de lo que es normal. Para obtener resultados, tendríamos que dividir varias herramientas de supervisión de SQL Server como monitor de rendimiento, eventos extendidos, etc. El recopilar datos, por ejemplo, en un día tranquilo le permitirá saber lo que es normal. En última instancia, esto hará que sea mucho más fácil averiguar qué es anormal.

En adelante, algunos de los síntomas del problema con el I/O son:

  • Alta cantidad de tipos de espera del I/O
  • Disminución del rendimiento durante momentos específicos
  • Latencia excesiva del disco
  • ADVERTENCIA del I/O, son registros de errores del SQL Server

Las DMV útiles en los casos anteriores que nos pueden ayudar a identificar cuellos de botella con I/O son:

Hay muchas estadísticas de espera en SQL Server y hay una gran documentación en línea, pero una lista de los tipos y pasos más comúnmente encontrados que se deben tomar para la solución de problemas más serios se puede encontrar en: tipos de espera de SQL Server

Cada vez que un subproceso se ejecuta y espera, por ejemplo, disco, CPU, consulta, etc., todo se almacena dentro de estas estadísticas. Además, tenemos las tareas de espera que devuelven información sobre la cola de espera de tareas en tiempo real de cualquier cosa que esté esperando actualmente. Finalmente, tenemos estadísticas de archivos virtuales que retornan estadísticas del I/O para datos y registros. Consiguientemente, vamos a ejecutar el SSMS y ver estadísticas generales de vista mediante la ejecución de la consulta desde abajo:

El resultado de esta consulta será un total en ejecución de cualquier momento en el que un subproceso esperó en algo:

The result of this SQL Server monitoring tools query will be a running total of any time a thread waited on something

Tenga en cuenta que, en la barra de estado, retornan 875 filas. Esto significa que hay tantas categorías diferentes AKA tipos de espera en SQL Server. El número total puede variar en función de la versión de SQL Server:

SQL Server monitoring tools query results showing the presence of many wait types

Esencialmente, lo que debe buscar entre estos tipos de espera al solucionar problemas de I/O es cualquier línea que empiece con PAGEIOLATCH. Cualquier línea con esto en frente simplemente significa que hay un retraso al escribir en los archivos de base de datos físicos.

A continuación, podemos ejecutar otra consulta para ver la tarea actual en espera:

Esta consulta retorna a la tarea actual que actualmente tiene un tipo de espera específico. El ejemplo anterior devolverá algo cuando una tarea está esperando en una pestaña para un búfer que está en una solicitud de I/O. La mía no devolvió nada.

Para ver las estadísticas de espera de I/O por base de datos/archivos, ejecute la siguiente consulta:

Para retornar las estadísticas de retraso para todas las bases de datos, siéntase libre de pasar a anular en el lugar del nombre de la base de datos. Pero, en última instancia, la consulta devuelve estadísticas del I/O para datos y archivos de registro:

SQL Server monitoring tools query result to return latency statistics for all databases

Esas estadísticas de atrasos se desglosarán por archivo y notarás que el resultado está ordenado por una columna llamada “io_stall”. Esta es una columna importante porque nos indica cuánto tiempo esperó un usuario para que los datos se escriban en ese archivo en milisegundos. Por lo tanto, esto podría ser una buena indicación si previamente configuramos nuestras bases de datos aquí y nos damos cuenta de que algunos picos son más altos de lo habitual, entonces esto podría ser una indicación de que un disco tiene demasiada actividad en él o tal vez necesita actualizarse.

Vamos a verificar a algunos de los contadores de rendimiento que podemos usar para solucionar problemas del I/O. Al usar cualquier herramienta de supervisión del SQL Server como, por ejemplo, monitor de rendimiento, probablemente se verán eventos como el disco lógico/físico. Disco sec/lectura/escritura/contadores de transferencia. Lógicas y físicas son categorías separadas y ambas deben ser rastreadas. Físico se refiere a un HDD o SSD físico real (o matriz en una configuración RAID de hardware), mientras que la lógica hace referencia a un volumen que se ha creado en ese disco. Los contadores de lectura, escritura y transferencia miden el tiempo necesario en milisegundos para que una solicitud de I/O sea atendida por solicitud lógica o física. Preferentemente, este valor debe ser menor a 10 ms, entre 10 y 15 es aceptable y más de 15 debe ser problema a solucionar. Por otra parte, si el tiempo de inactividad es constantemente inferior al 50 %, esto significa que nuestros discos están siempre ocupados, y es posible que tengamos que mirar eso también.

Tenemos dos herramientas útiles de supervisión de SQL Server o métricas que son simples contadores que podemos usar en esta categoría y que son exploraciones completas y búsquedas de índices. Estos índices nos darán una idea de la cantidad del I/O adicional que se está produciendo debido a la falta de los mismos o a una mala estrategia de indexación. En la práctica, si los exámenes completos por segundo son altos, entonces debemos solucionar el problema con el Asistente de optimización del motor de la base de datos para obtener algunas sugerencias de índice. Sin embargo, si las búsquedas de índices son bajas, entonces debemos solucionarlo de nuevo porque es una indicación de que la estrategia de indexación no está funcionando.

Si su SQL Server esta repentinamente más lento de lo que se recuerda, una de las mejores herramientas de supervisión de SQL Server es en realidad el monitor de rendimiento en Windows que se puede usar para llegar al final del problema. Vamos a eliminar a este problema y ver cómo podemos trabajar con algunos de los contadores mencionados antes. Utilice el atajo en el teclado: tecla de Windows + R para abrir el comando ejecutar, escriba “Perfmon” y haga clic en Aceptar para abrir:

One of the best SQL Server monitoring tools is perfmon. It can be opened via Run command

La primera vez que abra esta herramienta de supervisión de SQL Server, la misma se abrirá en la Página principal con una vista general clásica donde podemos ver los contadores. Lo que es importante es crear un conjunto de recopiladores de datos personalizado que es cómo podemos crear conjuntos personalizados con contadores de rendimiento y alertas basadas en criterios específicos. Por ello vamos a crear rápidamente un conjunto de recopiladores de datos personalizado solo para guiarlos a través de todos los pasos del asistente y para ver cómo recopilar información de rendimiento específico.

Expanda Data Collector Sets, haga clic en User Defined, seleccione New, y haga clic en Data Collector Set:

Opening SQL Server monitoring tools interface to observe watch counters

Asigne un nombre a este nuevo recopilador de datos, no queremos crearlo a partir de una plantilla, así que cambie a la opción crear manualmente (create manually) (avanzado) y haga clic en siguiente (next):

SQL Server monitoring tools interface to create a new Data collector set

En cuanto al tipo de datos, queremos realizar un seguimiento de los contadores de rendimiento, así que verifique la opción contador de rendimiento y haga clic en siguiente (next):

SQL Server monitoring tools interface to create a new Data collector set and specify data logs

Seguidamente, el asistente le preguntará qué contadores de rendimiento le gustaría registrar. Se pueden agregar contadores simplemente haciendo clic en el botón Agregar… opción y localizarlos en la lista. Por ejemplo, podemos localizar SQLServer:Access Methods, y expandirlo para encontrar Full Scans/sec y los contadores Index Searches/sec. También puede seleccionar varios contadores manteniendo presionada la tecla Mayús y luego simplemente haga clic en el botón Agregar > > para adicionarlos. Además, si necesita información complementaria sobre cualquier contador específico, simplemente marque la opción Mostrar Descripción y le dará una descripción adicional que es útil. Una vez que todo está hecho, haga clic en OK para cerrar el diálogo:

Using SQL Server monitoring tools to display and select performance counters

Pulse siguiente dos veces y, a continuación, finalizar y el asistente creará el conjunto de recopiladores de datos personalizado:

Using SQL Server monitoring tools to specifiy output location of saved performance data

Una vez que haya completado todos estos pasos, puede hacer clic con el botón derecho en el recopilador de datos recién creado en la categoría definido por el usuario y hacer clic en iniciar para ejecutarlo o detener para apagarlo:

Starting SQL Server monitoring tools to collect SQL Server performance data

Ya he pre-configurado uno para monitorear la actividad de I/O de los discos en tiempo real. También lo he empezado y dejo que recopile datos durante un tiempo para recopilar datos. Esto se puede ver en la carpeta de informes, categoría definida por el usuario:

SQL Server monitoring tools data collection reporting status

Una vez capturados los datos durante el período deseado, detenga el conjunto de recopiladores de datos. Esto creará un informe instantáneo y esto es lo que parece:

SQL Server monitoring tools collected SQL Server data performance visualizaiton

Este sistema local que estoy usando diariamente está configurado bastante bien. Esto no se debe a que soy un experto o algo así, es simplemente porque, aunque tengo varias versiones de SQL Server instaladas, no hay mucha variedad en ellos, excepto que me permita probarlos ocasionalmente.

Para alternar entre diferentes tipos de gráficos, utilice la opción Cambiar tipo de gráfico a la izquierda del signo más (+) o pulse Ctrl + G en el teclado. Es fácil usar esto y si miramos el informe todo está en los rangos donde debe estar ubicados:

SQL Server monitoring tools aggregate performance data

Como se ha mencionado antes, el tiempo de inactividad es bueno, si el valor está por debajo 50 % es malo, cualquier indicador superior es bueno, sobre 85 % es el valor esperado. Toda la tasa general de operaciones de lectura/transferencia/escritura en el disco es inferior a 0,010 (10 ms), que también es buena. Esta imagen es tan buena como llega en cuanto a rendimiento en un sistema, pero este ejemplo es artificial en un equipo donde casi nada está sucediendo. Consiguientemente, voy a activar una utilidad de simulación que se puede utilizar para realizar pruebas de confiabilidad e integridad en el subsistema de disco. Esta es otra gran herramienta de supervisión de SQL Server que se envía dentro de SQL Server. Se llama SQLIOSim y básicamente simula las actividades de lectura, escritura, punto de comprobación, copia de seguridad, ordenación y lectura anticipada para SQL Server.

Antes de ejecutarlo, comencemos de nuevo el recopilador de datos del I/O. Como se mencionó anteriormente, la herramienta de supervisión de SQL Server está disponible de inmediato. Por lo tanto, sólo tenemos que localizar el SQLIOSIM.EXE dentro de la carpeta de instancia Binn en el sistema de archivos de SQL Server, en mí caso con SQL Server 2016 es:

C:\Archivos de Programa\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\Binn

Locating SQL Server monitoring tools exe to open SQLIOSIM.exe

Ejecute esta herramienta como administrador sólo para que no encuentre ningún error debido a los privilegios que faltan. Una vez que se ejecuta la herramienta, se abrirá el cuadro de diálogo de archivos y configuración. Al haberse pre-configurado esto. Básicamente, hay dos archivos en los que SQL server leerá y escribirá. El sqliosim.ldx es nuestro archivo de registro:

Configuring SQL Server monitoring tools by specifying files and configuration for sqliosim.exe

Y el archivo sqliosim.mdx va a pretender ser nuestro archivo principal de base de datos:

Configuring SQL Server monitoring tools by specifying files and configuration for sqliosim.exe including primary database file

Es importante mencionar que, se trata de una gran herramienta de supervisión de SQL Server para descubrir lo que puede controlar el subsistema de I/O. Una vez que la configuración está hecha, lo que se debe hacer es pulsar la tecla OK para cerrar el diálogo y en la parte superior izquierda verá un pequeño icono verde que es un botón de Inicio:

SQL Server monitoring tools start icon

En consecuencia, aunque esta herramienta de supervisión de SQL Server imita la actividad de SQL Server en un subsistema de disco, el conjunto de recopiladores de datos de I/O supervisará la actividad en segundo plano:

Using SQL Server monitoring tools to simulate SQL Server activity on a disk subsystem

Es decir que no sólo hay que ejecutar esto en su servidor de producción, por lo que podría tener problemas. Sin embargo, si las cosas se salen de control, siempre se puede detener la prueba de estrés pulsando el icono de detener (stop):

Stopping monitoring with SQL Server monitoring tools

He detenido este proceso después de unos 5 minutos y así es como el nuevo informe se muestra a continuación:

SQL Server monitoring tools database performance results

Es obvio que los resultados son muy diferentes y si los sustituimos para reportar el tipo de gráfico aquí es lo que a continuación tenemos

SQL Server monitoring tools aggregate results and performance data

Como el tiempo de inactividad está todavía por encima del 50 % que es aceptable. Las lecturas también están bien, pero las transferencias y las escrituras no lo son. Ahora, obviamente, la utilidad SQLIOSim está destinada a manejar una carga pesada, pero si estamos a punto de ejecutar este recopilador de datos establecido en un entorno de producción durante un día o dos y así poder obtener resultados como este, entonces esto definitivamente sería una causa de preocupación.

El resultado final es que esos contadores son excelentes para una indicación de cómo funcionan los subsistemas de disco del I/O.

ES muy importante recomendar echar un vistazo a otro artículo escrito sobre lo más importante a cerca de las métricas de rendimiento de disco, ya que los mismos describen los contadores que hemos estado utilizando a través de este artículo en detalle: métricas de rendimiento de disco de SQL Server –parte 1 – lo más importante de los rendimientos de las métricas del disco.

De manera muy personal prefiero otra herramienta de supervisión de SQL Server llamada ApexSQL Monitor. Esta herramienta permite supervisar los detalles suficientes para un análisis y solución de problemas más profundos que el monitor de rendimiento en Windows. Seguidamente se incluye cómo supervisar todas las métricas del sistema, agregada la del I/O mediante ApexSQL monitor. Para ello instale el programa y ejecútelo. Luego se abrirá una página de inicio de sesión en el explorador Web predeterminado. Inicie sesión con la información proporcionada durante la instalación de la aplicación y haga lo siguiente:

  1. Vaya a la página Agregar instancia
  2. En la lista desplegable de instancias disponibles, seleccione una que desee supervisar (solo las instancias locales se detectan automáticamente y se enumeran en la lista desplegable)
  3. Deje el método de autenticación de Windows, ya que es esencial para la supervisión del sistema
  4. Deje todo lo demás como está y haga clic en el botón Agregar

SQL Server monitoring tools login information

Tras una operación exitosa, se le conducirá a la página de inicio de la aplicación. De forma predeterminada, se supervisan todas las métricas del sistema disponibles en la aplicación. Para realizar los cambios en las métricas del sistema supervisado, usted debe seleccione la instancia de SQL Server recién agregada en el panel izquierdo y, a continuación, debe ir a la página Configuración. De forma predeterminada, se seleccionará la categoría de métricas del sistema y aquí puede Agregar o quitar una métrica de la supervisión y realizar cambios adicionales:

SQL Server monitoring tools performance metric configuration

Para obtener instrucciones detalladas sobre la monitorización del sistema, efectúe la consulte de la documentación oficial de la aplicación:

Asimismo, si recuerda desde el principio del artículo, una cosa muy importante al solucionar problemas de rendimiento las que son las base de datos. Es muy fácil calcularlos con ApexSQL monitor. He aquí cómo hacerlo:

  1. Seleccione una instancia en el panel izquierdo
  2. Vaya a la página de configuración
  3. Cambie el submenú a baselines
  4. En la categoría rendimiento del sistema, seleccione, por ejemplo, el tiempo medio de lectura y los contadores temporales de escritura promedio
  5. Seleccione el intervalo de tiempo deseado
  6. Haga clic en el botón calcular y espere hasta que se realice la operación (se requieren al menos 7 días de datos para calcular las líneas de base)

Using SQL Server monitoring tools for creating performance baselines

A continuación, si navegamos a las métricas del sistema y vemos los detalles de la instancia seleccionada, se podrá observará los gráficos para todas las métricas del sistema y sus valores:

SQL Server monitoring tools graphs for system metrics and values

Y fundamentalmente, ahora que hemos calculado las bases de datos, podemos hacer clic en la opción Mostrar todas las bases de datos y también se mostrarán las bases de datos en el gráfico:

Using SQL Server monitoring tools to show all performance baselines

Al hacer clic en cualquier parte del gráfico, se desplegará una página adicional con información más detallada sobre el contador. De igual manera, si hace clic en el icono Mostrar base de datos en la esquina superior derecha, también mostrará las bases de datos:

Using SQL Server monitoring tools detailed performance baselines view

Para lograr obtener instrucciones más detalladas a cerca de las base de datos de rendimiento, consulte la documentación oficial de la aplicación:

Pese a que no he tocado problemas comunes de rendimiento que no se refieran de I/O, y esto resultó ser un artículo largo. Por consecuencia, voy a detenerme aquí, y luego continuaremos este tema de las herramientas de supervisión de SQL Server en otro artículo en el que vamos a poder analizar detalladamente el resto de la lista y hablar sobre las presiones de memoria.

Table of contents

SQL Server monitoring tools for disk I/O performance
SQL Server monitoring tools for memory performance
SQL Server monitoring tool for CPU performance

Bojan Petrovic

Bojan Petrovic

Bojan tiene pasión por trabajar con computadoras, servidores y tecnología de red, y electrónica. A él le gusta mantenerse actualizado con el desarrollo y lo más reciente en tecnología. Él trabaja actualmente en ApexSQL LLC como Ingeniero de Soporte con un grado de licenciatura en IT Red y Tecnología Electrónica.
Él ayuda a sus clientes con cualquier problema técnico y hace control de calidad (probar aplicaciones y prepararlas para producción) para ApexSQL Complete, ApexSQL Refactor y los complementos gratis de ApexSQL Search.

Ver todas las entradas de Bojan Petrovic
Bojan Petrovic
Monitoreo, Performance

Acerca de Bojan Petrovic

Bojan tiene pasión por trabajar con computadoras, servidores y tecnología de red, y electrónica. A él le gusta mantenerse actualizado con el desarrollo y lo más reciente en tecnología. Él trabaja actualmente en ApexSQL LLC como Ingeniero de Soporte con un grado de licenciatura en IT Red y Tecnología Electrónica. Él ayuda a sus clientes con cualquier problema técnico y hace control de calidad (probar aplicaciones y prepararlas para producción) para ApexSQL Complete, ApexSQL Refactor y los complementos gratis de ApexSQL Search. Ver todas las entradas de Bojan Petrovic

185 Views