O uso da CPU do SQL Server é sobrecarregado às vezes

Alguém é capaz de lançar alguma luz sobre o que pode estar acontecendo, ou me guiar através de algumas coisas para verificar.

Vamos começar com as coisas óbvias. Seu servidor, para uma caixa SQL de produção, não é tão potente. Por exemplo, é nisso que eu trabalho de desenvolvimento , e sou apenas um consultor brincando na maior parte do tempo.

Dito isto, mais hardware pode não resolver todos os seus problemas. Não estou familiarizado com a plataforma que você mencionou, mas normalmente quando você começa com um produto base e começa a se referir a ele como "altamente personalizado", isso significa que o código e os índices que costumavam trabalhar com o produto base provavelmente não já não funciona tão bem.

Com o que você pode começar?

A empresa para a qual trabalho escreve scripts gratuitos que podem ajudá-lo a chegar à raiz dessas coisas. Se é algo que você pode consertar com hardware ou com suporte do fornecedor, é outra questão.

Muitos fornecedores não gostam que você faça alterações em seus produtos. Mas ei, pelo menos você pode iniciar uma conversa sobre como fazer alterações.

1. Executar sp_Blitz

EXEC sp_Blitz @CheckUserDatabaseObjects = 1, @CheckServerInfo = 1;

Isso lhe dará uma idéia geral sobre o que está acontecendo em termos de saúde com seu servidor. Preste atenção a algumas configurações aqui: MAXDOP e Cost Threshold for Parallelism, e veja minha resposta aqui para saber por que eles podem fazer a diferença na sua situação.

2. Execute sp_BlitzFirst

EXEC sp_BlitzFirst @SinceStartup = 1;

Isso lhe dirá o que seu servidor está fazendo desde que foi inicializado. Observe o painel de estatísticas de espera para ver onde estão seus gargalos.

3. Execute sp_BlitzCache

EXEC sp_BlitzCache @SortOrder = 'cpu';

Como você está preocupado com o cache do seu plano, analise-o primeiro pela CPU. Você pode achar outras ordens de classificação úteis no futuro, mas comece aqui.

Vamos avisá-lo sobre todos os tipos de coisas em seus planos de consulta e fornecer o máximo de informações históricas possível.

4. Execute sp_BlitzIndex

EXEC sp_BlitzIndex @DatabaseName = N'YourDatabaseName', @Mode = 4

A principal coisa que você vai querer ver aqui são os índices ausentes de alto valor, que devem estar bem no topo.

4. Quando você pegar o pico de CPU, execute sp_BlitzWho

EXEC sp_BlitzWho

Isso permitirá que você saiba quais consultas estão sendo executadas quando a CPU está alta. Eles podem ser diferentes do que está no cache do plano.

Espero que isto ajude!

Monitorar o uso da CPU usando o gerenciador de tarefas não é realmente uma fonte confiável. Existem muitos outros processos não-sql (como atividade principal do sistema operacional, drivers de dispositivo) em execução em segundo plano que podem estar adicionando sobrecarga extra sem você saber.

PerfMon é a ferramenta que você deve usar nesses casos.

Processador/% Tempo Privilegiado, Processador/ % Tempo do Usuário, Processo (sqlservr.exe)/ % Tempo do Processador

Vai te dar uma ideia do que realmente está acontecendo com o seu servidor SQL, sem explicar cada um desses contadores, ative a caixa de seleção de descrição e leia a partir daí, mas essencialmente mostrará a proporção do uso do SQL Server vs Outros processos.

Embora seja fácil de detectar, não é tão fácil de diagnosticar. Pode haver outros problemas "ocultos" que indicam que o processador é o problema. Como ter muitas compilações/recompilações, que são problemas relacionados a consultas não parametrizadas ou recompilações forçadas. Você pode encontrar essas métricas em Perfmon: SQLServer:SQL Statistics/SQL Compilations/sec, SQLServer:SQL Statistics/SQL Re-Compilations/sec .

SQLServer:Plan Cache/Cache hit Ratio Indica problema de memória, mas a liberação excessiva de páginas na memória também adiciona uso extra da CPU.

Os DMVs também podem ajudá-lo a diagnosticar o problema.

SELECT TOP ( 10 )
wait_type ,
waiting_tasks_count ,
( wait_time_ms - signal_wait_time_ms ) AS resource_wait_time ,
max_wait_time_ms ,
CASE waiting_tasks_count
WHEN 0 THEN 0
ELSE wait_time_ms / waiting_tasks_count
END AS avg_wait_time
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%' 
AND wait_type NOT LIKE 'XE%'
AND wait_type NOT IN
( 'KSOURCE_WAKEUP', 'BROKER_TASK_STOP', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER',
'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE',
'DBMIRROR_EVENTS_QUEUE', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',
'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE',
'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS',
'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH' )
ORDER BY wait_time_ms DESC

