SQL Server e Azure SQL Database - Como retornar o uso de CPU e Memória utilizando T-SQL

Uma necessidade bem comum no dia a dia de um administrador de banco de dados é analisar o uso de CPU da instância. Por este motivo, compartilharei nesse artigo, uma consulta Transact-SQL (T-SQL) para obter essa informação de um banco SQL Server 2008+ ou Azure SQL Database.

Embora seja possível obter o uso de CPU utilizando ferramentas gráficas, muitas vezes é interessante conseguir ter acesso a essa informação utilizando uma query, pois você pode armazenar esse histórico numa tabela, gerar alertas e ações personalizadas conforme o valor obtido, criar gráficos no Power BI, etc…

Como retornar o uso de CPU utilizando T-SQL

Para retornar o uso de CPU do servidor utilizando T-SQL, utilize o script abaixo:

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 );

Exemplo de retorno:

Como retornar o uso de Memória utilizando T-SQL

Para retornar o uso de Memória do servidor utilizando T-SQL, utilize o script abaixo. Com ele, é possível identificar se está havendo pressão de memória e se ela está sendo causada pelo SO ou pelo próprio SQL Server.

O primeiro ponto que deve ser observado, é a coluna “RmNotification”, que nos indica o tipo de notificação gerada:

• RESOURCE_MEMPHYSICAL_HIGH – SQL tem espaço para alocar mais memória
• RESOURCE_MEMPHYSICAL_LOW – Memória física interna ou de sistema baixa
• RESOURCE_MEM_STEADY
• RESOURCE_MEMVIRTUAL_LOW – Range de endereços virtuais para o SQL Server estão se esgotando

Se a coluna “RmNotification” apresentar uma mensagem de “RESOURCE_MEMPHYSICAL_LOW”, quer dizer que a memória estava baixa e isso gerou esse alerta na Ring Buffer.

Para entender o que estava baixo, vamos observar as colunas “IndicatorsProcess” e “IndicatorsSystem”.

Se a coluna “IndicatorsProcess” retornar 0 e a coluna “IndicatorsSystem” retornar um valor diferente de 0, quer dizer que o problema de pressão de memória afetou todo o sistema. Mas se o contrário acontecer, quer dizer que somente um processo que sofreu pressão de memória e não o sistema todo (mas não fala qual processo foi).

Possívels valores para as colunas “IndicatorsProcess” e “IndicatorsSystem”:

• IDX_MEMPHYSICAL_HIGH (High Physical Memory) = 1
• IDX_MEMPHYSICAL_LOW (Low Physical Memory) = 2
• IDX_MEMVIRTUALL_LOW (Low Virtual Memory) = 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

Exemplo de resultado:

Se precisar de uma query mais simples, pode também utilizar a DMV “dm_os_process_memory”:

SELECT * 
FROM sys.dm_os_process_memory

Resultado:

Eu já havia compartilhado esses scripts acima no artigo SQL Server – Consultas úteis do dia a dia do DBA que você sempre tem que ficar procurando na Internet, mas a maioria das pessoas acabava não encontrando quando precisava e por isso resolvi criar esse artigo.

Espero que vocês tenham gostado desse post.
Um grande abraço e até mais!