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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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:
O script acima utiliza a DMV sys.dm_os_ring_buffers e o Extended Event “SystemHealth”, habilitado por padrão. Você não precisa ativar nada para utilizar esse script e ele retorna sempre o uso de CPU dos últimos 256 minutos (um pouco mais de 4h). Caso a instância seja reiniciada, esses dados serão perdidos.
Esse script foi testado no Azure SQL Database e SQL Server 2008 e 2019.
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
-- 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”:
|
1 2 |
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!
Portuguese 
English