Veja se você pode encontrar esperas SOS_SCHEDULER_YIELD & CXPACKET. Se as esperas de SOS_SCHEDULER_YIELD forem altas, você pode ter algumas consultas muito extensas da CPU, às quais você deve prestar atenção. Este:

SELECT TOP ( 10 )
SUBSTRING(ST.text, ( QS.statement_start_offset / 2 ) + 1,
( ( CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset
END - QS.statement_start_offset ) / 2 ) + 1)
AS statement_text ,
total_worker_time / 1000 AS total_worker_time_ms ,
( total_worker_time / 1000 ) / execution_count
AS avg_worker_time_ms ,
total_logical_reads ,
total_logical_reads / execution_count AS avg_logical_reads ,
total_elapsed_time / 1000 AS total_elapsed_time_ms ,
( total_elapsed_time / 1000 ) / execution_count
AS avg_elapsed_time_ms ,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC

mostrará as consultas mais extensas da CPU que você pode querer otimizar ainda mais.

Ao otimizar essas consultas, você pode encontrar índices ausentes, estatísticas desatualizadas, consultas não sarg-able, que são problemas reais que estão por trás do alto uso da CPU.

Não é o único plano de como corrigir problemas de CPU, mas espero que seja um bom começo!

Há duas coisas possivelmente acontecendo.

Vamos começar com o seguinte: Na maioria dos casos de uso de um SQL Server, a CPU é o elemento menos oneroso. Só não sobrecarrega. Período. Existem algumas exceções, mas eu diria que 99% dos casos a sobrecarga da CPU pode ser rastreada até o SQL incorreto ou o design de tabela que faz com que o SQL Server desperdice grandes quantidades de ciclos de CPU - fazendo uma conversão de dados que é realmente desnecessária.

Exemplo: A Tabela 1 tem um campo de ID que é inteiro, a Tabela 2 faz referência a ele, mas a chave estrangeira é caractere (armazenando um valor numérico). Bem-vindo à conversão de dados em cada junção - totalmente evitável e geralmente um pequeno erro "estúpido" (tipo de dados negligenciado ou algo assim) que é fácil de corrigir. QUASE tão ruim é o uso de campos de caracteres NVarchar (no SQL Server) para pesquisa - as regras de comparação de strings Unicode são complexas, o N (por exemplo, para uma tabela de códigos de produtos) é 20 vezes mais intensivo em CPU do que um VARCHAR puro (que não não lidar com Unicode). Às vezes você precisa, mas às vezes são apenas regulamentos estúpidos (todos os campos são Unicode, mesmo aqueles que contêm coisas como números de telefone).

Para corrigir isso, você realmente precisa entrar nas consultas lentas e identificá-las - há muitas ferramentas disponíveis, mesmo no SQL Server Management Studio. O Activity Monitor (a documentação pode ajudá-lo a encontrá-lo) fornecerá com prazer as consultas recentes mais caras por CPU - então você pode examiná-las e começar a descobrir onde está o erro. Tenho certeza de que uma investigação decente apontará para vários problemas fáceis de corrigir que são bastante triviais, mas resultam nos usos extremos da CPU mencionados.

Mas como elemento para - você afirma tão bem: "Agora o servidor executando SQL é bastante poderoso" - eu odeio ir contra sua ilusão aqui, mas este é o ano de 2017. Uma máquina de 4 núcleos com 14 GB de memória não é "bastante poderosa" . É essencialmente "low end". Eu não trabalharia em um desktop assim. Agora, não estou dizendo "pegue uma máquina maior", apenas não ostente especificações ultra baixas como de ponta, por favor. Existem TABLETS no mercado que são mais poderosos que o seu servidor "bastante poderoso". Observe que um problema na disponibilidade do servidor (memória insuficiente, largura de banda de E/S insuficiente) geralmente resulta em CPU BAIXA (aguardando CPU), portanto, isso não está relacionado a esse problema específico. Você pode apenas considerar o dimensionamento do servidor quando tiver problemas. Para lhe dar uma idéia de quão baixo é o seu maquine - você executa o SQL Server, que requer um Windows licenciado. A menor licença do Windows Server que você pode comprar cobre 16 núcleos, 4 vezes o seu número.