SQL Server y Azure SQL Database - Cómo Obtener el Uso de CPU y Memoria Usando T-SQL

Una necesidad muy común en la vida diaria de un administrador de bases de datos es analizar el uso de CPU de la instancia. Por esta razón, en este artículo compartiré una consulta Transact-SQL (T-SQL) para obtener esta información desde un SQL Server 2008+ o Azure SQL Database.

Aunque es posible obtener el uso de CPU mediante herramientas gráficas, muchas veces resulta interesante poder acceder a esta información mediante una consulta, ya que se puede almacenar este historial en una tabla, generar alertas y acciones personalizadas según el valor obtenido, crear gráficas en Power BI, etc…

Cómo devolver el uso de la CPU usando T-SQL

Para devolver el uso de CPU del servidor usando T-SQL, use el siguiente script:

DECLARE @ts_now BIGINT = (
    SELECT
        cpu_ticks / ( cpu_ticks / ms_ticks )
    FROM
        sys.dm_os_sys_info
);

SELECT
    SQLProcessUtilization AS [SQL Server Process CPU Utilization],
    SystemIdle AS [System Idle Process],
    100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
    DATEADD(ms, -1 * ( @ts_now - [timestamp] ), GETDATE()) AS [Event Time]
FROM
(
    SELECT
        record.value('(./Record/@id)[1]', 'int') AS record_id,
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle],
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization],
        [timestamp]
    FROM
    (
        SELECT
            [timestamp],
            CONVERT(XML, record) AS [record]
        FROM
            sys.dm_os_ring_buffers
        WHERE
            ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            AND record LIKE N'%<SystemHealth>%'
    ) AS x
) AS y
ORDER BY
    record_id DESC
OPTION ( RECOMPILE );

Ejemplo de devolución:

Cómo devolver el uso de memoria usando T-SQL

Para devolver el uso de la memoria del servidor usando T-SQL, use el siguiente script. Con él, es posible identificar si existe presión en la memoria y si es causada por el sistema operativo o por el propio SQL Server.

El primer punto que cabe destacar es la columna “RmNotification”, que nos indica el tipo de notificación generada:

• RESOURCE_MEMPHYSICAL_HIGH: SQL tiene espacio para asignar más memoria
• RESOURCE_MEMPHYSICAL_LOW: memoria física interna o del sistema baja
• RESURCE_MEM_STEADY
• RESOURCE_MEMVIRTUAL_LOW: el rango de direcciones virtuales para SQL Server se está agotando

Si la columna "RmNotification" muestra un mensaje "RESOURCE_MEMPHYSICAL_LOW", significa que la memoria era baja y esto generó esta alerta en Ring Buffer.

Para comprender qué era bajo, veamos las columnas "Proceso de indicadores" y "Sistema de indicadores".

Si la columna “IndicatorsProcess” devuelve 0 y la columna “IndicatorsSystem” devuelve un valor distinto de 0, significa que el problema de presión de memoria ha afectado a todo el sistema. Pero si ocurre lo contrario, significa que solo un proceso sufrió presión de memoria y no todo el sistema (pero no dice qué proceso fue).

Posibles valores para las columnas “IndicatorsProcess” e “IndicatorsSystem”:

• IDX_MEMPHYSICAL_HIGH (memoria física alta) = 1
• IDX_MEMPHYSICAL_LOW (memoria física baja) = 2
• IDX_MEMVIRTUALL_LOW (Memoria virtual baja) = 4

-- https://blogs.msdn.microsoft.com/mvpawardprogram/2012/06/04/using-sys-dm_os_ring_buffers-to-diagnose-memory-issues-in-sql-server/
WITH RingBuffer AS ( 
    SELECT
        CAST(dorb.record AS XML) AS xRecord,
        dorb.[timestamp]
    FROM
        sys.dm_os_ring_buffers AS dorb
    WHERE
        dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' 
)
SELECT
    xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)') AS RmNotification,
    DATEADD (ss, (-1 * ((B.cpu_ticks / CONVERT (FLOAT, (B.cpu_ticks/ B.ms_ticks))) - A.[timestamp]) / 1000), GETDATE()) AS RmDateTime,
    xr.value('(ResourceMonitor/IndicatorsProcess)[1]', 'tinyint') AS IndicatorsProcess,
    xr.value('(ResourceMonitor/IndicatorsSystem)[1]', 'tinyint') AS IndicatorsSystem,
    xr.value('(MemoryNode/TargetMemory)[1]', 'bigint') AS TargetMemory,
    xr.value('(MemoryNode/ReserveMemory)[1]', 'bigint') AS ReserveMemory,
    xr.value('(MemoryNode/CommittedMemory)[1]', 'bigint') AS CommitedMemory,
    xr.value('(MemoryNode/SharedMemory)[1]', 'bigint') AS SharedMemory,
    xr.value('(MemoryNode/PagesMemory)[1]', 'bigint') AS PagesMemory,
    xr.value('(MemoryRecord/MemoryUtilization)[1]', 'bigint') AS MemoryUtilization,
    xr.value('(MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS TotalPhysicalMemory,
    xr.value('(MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS AvailablePhysicalMemory,
    xr.value('(MemoryRecord/TotalPageFile)[1]', 'bigint') AS TotalPageFile,
    xr.value('(MemoryRecord/AvailablePageFile)[1]', 'bigint') AS AvailablePageFile,
    xr.value('(MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS TotalVirtualAddressSpace,
    xr.value('(MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS AvailableVirtualAddressSpace,
    xr.value('(MemoryRecord/AvailableExtendedVirtualAddressSpace)[1]', 'bigint') AS AvailableExtendedVirtualAddressSpace
FROM
    RingBuffer AS A
    CROSS APPLY A.xRecord.nodes('Record') record(xr)
    CROSS JOIN sys.dm_os_sys_info AS B

Resultado de ejemplo:

Si necesita una consulta más sencilla, también puede utilizar el DMV “dm_os_process_memory”:

SELECT * 
FROM sys.dm_os_process_memory

Resultado:

Ya había compartido estos scripts arriba en el artículo. SQL Server: consultas de DBA útiles para el día a día que siempre debe buscar en Internet, pero la mayoría de las personas terminaron no encontrándolo cuando lo necesitaban y por eso decidí crear este artículo.

Espero que te haya gustado esta publicación.
¡Un fuerte abrazo y hasta luego!