Monitorear el desempeño de SQL Server es una tarea compleja, ya que el desempeño depende de muchos parámetros de hardware y software. Es recomendado monitorear estos parámetros proactivamente para prevenir cualquier degradación potencial del desempeño. De todos modos, este no siempre es el caso. Si la degradación de desempeño ocurre, el primer paso hacia la resolución es determinar el problema, encontrar la causa y resolver el problema. Los problemas severos de desempeño requieren acciones y resoluciones rápidas. Problemas menos severos pueden tener una solución diferida. En cualquier caso, los Administradores de Bases de Datos (DBAs) deberían tomar acciones para prevenir estas situaciones en el futuro.
Eso no significa que cada ralentización de SQL Server es un problema de desempeño. Algunas acciones específicas requieren muchos recursos y ponen estrés en el servidor. Si estas acciones son ocasionales, no deberían ser consideradas como una amenaza seria y pueden ser ignoradas. Correr su SQL Server bajo estrés todo el tiempo debería ser analizado detalladamente. Es bueno saber cómo se comporta el sistema cuando no hay problemas de desempeño. Esto se puede obtener recolectando información de base acerca del recurso que se está usando.
Para saber qué está pasando con su SQL Server, inicie con el monitoreo de bases de datos, mire las métricas de desempeño durante el tiempo para crear líneas de base y tendencias para la operación normal, aísle el proceso que está usando muchos recursos. Luego usted podrá depurar y reparar los errores.
Diagnostique los problemas
Para iniciar la resolución de problemas, usted tiene que definir los síntomas primero. Los síntomas más comunes de fallas en el desempeño de SQL Server son CPU, memoria, red y cuellos de botella I/O, y consultas lentas.
Los cuellos de botella de CPU son causados por recursos de hardware insuficientes. La resolución de problemas comienza identificando a los usuarios de recursos de CPU más grandes. Picos ocasionales en el uso del procesador pueden ser ignorados, pero si el procesador está contantemente bajo presión, una investigación es necesaria. Añadir procesadores adicionales o usar uno más poderoso puede no solucionar el problema, ya que procesadores mal diseñados pueden usar todo el tiempo de CPU. La mejora de las consultas, el mejoramiento de los planes de ejecución y la reconfiguración del sistema pueden ayudar. Para evitar cuellos de botella, es recomendado tener un servidor dedicado que sólo correrá SQL Server y remover todo el otro software a otra máquina.
Los cuellos de botella de memoria pueden resultar en un tiempo de respuesta largo para la aplicación, una ralentización general del sistema o incluso el colapso de la aplicación. Es recomendado identificar cuando el sistema corre con memoria insuficiente, qué aplicaciones usan la mayor parte de los recursos de memoria, o si hay cuellos de botella para otros recursos del sistema. Revisar y afinar consultas, reconfiguración de la memoria, y añadir más memoria física puede ayudar.
Los cuellos de botella de red pueden no ser reconocidos inmediatamente, ya que a primera vista pueden ser considerados como problemas de desempeño de SQL causados por otros recursos. Por ejemplo, una demora de datos enviados por la red puede parecer una respuesta lenta de SQL Server.
Los cuellos de botella I/O son causados por lecturas y escrituras excesivas de páginas de la base de datos al disco. Un cuello de botella es manifestado a través de tiempos de respuesta largos, ralentizaciones de la aplicación y expiración de tareas. Si otras aplicaciones usan recursos de disco excesivamente, puede que SQL Server no tenga suficientes recursos de disco para su operación normal y tendría que esperar para poder leer y escribir al disco.
Las consultas lentas pueden ser el resultado de índices perdidos, planes pobres de ejecución, mal diseño de aplicación y esquema, etc.
Causas comunes de problemas de desempeño
Las causas para los problemas de desempeño pueden ser varias, pero las más comunes son una base de datos pobremente diseñada, un sistema incorrectamente configurado, espacio de disco insuficiente u otros recursos de sistema, compilación y recompilación de consultas excesivas, y consultas o procedimientos almacenados que tienen tiempos de ejecución largos debido a un diseño inapropiado.
Los cuellos de botella de memoria son causados por limitaciones en la memoria disponible y la presión de memoria causada por SQL Server, el sistema u otra actividad de aplicación. Un indexado pobre requiere escaneos de tabla, lo cual, en tablas grandes, significa que un gran número de filas es leído desde el disco y manejado en memoria.
Los cuellos de botella de red son causados por una sobrecarga en un servidor o la red, de modo que los datos no pueden fluir como se espera.
Los problemas de I/O pueden ser causados por hardware lento, mal diseño de soluciones de almacenamiento, y la configuración. Aparte de los componentes de hardware, como tipos de discos, tipos de arreglos de discos, y configuraciones RAID que afectan el desempeño I/O, solicitudes innecesarias hechas por la base de datos también afectan al tráfico I/O. Escaneos frecuentes de índices, consultas ineficientes y estadísticas desactualizadas también pueden causar sobrecargas I/O y cuellos de botella.
Qué métricas monitorear
Para comenzar a solucionar los problemas de desempeño más comunes de CPU, monitoree % Processor Time. Este contador está disponible en Performance Monitor. Si su valor es constantemente más alto que 80%, el procesador está bajo presión.
Los contadores que indican las causas más comunes para la presión al procesador son Batch Requests/sec, SQL Compilations/sec y SQL Recompilations/sec. Estos contadores están disponibles en Performance Monitor y en la vista sys.dm_os_performance_counters.
|
1 2 |
select * from sys.dm_os_performance_counters where counter_name in ('Batch Requests/sec', 'SQL Compilations/sec' , 'SQL Re-Compilations/sec') |
Note que el tipo de contador para los tres contadores es 272696576 y que los valores mostrados son acumulativos desde el último inicio de SQL Server, así que tienen que ser calculados. Uno de los métodos es tomar dos muestras con una demora de 10 segundos.
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @BatchRequests BIGINT; SELECT @BatchRequests = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Batch Requests/sec'; WAITFOR DELAY '00:00:10'; SELECT (cntr_value - @BatchRequests) / 10 AS 'Batch Requests/sec' FROM sys.dm_os_performance_counters WHERE counter_name = 'Batch Requests/sec'; |
El valor de Batch Requests/sec depende del hardware usado, pero debería estar debajo de 1000. El valor recomendado para SQL Compilations/sec es menos que 10% de Batch Requests/sec y para SQL Re-Compilations/sec es menos que 10% de SQL Compilations/sec.
Para problemas de memoria, monitoree el contador de desempeño Memory Available KB. Los valores normales deberían estar encima de 200 MB. Si el valor del contador Memory Available KB es menos que 100MB por un largo tiempo, es una indicación clara de insuficiente memoria en el servidor. Este contador está disponible en Performance Monitor, y dos contadores adicionales y útiles – Total Server Memory (KB) y Target Server Memory (KB) están disponibles vía la vista sys.dm_os_performance_counters.
Otro contador a monitorear es Pages/sec, está disponible en Performance Monitor. Muestra el ritmo al cual las páginas son escritas desde el disco a la memoria RAM y leídas desde la RAM al disco. Los valores mayores a 50 muestran actividad intensiva de la memoria y posibles sobrecargas y presión de memoria que puede llevar a la degradación del desempeño de SQL Server.
Checkpoint pages/sec y Lazy writes/sec indicant si páginas sucias son enviadas al disco muy frecuentemente. Las páginas sucias son automáticamente enviadas al disco en un punto de control. Si el espacio libre disponible en el caché del búfer entre dos puntos de control es poco, una escritura física ocurrirá para limpiar las páginas del búfer al disco y liberar la memoria. El valor de lazy Writes/sec debería estar debajo de 20. Ambos contadores están disponibles en Performance Monitor y la vista sys.dm_os_performance_counters, pero como el tipo del contador es 272696576, los valores retornados por la vista deberían ser calculados.
Si el valor de Lazy Writes/sec está constantemente encima del umbral, revise el valor de Page Life Expectancy. Los valores debajo de 300 segundos indican presión a la memoria. El contador está disponible en Performance Monitor y la vista sys.dm_os_performance_counters, no se necesita hacer cálculos adicionales.
Buffer Cache Hit Ratio muestra la proporción de páginas de datos encontradas y leídas desde la caché del búfer de SQL Server y todas las solicitudes de páginas de datos. Si una página no existe en la caché del búfer, tiene que ser leída en el disco, lo cual degrada el desempeño. El valor recomendado está encima de 90. El contador está disponible en Performance Monitor y la vista sys.dm_os_performance_counters.
Ya que el tipo del contador es 537003264, el valor retornado por la vista tiene que ser calculado para obtener el valor actual. Para hacer eso, es necesario usar también el valor de Buffer Cache Hit Ratio Base.
Buffer Cache Hit Ratio % = 100 * Buffer Cache Hit Ratio / Buffer Cache Hit Ratio Base
= 100 * 1,797 / 1,975
= 90.98%
La resolución de problemas de red debería comenzar encontrando consultas, funciones y procedimientos almacenados que tiene un tiempo lento de respuesta. Si son ejecutados rápidamente, pero con una gran demora entre las dos llamadas, puede ser una indicación de un problema de red. SQL Server Profiler puede ser usado para determinar qué consultas, funciones y procedimientos almacenados fueron ejecutados.
Para problemas de I/O, monitoree los contadores relacionados con el disco: Average Disk Queue Length, Average Disk Sec/Read, Average Disk Sec/Write, %Disk Time, Average Disk Reads/Sec, y Average Disk Writes/Sec. Todos los contadores están disponibles en Performance Monitor.
Average Disk Queue Length muestra el número promedio de operaciones I/O que están esperando a ser escritas a o leídas desde el disco y el número de escrituras y lecturas procesadas actualmente. El valor recomendado es debajo de 2 por disco individual, y valores más altos indican cuellos de botella de I/O.
Average Disk Sec/Read muestra el tiempo promedio en segundos necesarios para leer los datos del disco. Los valores recomendados están dados por categorías, donde debajo de 8 milisegundos es un desempeño excelente, y más de 20 milisegundos es un problema serio de I/O.
Average Disk Sec/Write muestra el tiempo promedio en segundos necesario para escribir datos al disco. El desempeño es excelente si el valor está debajo de 1 milisegundo y malo si el valor del contador es más alto que 4 milisegundos.
Average Disk Reads/Sec y Average Disk Writes/Sec muestran el ritmo de operaciones de lectura y escritura en el disco, respectivamente. Los valores bajos indican un procesamiento I/O del disco lento, y se recomienda revisar el uso del procesador y las consultas que usan mucho disco. Los valores normales dependen de la especificación del disco y la configuración del servidor. Estos contadores no tienen un umbral específico, así que es recomendado monitorear estas métricas durante un tiempo y determinar las tendencias y establecer una línea base.
El desempeño de SQL Server puede ser afectado por muchos factores. Cuando se está resolviendo problemas, es necesario saber dónde empezar, para saber los valores normales para los contadores de desempeño y para seleccionar una herramienta que proveerá suficiente información para analizar y solucionar los problemas.
Recursos
- Solucionando problemas de desempeño en SQL Server 2008 – reporte
- Cinco cosas que arreglan un mal desempeño de SQL Server
- Cómo solucionar problemas de desempeño de SQL Server
- Afinamiento de desempeño de SQL Server
Ella ha estado trabajando con SQL Server desde 2005 y tiene experiencia con SQL 2000 hasta SQL 2014.
Su tema favorito de SQL Server son las recuperaciones de desastres, la auditoría y el monitoreo de desempeño.
Vea todas las entradas de Milena “Millie” Petrovic
- Una guía de DBA para la solución de problemas de rendimiento de SQL Server – parte 2 – utilización de la supervisión - May 7, 2019
- Planes de ejecución de consultas – Entendiendo y leyendo los planes - April 19, 2018
- Mala indexación de base de datos – El asesino de las consultas SQL – recomendaciones - April 18, 2018