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:
|
1 2 3 |
SELECT * FROM sys.dm_os_wait_stats dows ORDER BY dows.wait_time_ms DESC; |
El resultado de esta consulta será un total en ejecución de cualquier momento en el que un subproceso esperó en algo:
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:
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:
|
1 2 3 |
SELECT * FROM sys.dm_os_waiting_tasks dowt WHERE dowt.wait_type LIKE 'PAGEIOLATCH%'; |
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:
|
1 2 3 |
SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID('AdventureWorks2014'), NULL) divfs ORDER BY divfs.io_stall DESC; |
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:
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:
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:
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):
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):
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:
Pulse siguiente dos veces y, a continuación, finalizar y el asistente creará el conjunto de recopiladores de datos personalizado:
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:
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:
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:
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:
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
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:
Y el archivo sqliosim.mdx va a pretender ser nuestro archivo principal de base de datos:
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:
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:
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):
He detenido este proceso después de unos 5 minutos y así es como el nuevo informe se muestra a continuación:
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
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.
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 |
He has written extensively on both the SQL Shack and the ApexSQL Solution Center, on topics ranging from client technologies like 4K resolution and theming, error handling to index strategies, and performance monitoring.
Bojan works at ApexSQL in Nis, Serbia as an integral part of the team focusing on designing, developing, and testing the next generation of database tools including MySQL and SQL Server, and both stand-alone tools and integrations into Visual Studio, SSMS, and VSCode.
View all posts by Bojan Petrovic