Seja você responsável por gerenciar um conjunto de instâncias do SQL Server ou irá dar suporte a elas no futuro, é importante ter visibilidade sobre o consumo de recursos de cada servidor. Neste artigo, discutiremos como obter rapidamente informações sobre o uso de CPU e memória para cada uma de suas instâncias do SQL Server.

Coletando Informações de CPU e Memória

Para coletar informações de CPU e memória, usaremos uma consulta que pode ser executada diretamente no SQL Server Management Studio. Aqui está a consulta:

WITH SQLProcessCPU
AS(
   SELECT TOP(30) SQLProcessUtilization AS 'Uso_CPU', ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS 'numero_linha'
   FROM ( 
         SELECT 
           record.value('(./Record/@id)[1]', 'int') AS id_registro,
           record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [OciosidadeSistema],
           record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [UsoProcessoSQL], 
           [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 '%<SystemHealth>%'
              ) AS x 
        ) AS y
) 

SELECT 
   SERVERPROPERTY('SERVERNAME') AS 'Instância',
   (SELECT value_in_use FROM sys.configurations WHERE name like '%max server memory%') AS 'Memória_Máxima_Servidor',
   (SELECT physical_memory_in_use_kb/1024 FROM sys.dm_os_process_memory) AS 'Uso_Memória_SQL_Server (MB)',
   (SELECT total_physical_memory_kb/1024 FROM sys.dm_os_sys_memory) AS 'Memória_Física (MB)',
   (SELECT available_physical_memory_kb/1024 FROM sys.dm_os_sys_memory) AS 'Memória_Disponível (MB)',
   (SELECT system_memory_state_desc FROM sys.dm_os_sys_memory) AS 'Estado_Memória_Sistema',
   (SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Manager%' AND [counter_name] = 'Page life expectancy') AS 'Expectativa_Vida_Página',
   (SELECT AVG(Uso_CPU) FROM SQLProcessCPU WHERE numero_linha BETWEEN 1 AND 30) AS 'UsoProcessoSQL30',
   (SELECT AVG(Uso_CPU) FROM SQLProcessCPU WHERE numero_linha BETWEEN 1 AND 15) AS 'UsoProcessoSQL15',
   (SELECT AVG(Uso_CPU) FROM SQLProcessCPU WHERE numero_linha BETWEEN 1 AND 10) AS 'UsoProcessoSQL10',
   (SELECT AVG(Uso_CPU) FROM SQLProcessCPU WHERE numero_linha BETWEEN 1 AND 5)  AS 'UsoProcessoSQL5',
   GETDATE() AS 'Timestamp_Amostra_Dados'

Esta consulta recupera informações como o nome da instância do SQL Server, configuração de memória máxima do servidor, uso de memória do SQL Server, memória física, memória disponível, estado da memória do sistema, expectativa de vida da página e utilização média da CPU em diferentes intervalos de tempo.

Usando o PowerShell para Coletar Informações de Múltiplas Instâncias

Se você tiver várias instâncias do SQL Server para monitorar, pode usar o PowerShell para automatizar o processo. Aqui está um exemplo de script PowerShell:

$servidor = "XXX"
$banco_dados_inventario = "XXX"

# Cria a tabela para armazenar as informações de uso de recursos
$tabela_uso_recursos = "
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'CPU_Memory_Usage' AND xtype = 'U')
CREATE TABLE CPU_Memory_Usage(
   [servidor] [varchar](128) NOT NULL,
   [memoria_servidor_maxima] [int] NOT NULL,
   [uso_memoria_sql] [int] NOT NULL,
   [memoria_fisica] [int] NOT NULL,
   [memoria_disponivel] [int] NOT NULL,
   [estado_memoria_sistema] [varchar](255) NOT NULL,
   [expectativa_vida_pagina] [int] NOT NULL,
   [uso_cpu_30] [int] NOT NULL,
   [uso_cpu_15] [int] NOT NULL,
   [uso_cpu_10] [int] NOT NULL,
   [uso_cpu_5] [int] NOT NULL,
   [timestamp_amostra_dados] [datetime] NULL
) ON [PRIMARY]
"

# Cria a tabela de instâncias ou usa uma existente
$instancias = Invoke-Sqlcmd -ServerInstance $servidor -Database $banco_dados_inventario -Query $tabela_uso_recursos

# Obtém a lista de instâncias para monitorar
$consulta_instancias = "SELECT nome, versao, instancia FROM instancias" 
$instancias = Invoke-Sqlcmd -ServerInstance $servidor -Database $banco_dados_inventario -Query $consulta_instancias

# Consulta informações de CPU e memória para cada instância
$consulta_recursos = "
WITH SQLProcessCPU
AS(
   SELECT TOP(30) SQLProcessUtilization AS 'Uso_CPU', ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS 'numero_linha'
   FROM ( 
         SELECT 
           record.value('(./Record/@id)[1]', 'int') AS id_registro,
           record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [OciosidadeSistema],
           record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [UsoProcessoSQL], 
           [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 '%<SystemHealth>%'
              ) AS x 
        ) AS y
) 

