La sobrecarga de la memoria vuelve lentas las consultas
Este artículo es parte de la serie sobre las herramientas de supervisión de SQL Server y los problemas comunes en el rendimiento. El primer artículo de herramientas de supervisión de disco I/O del rendimiento de SQL Server, es sobre cómo poder detectar y resolver un gran número entradas y salidas en los subsistemas del disco duro, al realizarse demasiado trabajo durante los tiempos de carga máxima o de mantenimiento.
En este artículo se puede leer de manera independiente, sin embargo, para obtener el panorama general, se recomienda leer la primera parte. Además, debido a que los problemas descritos con el rendimiento de I/O del disco se pueden manifestar a causa de problemas de memoria. Por tanto, siéntase libre de revisar el artículo inicial en el siguiente enlace: herramientas de supervisión de SQL Server para el rendimiento del I/O del disco.
Uno de los problemas más obvios respecto a la memoria es que en realidad no requiere ninguna herramienta de monitoreo del SQL Server para poder ser detectado sino es cuando el sistema simplemente no tiene suficiente memoria de acceso aleatorio AKA RAM. En dichos casos, la presión de la memoria puede producirse porque SQL Server simplemente no puede asignar suficiente cantidad de memoria.
Por suerte, este será un artículo corto en lugar de uno largo como lo hice con el rendimiento de I/O del disco, pero esto es esencial para obtener una imagen clara, completa y tomar en cuenta todas las cosas al momento de solucionar los problemas de performance. Si usted ya ha solucionado el problema de la memoria antes, entonces es probable que usted encontró algunos de los síntomas como la disminución del rendimiento de todo el sistema y aumento del I/O.
Herramientas para el seguimiento del rendimiento
Vistas de administración dinámica
Por tanto, comencemos con las vistas de administración de las dinámicas AKA DMVs. La mayoría de ustedes sabe lo que son los DMVs, pero para aquellos que no lo saben, las DMV son vistas y funciones que devuelven información de estado del servidor que se puede utilizar para monitorear el mantenimiento y estado de salud del servidor, diagnosticar problemas, etc.
Las tres DMV de uso común en SQL Server para el rendimiento de la memoria son:
Vamos a empezar como SSMS y ejecutar algunas consultas, ver lo que esas DMV devolverán. Utilice el código de abajo y ejecútelo:
|
1 2 3 4 5 |
SELECT dosi.physical_memory_kb, dosi.virtual_memory_kb, dosi.committed_kb, dosi.committed_target_kb FROM sys.dm_os_sys_info dosi; |
Esto hará retornar un conjunto de información útil sobre la máquina:
- physical_memory_kb – cantidad total de memoria física en la máquina
- virtual_memory_kb – cantidad total de espacio de direcciones virtuales disponibles para el proceso en modo de usuario
- committed_kb – memoria comprometida en kilobytes (KB), en el administrador de memoria
- committed_target_kb – cantidad de memoria, en kilobytes (KB), que puede consumir el administrador de memoria del SQL Server
Para ver la información actual de la memoria del sistema, utilice la siguiente consulta:
|
1 2 3 4 |
SELECT dosm.total_physical_memory_kb, dosm.available_physical_memory_kb, dosm.system_memory_state_desc FROM sys.dm_os_sys_memory dosm; |
Esta vez, en realidad tenemos un mensaje amigable de usuario, el cual nos dice que la memoria física disponible es alta y eso es bueno:
- total_physical_memory_kb – cantidad total de memoria física disponible para el sistema operativo
- available_physical_memory_kb – cantidad total de memoria física disponible
- system_memory_state_desc – explicación del estado de la memoria (la memoria física disponible es alta o el uso de la memoria física es constante, el estado de la memoria física está en transición)
El último muestra la memoria de proceso de SQL Server actual:
|
1 2 3 4 |
SELECT dopm.physical_memory_in_use_kb, dopm.process_physical_memory_low, dopm.process_virtual_memory_low FROM sys.dm_os_process_memory dopm; |
Esto hará retornar unas señales para hacernos saber si la memoria de proceso física y virtual para SQL Server es baja:
- physical_memory_in_use_kb – indica el proceso de trabajo establecido en KB
- process_physical_memory_low – indica que el proceso responde a una notificación de memoria física baja
- process_virtual_memory_low – indica que se ha detectado una condición de memoria virtual baja
Si ambas señales son cero, estamos en buen estado.
Recopilador de datos
Vamos avanzando, echemos un vistazo a algunos contadores para la supervisión del rendimiento y las herramientas de supervisión de SQL Server que se pueden usar para realizar un seguimiento de ellos:
Memoria disponible en megabytes – Este es un excelente contador, especialmente si buscamos durante mucho tiempo porque podemos averiguar qué rangos hay para la memoria. El valor predeterminado del rango de memoria es 100 MB.
SQLServer: buffer Manager/buffer cache ratio – Este representa un porcentaje de la frecuencia con la que SQL Server puede encontrar páginas de datos en la memoria en lugar de recuperarlas del disco. Este número debe ser = = > 98%. Yo diría que si esa proporción es menor que 95% entonces el servidor está bajo presión de memoria. Siempre queremos que esto sea extremadamente alto.
SQLServer:Buffer Manager/Page life expectancy – SQLServer: buffer Manager/ Page life expectancy – este es con mucha probabilidad el contador de rendimiento más popular cuando se refiere a memoria en SQL Server. Representa el número de segundos que una página se alojará en el grupo de búferes sin las referencias. Si tuviera que elegir un contador, esta métrica es la que todas las herramientas de supervisión de SQL Server deberían poder realizar y hacer un seguimiento. Por lo tanto, esencialmente, deseamos que la página permanezca en el búfer durante el mayor tiempo posible, ya que llevará a un alto rendimiento. Esto es simplemente debido a que, si una solicitud ingresa en esa página, se puede leer desde la memoria caché en lugar de buscar en el disco duro y, por último, reducir el I/O. En cuanto al valor que se espera, esto es aún más complicado. Usted probablemente leerá en línea que debe ser más de 300. Esta es una antigua regla, cuando la memoria de SQL Server podría ser de 4 GB como máximo. En estos días, se puede tener mucho más RAM que esa cantidad y esta métrica es básicamente dependiente de la memoria. Por tanto, una de las fórmulas de memoria que mejor se conocen que se pueden encontrar para calcular su valor PLE en línea es la cantidad de memoria asignada a SQL Server, dividir por 4 y multiplicar ese número por 300. Por ejemplo, si 16 GB se asignan a SQL Server debe ser como este 300 x (16/4) = 1200. Por tanto, este número es la base y si nuestro valor de PLE es menor que este número que sería una buena indicación de que SQL Server no tiene suficiente memoria para mantener las páginas en el búfer el tiempo suficiente. Por cierto, esto llegará a afectar de manera directa a la relación de aciertos del caché del búfer. Por eso, esas dos son las herramientas de supervisión de SQL Server más importantes y los contadores que se usan habitualmente para detectar sobrecargas de memoria.
Para `poder obtener más información acerca de la supervisión de dos tipos de medidores anteriores, vea el artículo de las métricas de rendimiento de memoria de SQL Server – Parte 4 – relación de aciertos del caché del búfer y la expectativa de vida de la página..
SQLServer: buffer Manager/Lazy writes/seg – este número muestra cuántas páginas se vacían de la memoria fuera del proceso del punto de comprobación o recuperación cuando hay sobrecarga de memoria. Este valor siempre debe ser menor que 20, si es mayor, entonces probablemente debería considerar la asignación de más memoria al SQL Server.
Para obtener mayor información acerca de la supervisión de este tipo de contador, vea el artículo de las métricas de rendimiento de memoria de SQL Server – Parte 5 – comprensión de las escrituras lentas, puestos de lista libre/sec y concesiones de memoria pendientes.
Monitor de rendimiento
A continuación, veamos cómo se puede supervisar algunos de los contadores mediante el monitor de rendimiento de la herramienta de supervisión de SQL Server del sistema nativo. Si no está familiarizado con esta herramienta, dele un vistazo a la primera parte de la serie para obtener información más detallada sobre cómo ejecutarla y lo que es más importante, cómo crear un conjunto de datos personalizados para supervisar contadores específicos. Yo ya he creado uno llamado “memoria” y de la lista de contadores disponibles, he añadido lo siguiente:
Ahora, si solo se iniciara este conjunto de recopilación de datos, nada raro habría sucedido en mi máquina local. En reposo, el resultado hubiera resultado perfecto. Por eso, vamos a crear un poco de carga mediante la ejecución de la herramienta de supervisión de SQL Server enviado dentro de SQL Server llamado SQLIOSim. De nuevo, voy a utilizar la misma prueba de estrés en la que ya he preconfigurado todo. De ser necesario, vaya a la parte uno para obtener más información sobre cómo hacerlo.
Básicamente, acabo de ejecutar la prueba de esfuerzo, a continuación, también comenzó el recopilador de datos establecido en el monitor de rendimiento y dejo que ambos se ejecuten durante unos 10 minutos. Tómese la libertad de dejar que la prueba de rendimiento termine su trabajo, claro que esto puede llevar mucho tiempo según la configuración del equipo:
Una vez realizado esto, haga clic en el informe de memoria en la carpeta de informes definidos por el usuario y cambie al tipo de gráfico de informe:
En mi equipo, se tienen 16 GB de RAM. Se puede ejecutar la consulta a continuación para ver cuánto se dedica a SQL Server:
|
1 2 3 4 |
SELECT c.name, c.value_in_use FROM sys.configurations c WHERE c.name LIKE '%server memory%'; |
Resultados de mi equipo son:
Si el número de cantidades máximas de memoria que usará SQL Server es 2147483647 que indica que SQL servers usará tanta memoria como se pueda, suponiendo que así lo necesite. Éste es a su vez el valor predeterminado para la memoria máxima del servidor (MB). Así que, de vuelta al informe, tenemos casi 5 campos de RAM disponible. Más importante aún, bajo los contadores del administrador de búfer, se tiene la relación de aciertos de caché del buffer en casi 100 por ciento. Ahora, esto es tan bueno como se ve, recuerda que si queremos tener un valor alto aquí. Finalmente, pero no menos importante, el valor de expectativa de vida de la página está muy por encima de la base para este sistema que es realmente bueno. Esto implica que las páginas no se vacían de la memoria debido a que la memoria no se está volviendo a sobrecargar.
Estos contadores son en definitiva lo que se necesita en una herramienta de supervisión de SQL Server y lo que desea ver al tratar de solucionar problemas de memoria porque nos dan una gran gama de información sobre cómo SQL Server está usando la 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