Solucionar problemas de uso alto de CPU no SQL Server

Aplica-se ao: SQL Server

Este artigo fornece procedimentos para diagnosticar e corrigir problemas causados pelo alto uso da CPU em um computador que está executando o Microsoft SQL Server. Embora haja muitas causas possíveis de alto uso da CPU que ocorrem no SQL Server, as causas seguintes são as mais comuns:

• Leituras lógicas altas causadas por verificações de tabela ou índice devido às seguintes condições:
  • Estatísticas desatualizadas
  • Índices ausentes
  • Problemas de PSP (plano com distinção de parâmetro)
  • Consultas mal projetadas
• Aumento na carga de trabalho

Você pode usar as etapas a seguir para solucionar problemas de alto uso da CPU no SQL Server.

Use uma das seguintes ferramentas para verificar se o processo do SQL Server está realmente contribuindo para o alto uso da CPU:

• Gerenciador de Tarefas: na guia Processo, verifique se o valor da coluna CPU para SQL Server Windows NT-64 Bits está próximo de 100%.

• Monitor de Desempenho e Recursos (perfmon)

  • Contador: Process/%User Time, % Privileged Time
  • Instância: sqlservr

• Você pode usar o seguinte script do PowerShell para coletar os dados do contador em um intervalo de 60 segundos:

  PowerShell Copiar

  $serverName = $env:COMPUTERNAME
  $Counters = @(
      ("\\$serverName" + "\Process(sqlservr*)\% User Time"), ("\\$serverName" + "\Process(sqlservr*)\% Privileged Time")
  )
  Get-Counter -Counter $Counters -MaxSamples 30 | ForEach {
      $_.CounterSamples | ForEach {
          [pscustomobject]@{
              TimeStamp = $_.TimeStamp
              Path = $_.Path
              Value = ([Math]::Round($_.CookedValue, 3))
          }
          Start-Sleep -s 2
      }
  }
  

Se % User Time for consistentemente maior que 90% (% tempo de usuário é a soma do tempo do processador em cada processador, seu valor máximo é 100% * (não de CPUs)), o processo SQL Server está causando alto uso da CPU. No entanto, se % Privileged time for consistentemente maior que 90%, seu software antivírus, outros drivers ou outro componente do sistema operacional no computador estará contribuindo para o alto uso da CPU. Você deve trabalhar com o administrador do sistema para analisar a causa raiz desse comportamento.

Se o processo Sqlservr.exe estiver causando alto uso da CPU, o motivo mais comum é que as consultas do SQL Server executam verificações de tabela ou índice, seguidas por classificação, operações de hash e loops (operador de loop aninhado ou WHILE (T-SQL)). Para obter uma visão da quantidade de CPU que as consultas estão usando no momento, fora da capacidade geral da CPU, execute a seguinte instrução:

DECLARE @init_sum_cpu_time int,
        @utilizedCpuCount int 
--get CPU count used by SQL Server
SELECT @utilizedCpuCount = COUNT( * )
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE' 
--calculate the CPU usage by queries OVER a 5 sec interval 
SELECT @init_sum_cpu_time = SUM(cpu_time) FROM sys.dm_exec_requests
WAITFOR DELAY '00:00:05'
SELECT CONVERT(DECIMAL(5,2), ((SUM(cpu_time) - @init_sum_cpu_time) / (@utilizedCpuCount * 5000.00)) * 100) AS [CPU from Queries as Percent of Total CPU Capacity] 
FROM sys.dm_exec_requests

Para identificar as consultas responsáveis pela atividade alta da CPU no momento, execute a seguinte instrução:

SELECT TOP 10 s.session_id,
           r.status,
           r.cpu_time,
           r.logical_reads,
           r.reads,
           r.writes,
           r.total_elapsed_time / (1000 * 60) 'Elaps M',
           SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
           ((CASE r.statement_end_offset
                WHEN -1 THEN DATALENGTH(st.TEXT)
                ELSE r.statement_end_offset
            END - r.statement_start_offset) / 2) + 1) AS statement_text,
           COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) 
           + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
           r.command,
           s.login_name,
           s.host_name,
           s.program_name,
           s.last_request_end_time,
           s.login_time,
           r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC

Se as consultas não estiverem usando a CPU no momento, você poderá executar a seguinte instrução para procurar consultas históricas associadas à CPU:

SELECT TOP 10  qs.last_execution_time, st.text AS batch_text,
    SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, ((CASE qs.statement_end_offset WHEN - 1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text,
    (qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms,
    (qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    (qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms,
    (qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
ORDER BY(qs.total_worker_time / qs.execution_count) DESC

Depois de identificar as consultas que têm o maior consumo de CPU, atualize as estatísticas das tabelas usadas por essas consultas. Você pode usar o procedimento armazenado do sistema sp_updatestats para atualizar as estatísticas de todas as tabelas internas e definidas pelo usuário no banco de dados atual. Por exemplo:

exec sp_updatestats

Para obter mais informações sobre sp_updatestats, consulte sp_updatestats.

Se o SQL Server ainda estiver usando a capacidade excessiva da CPU, vá para a próxima etapa.

Índices ausentes podem levar a consultas em execução mais lentas e alto uso da CPU. Você pode identificar índices ausentes e cria-los para ajudar a melhorar esse impacto no desempenho.

1. Execute a consulta a seguir para identificar consultas que causam alto uso da CPU e que contêm pelo menos um índice ausente no plano de consulta:

   SQL Copiar

   -- Captures the Total CPU time spent by a query along with the query plan and total executions
   SELECT
       qs_cpu.total_worker_time / 1000 AS total_cpu_time_ms,
       q.[text],
       p.query_plan,
       qs_cpu.execution_count,
       q.dbid,
       q.objectid,
       q.encrypted AS text_encrypted
   FROM
       (SELECT TOP 500 qs.plan_handle,
        qs.total_worker_time,
        qs.execution_count FROM sys.dm_exec_query_stats qs ORDER BY qs.total_worker_time DESC) AS qs_cpu
   CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
   CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
   WHERE p.query_plan.exist('declare namespace 
           qplan = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
           //qplan:MissingIndexes')=1
   

2. Examine os planos de execução das consultas identificadas e ajuste a consulta fazendo as alterações necessárias. A captura de tela a seguir mostra um exemplo no qual o SQL Server apontará um índice ausente para sua consulta. Clique com o botão direito do mouse na parte de índice ausente do plano de consulta e selecione Detalhes do Índice Ausente para criar o índice em outra janela no SQL Server Management Studio.

   

3. Use a consulta a seguir para verificar se há índices ausentes e aplicar quaisquer índices recomendados que tenham valores de medida de melhoria alta. Comece com as 5 ou 10 principais recomendações da saída que têm o valor de improvement_measure mais alto. Esses índices têm o efeito positivo mais significativo no desempenho. Decida se deseja aplicar esses índices e verifique se o teste de desempenho foi feito para o aplicativo. Em seguida, continue aplicando recomendações de índice ausente até obter os resultados de desempenho do aplicativo desejados. Para obter mais informações sobre este tópico, consulte Ajustar índices não clusterizados com sugestões de índice ausentes.

   SQL Copiar

   SELECT CONVERT(VARCHAR(30), GETDATE(), 126) AS runtime,
       mig.index_group_handle,
       mid.index_handle,
       CONVERT(DECIMAL(28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,
       'CREATE INDEX missing_index_' + CONVERT(VARCHAR, mig.index_group_handle) + '_' + CONVERT(VARCHAR, mid.index_handle) + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns,
           '') + CASE WHEN mid.equality_columns IS NOT NULL
   AND mid.inequality_columns IS NOT NULL THEN ','
   ELSE ''
   END + ISNULL(mid.inequality_columns,
           '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ')',
           '') AS create_index_statement,
       migs.*,
       mid.database_id,
       mid.[object_id]
   FROM sys.dm_db_missing_index_groups mig
   INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
   INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
   WHERE CONVERT (DECIMAL (28, 1),
                  migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
   ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
   

Você pode usar o comando DBCC FREEPROCCACHE para liberar o cache do plano e verificar se isso resolve o problema de alto uso da CPU. Se o problema for corrigido, ele será uma indicação de um problema que diferencia parâmetros (PSP, também conhecido como "problema de detecção de parâmetro").

Para mitigar os problemas que diferenciam parâmetros, use as seguintes etapas: Cada método tem compensações e desvantagens associadas.

• Use a dica de consulta RECOMPILE. Você pode adicionar uma dica de consulta RECOMPILE a uma ou mais das consultas de alto uso da CPU identificadas na etapa 2. Essa dica ajuda a equilibrar o pequeno aumento no uso da CPU de compilação com um desempenho mais ideal para cada execução de consulta. Para obter mais informações, consulte Parâmetros e reutilização do plano de execução, sensibilidade de parâmetro e dica de consulta RECOMPILE.

  Aqui está um exemplo de como você pode aplicar essa dica à sua consulta.

  SQL Copiar

  SELECT * FROM Person.Person 
  WHERE LastName = 'Wood'
  OPTION (RECOMPILE)
  

• Use a dica de consulta OPTIMIZE FOR para substituir o valor do parâmetro real por um valor de parâmetro mais típico que abrange a maioria dos valores nos dados. Essa opção requer uma compreensão completa dos valores de parâmetro ideais e das características de plano associadas. Aqui está um exemplo de como usar essa dica em sua consulta.

  SQL Copiar

  DECLARE @LastName Name = 'Frintu'
  SELECT FirstName, LastName FROM Person.Person 
  WHERE LastName = @LastName
  OPTION (OPTIMIZE FOR (@LastName = 'Wood'))
  

• Use a dica de consulta OPTIMIZE FOR UNKNOWN para substituir o valor do parâmetro real pela média do vetor de densidade. Você também pode fazer isso ao capturar os valores de parâmetro de entrada em variáveis locais e, em seguida, usar as variáveis locais dentro dos predicados em vez de usar os próprios parâmetros. Para essa correção, a densidade média pode ser suficiente para fornecer um desempenho aceitável.

• Use a dica de consulta DISABLE_PARAMETER_SNIFFING para desabilitar completamente a detecção de parâmetros. Aqui está um exemplo de como usá-la em uma consulta:

  SQL Copiar

  SELECT * FROM Person.Address  
  WHERE City = 'SEATTLE' AND PostalCode = 98104
  OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))
  

• Use a dica de consulta KEEPFIXED PLAN para evitar recompilações no cache. Essa solução alternativa pressupõe que o plano comum "bom o suficiente" é aquele que já está em cache. Você também pode desabilitar as atualizações automáticas de estatísticas para reduzir as chances de que o bom plano seja removido e um novo plano ruim seja compilado.

• Use o comando DBCC FREEPROCCACHE como uma solução temporária até que o código do aplicativo seja corrigido. Você pode usar o comando DBCC FREEPROCCACHE (plan_handle) para remover apenas o plano que está causando o problema. Por exemplo, para localizar planos de consulta que fazem referência à tabela Person.Person no AdventureWorks, você pode usar essa consulta para localizar o identificador de consulta. Em seguida, você pode liberar o plano de consulta específico do cache usando DBCC FREEPROCCACHE (plan_handle) que é produzido na segunda coluna dos resultados da consulta.

  SQL Copiar

  SELECT text, 'DBCC FREEPROCCACHE (0x' + CONVERT(VARCHAR (512), plan_handle, 2) + ')' AS dbcc_freeproc_command FROM sys.dm_exec_cached_plans
  CROSS APPLY sys.dm_exec_query_plan(plan_handle)
  CROSS APPLY sys.dm_exec_sql_text(plan_handle)
  WHERE text LIKE '%person.person%'
  

Um predicado em uma consulta é considerado passível de SARG (Search ARGument) quando um mecanismo SQL Server pode usar uma busca de índice para acelerar a execução da consulta. Muitos designs de consulta impedem a capacidade SARG e levam a verificações de tabela ou índice e alto uso da CPU. Considere a consulta a seguir no banco de dados AdventureWorks em que cada ProductNumber deve ser recuperado e a função SUBSTRING() aplicada a ele, antes de ser comparada a um valor literal de cadeia de caracteres. Como você pode ver, você precisa buscar todas as linhas da tabela primeiro e, em seguida, aplicar a função antes de fazer uma comparação. Buscar todas as linhas da tabela significa uma verificação de tabela ou índice, o que leva a um maior uso da CPU.

SELECT ProductID, Name, ProductNumber
FROM [Production].[Product]
WHERE SUBSTRING(ProductNumber, 0, 4) =  'HN-'

A aplicação de qualquer função ou computação nas colunas no predicado de pesquisa geralmente torna a consulta não sargável e leva a um maior consumo de CPU. As soluções normalmente envolvem reescrever as consultas de maneira criativa para torná-las passíveis de SARG. Uma solução possível para este exemplo é a reescrita em que a função é removida do predicado de consulta, outra coluna é pesquisada e os mesmos resultados são obtidos:

SELECT ProductID, Name, ProductNumber
FROM [Production].[Product]
WHERE Name LIKE  'Hex%'

Aqui está outro exemplo, em que um gerente de vendas pode querer dar 10% de comissão de vendas em pedidos grandes e deseja ver quais pedidos terão comissão maior que US$ 300. Aqui está a maneira lógica, mas não sargável de fazer isso.

SELECT DISTINCT SalesOrderID, UnitPrice, UnitPrice * 0.10 [10% Commission]
FROM [Sales].[SalesOrderDetail]
WHERE UnitPrice * 0.10 > 300

Aqui está uma possível reescrita menos intuitiva, mas passível de SARG da consulta, na qual o cálculo é movido para o outro lado do predicado.

SELECT DISTINCT SalesOrderID, UnitPrice, UnitPrice * 0.10 [10% Commission]
FROM [Sales].[SalesOrderDetail]
WHERE UnitPrice > 300/0.10

A capacidade de SARG aplica-se não apenas às cláusulas WHERE, mas também às clásulas JOINs, HAVING, GROUP BY e ORDER BY. As ocorrências frequentes de prevenção da capacidade SARG em consultas envolvem funções CONVERT(), CAST(), ISNULL() e COALESCE() usadas em clásulas WHERE ou JOIN que levam à verificação de colunas. Nos casos de conversão de tipo de dados (CONVERT ou CAST), a solução pode ser garantir que seja comparado os mesmos tipos de dados. Aqui está um exemplo em que a coluna T1.ProdID é convertida explicitamente para o tipo de dados INT em um JOIN. A conversão anula o uso de um índice na coluna de junção. O mesmo problema ocorre com a conversão implícita em que os tipos de dados são diferentes e o SQL Server converte um deles para executar a junção.

SELECT T1.ProdID, T1.ProdDesc
FROM T1 JOIN T2 
ON CONVERT(int, T1.ProdID) = T2.ProductID
WHERE t2.ProductID BETWEEN 200 AND 300

Para evitar uma verificação da tabela T1, você pode alterar o tipo de dados subjacente da coluna ProdID após o planejamento e o design adequados e, em seguida, unir as duas colunas sem usar a função de conversão ON T1.ProdID = T2.ProductID.

Outra solução é criar uma coluna computada em T1 que usa a mesma função CONVERT() e, em seguida, criar um índice nela. Isso permitirá que o otimizador de consulta use esse índice sem a necessidade de alterar a consulta.

ALTER TABLE dbo.T1  ADD IntProdID AS CONVERT (INT, ProdID);
CREATE INDEX IndProdID_int ON dbo.T1 (IntProdID);

Em alguns casos, as consultas não podem ser reescritas facilmente para permitir a capacidade SARG. Nesses casos, veja se a coluna computada com um índice pode ajudar ou mantenha a consulta como era com a percepção de que ela pode levar a cenários de uso da CPU mais altos.

Verifique se o SQL Trace ou o rastreamento XEvent afetam o desempenho do SQL Server e causa alto uso da CPU. Por exemplo, o uso dos seguintes eventos poderá causar alto uso da CPU se você rastrear uma atividade SQL Server pesada:

• Eventos XML do plano de consulta (query_plan_profile, query_post_compilation_showplan, query_post_execution_plan_profile, query_post_execution_showplan, query_pre_execution_showplan)
• Eventos no nível da instrução (sql_statement_completed, sql_statement_starting, sp_statement_starting, sp_statement_completed)
• Eventos de logon e logoff (login, process_login_finish, login_event, logout)
• Bloquear eventos (lock_acquired, lock_cancel, lock_released)
• Eventos de espera (wait_info, wait_info_external)
• Eventos de auditoria do SQL (dependendo do grupo auditado e da atividade do SQL Server nesse grupo)

Execute as seguintes consultas para identificar rastreamentos ativos do XEvent ou do Servidor:

PRINT '--Profiler trace summary--'
SELECT traceid, property, CONVERT(VARCHAR(1024), value) AS value FROM::fn_trace_getinfo(
    default)
GO
PRINT '--Trace event details--'
SELECT trace_id,
    status,
    CASE WHEN row_number = 1 THEN path ELSE NULL end AS path,
    CASE WHEN row_number = 1 THEN max_size ELSE NULL end AS max_size,
    CASE WHEN row_number = 1 THEN start_time ELSE NULL end AS start_time,
    CASE WHEN row_number = 1 THEN stop_time ELSE NULL end AS stop_time,
    max_files,
    is_rowset,
    is_rollover,
    is_shutdown,
    is_default,
    buffer_count,
    buffer_size,
    last_event_time,
    event_count,
    trace_event_id,
    trace_event_name,
    trace_column_id,
    trace_column_name,
    expensive_event
FROM
    (SELECT t.id AS trace_id,
     row_number() over(PARTITION BY t.id order by te.trace_event_id, tc.trace_column_id) AS row_number,
     t.status,
     t.path,
     t.max_size,
     t.start_time,
     t.stop_time,
     t.max_files,
     t.is_rowset,
     t.is_rollover,
     t.is_shutdown,
     t.is_default,
     t.buffer_count,
     t.buffer_size,
     t.last_event_time,
     t.event_count,
     te.trace_event_id,
     te.name AS trace_event_name,
     tc.trace_column_id,
     tc.name AS trace_column_name,
     CASE WHEN te.trace_event_id in (23, 24, 40, 41, 44, 45, 51, 52, 54, 68, 96, 97, 98, 113, 114, 122, 146, 180) THEN CAST(1 as bit) ELSE CAST(0 AS BIT) END AS expensive_event FROM sys.traces t CROSS APPLY::fn_trace_geteventinfo(t.id) AS e JOIN sys.trace_events te ON te.trace_event_id = e.eventid JOIN sys.trace_columns tc ON e.columnid = trace_column_id) AS x
GO
PRINT '--XEvent Session Details--'
SELECT sess.NAME 'session_name', event_name, xe_event_name, trace_event_id,
    CASE WHEN xemap.trace_event_id IN(23, 24, 40, 41, 44, 45, 51, 52, 54, 68, 96, 97, 98, 113, 114, 122, 146, 180) 
    THEN Cast(1 AS BIT)
ELSE Cast(0 AS BIT)
END AS expensive_event
FROM sys.dm_xe_sessions sess
JOIN sys.dm_xe_session_events evt
ON sess.address = evt.event_session_address
INNER JOIN sys.trace_xe_event_map xemap
ON evt.event_name = xemap.xe_event_name
GO

Para resolver o uso comum de alta CPU causado pela contenção de spinlock, confira as seções a seguir.

Se sua instância SQL Server tiver uma contenção de spinlock pesada SOS_CACHESTORE ou perceber que seus planos de consulta são frequentemente removidos em cargas de trabalho de consulta não planejadas, consulte o artigo a seguir e habilite o sinalizador T174 de rastreamento usando o DBCC TRACEON (174, -1) comando:

CORREÇÃO: contenção de spinlock SOS_CACHESTORE no cache de plano do SQL Server ad hoc causa alto uso da CPU no SQL Server.

Se a condição de alto uso da CPU for resolvida usando T174, habilite-a como um parâmetro de inicialização usando o SQL Server Configuration Manager.

Se sua instância SQL Server tiver um alto uso aleatório de CPU devido à SOS_BLOCKALLOCPARTIALLIST contenção de spinlock, recomendamos que você aplique a Atualização Cumulativa 21 para SQL Server 2019. Para obter mais informações sobre como resolver o problema, confira referência de bug 2410400 e DROPCLEANBUFFERS do DBCC que fornece mitigação temporária.

Se sua instância SQL Server tiver um cenário de CPU alto causado pela contenção de spinlock no spinlock em XVB_LIST computadores de alta configuração (sistemas high-end com um grande número de CPUs (processadores de nova geração)), habilite o sinalizador de rastreamento TF8102 junto com O TF8101.

Se você estiver usando uma máquina virtual, verifique se não está sobreprovisionando CPUs e se elas estão configuradas corretamente. Para obter mais informações, consulte Solução de problemas de desempenho de máquina virtual ESX/ESXi (2001003).

Se instâncias de consulta individuais estiverem usando pouca capacidade de CPU, mas a carga de trabalho geral de todas as consultas juntas causar alto consumo de CPU, considere escalar verticalmente o computador adicionando mais CPUs. Use a consulta a seguir para localizar o número de consultas que excederam um determinado limite de consumo médio e máximo de CPU por execução e foram executadas muitas vezes no sistema (modifique os valores das duas variáveis para corresponder ao seu ambiente):

-- Shows queries where Max and average CPU time exceeds 200 ms and executed more than 1000 times
DECLARE @cputime_threshold_microsec INT = 200*1000
DECLARE @execution_count INT = 1000
SELECT qs.total_worker_time/1000 total_cpu_time_ms,
       qs.max_worker_time/1000 max_cpu_time_ms,
       (qs.total_worker_time/1000)/execution_count average_cpu_time_ms,
       qs.execution_count,
       q.[text]
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
WHERE (qs.total_worker_time/execution_count > @cputime_threshold_microsec
        OR qs.max_worker_time > @cputime_threshold_microsec )
        AND execution_count > @execution_count
ORDER BY  qs.total_worker_time DESC 

• Altas concessões de CPU ou memória podem ocorrer com consultas que usam loop aninhado otimizado ou classificação em lote
• Atualizações e opções de configuração recomendadas para o SQL Server com cargas de trabalho de alto desempenho
• Atualizações e opções de configuração recomendadas para SQL Server 2017 e 2016 com cargas de trabalho de alto desempenho