SELECT 
   SERVERPROPERTY('SERVERNAME') AS 'Instância',
   (SELECT value_in_use FROM sys.configurations WHERE name like '%max server memory%') AS 'Memória_Máxima_Servidor',
   (SELECT physical_memory_in_use_kb/1024 FROM sys.dm_os_process_memory) AS 'Uso_Memória_SQL_Server (MB)',
   (SELECT total_physical_memory_kb/1024 FROM sys.dm_os_sys_memory) AS 'Memória_Física (MB)',
   (SELECT available_physical_memory_kb/1024 FROM sys.dm_os_sys_memory) AS 'Memória_Disponível (MB)',
   (SELECT system_memory_state_desc FROM sys.dm_os_sys_memory) AS 'Estado_Memória_Sistema',
   (SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Manager%' AND [counter_name] = 'Page life expectancy') AS 'Expectativa_Vida_Página',
   (SELECT AVG(Uso_CPU) FROM SQLProcessCPU WHERE numero_linha BETWEEN 1 AND 30) AS 'UsoProcessoSQL30',
   (SELECT AVG(Uso_CPU) FROM SQLProcessCPU WHERE numero_linha BETWEEN 1 AND 15) AS 'UsoProcessoSQL15',
   (SELECT AVG(Uso_CPU) FROM SQLProcessCPU WHERE numero_linha BETWEEN 1 AND 10) AS 'UsoProcessoSQL10',
   (SELECT AVG(Uso_CPU) FROM SQLProcessCPU WHERE numero_linha BETWEEN 1 AND 5)  AS 'UsoProcessoSQL5',
   GETDATE() AS 'Timestamp_Amostra_Dados'
"

# Obtém informações de CPU e memória para cada instância
foreach ($instancia in $instancias){
   Write-Host "Obtendo informações de CPU/memória para a instância" $instancia.instancia
   $resultados = Invoke-Sqlcmd -Query $consulta_recursos -ServerInstance $instancia.nome -ErrorAction Stop -querytimeout 30
   
   # Constrói a instrução INSERT
   if($resultados.Length -ne 0){    
      $inserir = "INSERT INTO CPU_Memory_Usage VALUES"
      foreach($resultado in $resultados){        
         $inserir += "
         (
         '"+$resultado['Instância']+"',
         "+$resultado['Memória_Máxima_Servidor']+",
         "+$resultado['Uso_Memória_SQL_Server (MB)']+",
         "+$resultado['Memória_Física (MB)']+",
         "+$resultado['Memória_Disponível (MB)']+",
            '"+$resultado['Estado_Memória_Sistema']+"',
            "+$resultado['Expectativa_Vida_Página']+",
            "+$resultado['UsoProcessoSQL30']+",
            "+$resultado['UsoProcessoSQL15']+",
            "+$resultado['UsoProcessoSQL10']+",
            "+$resultado['UsoProcessoSQL5']+",
            GETDATE()
            ),
       "
       }

   # Executa o INSERT na tabela central
   Invoke-Sqlcmd -Query $inserir.Substring(0,$inserir.LastIndexOf(',')) -ServerInstance $servidor -Database $banco_dados_inventario
   }
}

Write-Host "Concluído!"

Este script PowerShell se conecta à instância do SQL Server especificada pela variável $servidor e recupera a lista de instâncias para monitorar do banco de dados $banco_dados_inventario. Em seguida, consulta as informações de CPU e memória para cada instância e insere os resultados na tabela CPU_Memory_Usage.

Interpretando os Resultados

Após executar o script PowerShell, você terá uma tabela chamada CPU_Memory_Usage que contém as seguintes informações para cada instância:

• Instância: O nome da instância do SQL Server.
• Memória Máxima do Servidor: A quantidade máxima de memória configurada para a instância.
• Uso de Memória do SQL Server (MB): A quantidade de memória usada pelo processo do SQL Server.
• Memória Física (MB): A memória física total disponível para o sistema operacional.
• Memória Disponível (MB): A quantidade de memória disponível para uso no servidor.
• Estado da Memória do Sistema: Uma breve descrição do estado da memória em termos de uso e disponibilidade.
• Expectativa de Vida da Página: A expectativa de vida da página no momento em que os dados foram amostrados.
• UsoProcessoSQL30, UsoProcessoSQL15, UsoProcessoSQL10 e UsoProcessoSQL5: A utilização média da CPU em diferentes intervalos de tempo.
• Timestamp_Amostra_Dados: O timestamp quando a amostra foi capturada.

Observe que a configuração de Memória Máxima do Servidor limita apenas a alocação do buffer pool, e memória adicional pode ser usada para outros fins, como objetos COM, procedimentos armazenados estendidos, SQLCLR e memória alocada por servidores vinculados.

Ao usar essa abordagem, você pode monitorar facilmente o uso de CPU e memória de suas instâncias do SQL Server sem depender de ferramentas de terceiros ou scripts personalizados. Isso fornece um ponto de partida para monitorar esses valores básicos em seu ambiente.