Solucionar problemas de alto uso da CPU no SQL Server

Artigo
11/19/2024

Aplica-se a: SQL Server

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

Leituras lógicas altas causadas por varreduras de tabela ou índice devido às seguintes condições:
- Estatísticas desatualizadas
- Índices ausentes
- Problemas de PSP (plano sensível ao parâmetro)
- Consultas criadas incorretamente
Aumento na carga de trabalho

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

Etapa 1: Verificar se o SQL Server está causando alto uso de CPU

Use uma das seguintes ferramentas para verificar se o processo do SQL Server está realmente contribuindo para o alto uso de 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:
```
$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 do Usuário é a soma do tempo do processador em cada processador, seu valor máximo é 100% * (número de CPUs)), o processo do 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 SO no computador estará contribuindo para o alto uso de CPU. Você deve trabalhar com o administrador do sistema para analisar a causa raiz desse comportamento.
Painel de Desempenho: no SQL Server Management Studio, clique com o botão direito do mouse em <SQLServerInstance> e selecione Painel de Desempenho de Relatórios Padrão de Relatórios>>.

O painel ilustra um gráfico intitulado Utilização da CPU do sistema com um gráfico de barras. A cor mais escura indica a utilização da CPU do mecanismo do SQL Server, enquanto a cor mais clara representa a utilização geral da CPU do sistema operacional (consulte a legenda no gráfico para referência). Selecione o botão de atualização circular ou F5 para ver a utilização atualizada.

Etapa 2: Identificar consultas que contribuem para o uso de CPU

Se o processo Sqlservr.exe estiver causando alto uso de CPU, claramente, o motivo mais comum são consultas do SQL Server que 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 ter uma ideia 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

Etapa 3: Atualizar estatísticas

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

Observação

O procedimento armazenado do sistema sp_updatestats executa UPDATE STATISTICS em todas as tabelas internas e definidas pelo usuário no banco de dados atual. Para manutenção regular, verifique se a manutenção agendada regularmente está mantendo as estatísticas atualizadas. Use soluções, como Adaptive Index Defrag, para gerenciar de forma automática a desfragmentação do índice e as atualizações de estatísticas para um ou mais bancos de dados. Esse procedimento escolhe automaticamente se deseja recompilar ou reorganizar um índice de acordo com o nível de fragmentação, entre outros parâmetros, e realizar a atualização de estatísticas com um limite linear.

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

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

Etapa 4: Adicionar índices ausentes

Í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.

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:

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

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.

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.

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

Etapa 5: Investigar e resolver problemas que diferenciam parâmetros

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").

Observação

Usar DBCC FREEPROCCACHE sem parâmetros remove todos os planos compilados do cache do plano. Isso fará com que novas execuções de consulta sejam compiladas novamente, o que levará a uma duração mais longa para cada nova consulta. A melhor abordagem é usar DBCC FREEPROCCACHE ( plan_handle | sql_handle ) para identificar qual consulta pode estar causando o problema e, em seguida, resolver essa consulta ou consultas individuais.

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.
```
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.
```
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:
```
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 o plano bom ser removido e um novo plano incorreto ser 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.
```
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%'
```

Etapa 6: Investigar e resolver problemas de SARGability

Um predicado em uma consulta é considerado SARGable (Search ARGument-able) quando o mecanismo do SQL Server pode usar um índice que busca acelerar a execução da consulta. Muitos designs de consulta impedem a SARGability e levam a verificações de tabela ou de í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 de í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 SARGable e leva a um maior consumo de CPU. As soluções normalmente envolvem a reescrita das consultas de forma criativa para tornar o SARGable. Uma possível solução para este exemplo é essa 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.

Etapa 7: Desabilitar rastreamento intenso

Verifique se o Rastreamento SQL ou rastreamento XEvent afeta o desempenho do SQL Server e causa alto uso de CPU. Por exemplo, o uso dos seguintes eventos pode causar alto uso da CPU se você rastrear atividades pesadas do SQL Server:

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

Etapa 8: Corrigir o alto uso da CPU causado pela contenção de spinlock

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

SOS_CACHESTORE contenção de spinlock

Se sua instância do SQL Server tiver uma contenção de spinlock pesada SOS_CACHESTORE ou você perceber que seus planos de consulta geralmente são 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 de CPU no SQL Server.

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

Alto uso aleatório da CPU devido à contenção de spinlock SOS_BLOCKALLOCPARTIALLIST em computadores com memória grande

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

Alto uso da CPU devido à contenção de spinlock em XVB_list em máquinas de última geração

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

Observação

O alto uso da CPU pode resultar da contenção de spinlock em muitos outros tipos de spinlock. Para obter mais informações sobre spinlocks, consulte Diagnosticar e resolver a contenção de spinlock no SQL Server.

Etapa 9: Configure sua máquina virtual

Se você estiver usando uma máquina virtual, verifique se não está superprovisionando 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).

Etapa 10: escalar verticalmente o sistema para usar mais CPUs

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

Compartilhar via

Solucionar problemas de alto uso da CPU no SQL Server

Etapa 1: Verificar se o SQL Server está causando alto uso de CPU

Etapa 2: Identificar consultas que contribuem para o uso de CPU

Etapa 3: Atualizar estatísticas

Etapa 4: Adicionar índices ausentes

Etapa 5: Investigar e resolver problemas que diferenciam parâmetros

Etapa 6: Investigar e resolver problemas de SARGability

Etapa 7: Desabilitar rastreamento intenso

Etapa 8: Corrigir o alto uso da CPU causado pela contenção de spinlock

SOS_CACHESTORE contenção de spinlock

Alto uso aleatório da CPU devido à contenção de spinlock SOS_BLOCKALLOCPARTIALLIST em computadores com memória grande

Alto uso da CPU devido à contenção de spinlock em XVB_list em máquinas de última geração

Etapa 9: Configure sua máquina virtual

Etapa 10: escalar verticalmente o sistema para usar mais CPUs

Confira também

Comentários

Recursos adicionais