Temos um servidor Windows 2008 R2 executando o SQL Server 2008. De repente, o processo do SQL Server se recusa a ultrapassar 20% do uso da CPU. A partir da semana passada, ao executar uma consulta pesada contra o banco de dados, aumentaria para 100% o uso, como seria de se esperar. Nós já tivemos esse servidor por um tempo e parece estranho que de repente tivesse esse limite. Esse limite está fazendo com que nossas consultas demorem muito mais do que normalmente. Ninguém (pelo menos sabiamente) fez alterações na configuração do servidor.
Depois de um pouco de investigação, descobri a exibição sys.dm_os_sys_memory. Isso mostra que 'memória física disponível é alta' bu ao mesmo tempo em que a memória física disponível é de 339552kb, enquanto o total é de 4193848kb. Vale a pena notar que este é um servidor virtual em execução no vmware.
Existe uma configuração em algum lugar no SQL Server que define o uso máximo da CPU? Eu encontrei as configurações no governador de recursos, embora isso esteja desativado como sempre foi.
Recentemente, começamos a usar o Spotlight for SQL Server pela Quest Software. O banco de dados de playbacks estava localizado neste servidor por um curto período de tempo esta manhã, eu notei o problema logo em seguida, embora eu não tenha feito nenhuma pergunta antes disso, então não sei se esse é o ponto em que o problema começou, no entanto, o banco de dados estava funcionando como esperado na tarde de sexta-feira. O log do Windows mostra que as configurações a seguir foram aplicadas ao SpotlightPlaybackDatabase quando ele foi criado.
Alguma dessas alterações nas configurações modificou as configurações aplicadas a todo o servidor?
Editar # 1: Conseguido corrigir este problema reiniciando o sql server, não tendo certeza qual foi o problema em primeiro lugar. Apesar do problema estar sendo resolvido, ainda tenho alguns problemas para resolver que eu não estava ciente anteriormente.
Editar # 2: O problema ocorreu novamente. A solução era desativar o Trace Analysis no Spotlight no SQL Server, isso era o que estava arrastando tudo para baixo.
Verifique as sys.dm_os_waiting_tasks e veja quais são os recursos de espera. Basicamente, olhe o wait_type e veja o que está lá. Execute esta consulta e poste os resultados de volta.
select wait_type, sum(wait_duration_ms) sum_wait_duration_ms, avg(wait_duration_ms) avg_wait_duration_ms, count(*) waits
from sys.dm_os_waiting_tasks
group by wait_type
Você pode estar sofrendo de um problema parecido com o que eu falei nesta manhã em
Você não pode gerenciar o uso da CPU, mas pode gerenciar Afinidade da CPU . Ou seja, alguém restringiu o SQL Server a usar uma única CPU?
Na mesma linha, alguém alterou a configuração global maxdop ? Isso limita toda a consulta a uma CPU, mas qualquer consulta única será executada em uma das CPUs disponíveis
Supondo que não houve uma alteração de configuração na afinidade de CPU ou no MAXDOP, como mencionado por gbn, existem algumas possibilidades.
A primeira é que o plano de consulta da sua consulta foi alterado porque a distribuição dos índices ou dos dados da tabela subjacente foi alterada de forma significativa. Tente otimizar ou reconstruir índices nas tabelas subjacentes.
Em segundo lugar, você pode agora estar ligado a E / S, lendo dados do seu arquivo de banco de dados principal ou trabalhando em tempdb (onde o SQL armazenará partes intermediárias da consulta se for muito grande para RAM). Use o perfmon e monitore o avg. Comprimento da fila de disco. Deve ter uma média menor que o número de fusos de disco físico no servidor. Se ele disparar durante a "consulta pesada" enquanto a CPU estiver baixa, a CPU está simplesmente aguardando o IO do disco e, portanto, não pode executar a 100% fazendo um trabalho útil. Se este for o caso, você tem algumas opções: mais RAM (para reduzir a necessidade de usar o disco), disco mais rápido (SSD?) Ou otimizar consultas, índices e esquemas para reduzir o IO do disco. A última opção pode ter, de longe, o maior impacto (literalmente melhorando as coisas por um fator de 100 ou mais). Mas também pode ser o mais difícil, dependendo da estrutura de dados e das consultas. Leia sobre planos de execução de SQL; compre alguns livros.
Uma coisa que você pode fazer é ver exatamente o que está acontecendo com o processo que está executando a consulta. Se você continuar monitorando a atividade de spids e ver qual é o tipo de espera mais comum. Você provavelmente descobrirá que há um recurso como o disco io em que o spid está aguardando, o que significa que o cpu está inativo para a consulta até que a leitura / gravação do disco seja concluída.
Esse problema foi resolvido reiniciando o sql server, embora eu não saiba o que causou isso em primeiro lugar. Obrigado a todos por suas respostas.
Tags sql-server cpu-usage