Bojan Petrovic
Baseline adjustment options with thresholds configuration for Available megabytes of memory counter

Las herramientas de monitorización de SQL Server para el rendimiento de la memoria

May 12, 2019 by

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:

Esto hará retornar un conjunto de información útil sobre la máquina:

The result of the query will return a set of useful information about the machine

  • 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:

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:

The result of the query will return current system memory information

  • 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:

Esto hará retornar unas señales para hacernos saber si la memoria de proceso física y virtual para SQL Server es baja:

The result of the query will be a complete picture of the process memory space

  • 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:

SQL Server monitoring tool and its configuration of performance counters

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:

SQL Server monitoring tool for simulating server activity on a disk subsystem

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:

Performance Monitor report showing data collection set captured for memory performance

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:

Resultados de mi equipo son:

The result of the query will be a running total of dedicated memory to SQL Server

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.

Alternativas

Démosle una mirada a otra herramienta de supervisión de SQL Server llamada ApexSQL Monitor y observe cómo se puede usar para poder hacer un seguimiento de la memoria disponible de SQL Server. Una de las principales características más útiles de esta herramienta son los informes, ya que nos ayudan con el análisis de los datos de rendimiento históricos de SQL Server. La aplicación tiene informes por defecto divididos en diferentes categorías, pero con el propósito de ilustrar en este artículo, podemos crear un nuevo informe personalizado usando unos sencillos pasos.

Vaya a la página informes y haga clic en agregar opción personalizada para crear un nuevo informe personalizado por el usuario:

Steps for creating a custom report

Asigne un nombre descriptivo, seleccione memoria (memory) en la lista de categorías y, a continuación, junto a este, elija el medidor de megabytes y memoria disponibles (Memory available megabytes) como se muestra en la figura mostrada a continuación. Pulse guardar para volver a la página informes:

Custom report options for generating available  megabytes of memory report

Ahora, que hemos creado un informe personalizado, asegúrese de que está seleccionado en la lista de todos los informes. Escoja para cuál de las instancias de SQL Server desea que se realice el informe y el intervalo de tiempo. Acabo de escoger todas las instancias en mi equipo y elegí 14 días como se ve en la siguiente figura:

Options for generating memory chart availability of the SQL Server for the past 14 days

Seguidamente, haga clic en generar y espere a que la aplicación cree los informes escogidos. Esto es lo que aparece:

Generated chart displaying available memory of SQL Server for the past 14 days

Desde acá, se puede, por ejemplo, enviar esto a una dirección de correo electrónico haciendo clic en la opción de enviar a correo electrónico (send to email):

SQL Server monitoring tool with an option to email the report to anyone

También, se puede guardar este informe como un formato de documento portable haciendo clic en la opción Guardar como PDF:

SQL Server monitoring tool with an option to save the report as pdf file

Vamos a continuar y veremos cómo generar un informe para todos los demás tipos mediante esta supervisión del rendimiento y la herramienta de supervisión de SQL Server. Esto se puede hacer a través de un informe predefinido en la categoría rendimiento. Este tipo de informes se pueden generar utilizando cualquier métrica de rendimiento como un gráfico para el período definido por el usuario. Complete el formulario como en el ejemplo anterior solo que esta vez agregue los siguientes contadores:

  • Esperanza de vida de página
  • Relación de aciertos caché buffer
  • Las escrituras perezosas por segundo

User-configurable options for SQL Server metrics report

Haga clic en generar y espere a que la aplicación genere los informes seleccionados:

SQL Server monitoring tool generated chart with the historical presentation of memory performance counters

Los gráficos se pueden exportar a través del icono del menú contextual del gráfico en el icono superior derecho, como una imagen PNG, JPEG y SVG, o un documento PDF, o se pueden imprimir:

Chart context menu for exporting options

Esto es lo que aparece:

Chart with the historic presentation of memory performance counters

También, me gustaría mostrarle cómo poder calcular las líneas de base para estos contadores. No todas las herramientas de supervisión de SQL Server tienen esta característica, pero se puede lograr con pocos pasos mediante ApexSQL Monitor:

  1. Vaya a la página de configuración (configuration)
  2. Seleccione la instancia de SQL Server de la lista de los que están disponibles (en mi caso Desktop-9JU7UEFSQLEXPRESS)
  3. Cambie la pestaña a líneas de base
  4. Seleccione el rango de tiempo usando los selectores de fecha, hora de inicio y fin (en mi caso dos semanas)

SQL Server monitoring tool with configuration options for calculating baselines

A continuación, se seleccionan los contadores/métricas para los cuales queremos calcular las líneas de base. Esto se realiza desplazándose hacia abajo en la página. Encontrará megabytes disponibles de memoria en categoría rendimiento del sistema y el resto se encuentra en SQL Server. Una vez seleccionados, pulse calcular en la parte superior de la página y espere unos segundos para la finalización de la operación:

SQL Server monitoring tool with generated metrics for memory performance

Una vez hecho el cálculo exitoso, las métricas previamente seleccionadas se convertirán en URLs donde se puede hacer clic y ser redireccionados a una página dedicada para cada página de ajuste de línea de base:

Baseline adjustment options with thresholds configuration for Available megabytes of memory counter

Aquí es posible ajustar los umbrales y modificar la configuración de las métricas. Tome en cuenta que todas estas métricas están siendo monitoreadas por el monitor ApexSQL con umbrales predefinidos de forma predeterminada:

Baseline adjustment options with thresholds configuration for Lazy writes per second counter

También, estas métricas se establecen, de forma predeterminada, para notificarle sobre los problemas de rendimiento potenciales y continuos con sus equipos. Las alertas también son configurables a un nivel granular para cada máquina monitoreada.

Continuaremos la historia de herramientas de supervisión de SQL Server en otro artículo en el cual hablaremos sobre la sobrecarga de la CPU que ralentiza al servidor de la base de datos.

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

719 Views