Dois dias atrás, nosso servidor de produção sofreu uma grande desaceleração, onde o principal sintoma foi que um número extraordinariamente alto de solicitações estava sofrendo SQLTimeouts. Descreverei rapidamente nossa configuração, o que eu investiguei, nossa solução alternativa e, em seguida, sigo com minha pergunta.
Nossa configuração
Um par de servidores hospeda essa ramificação do nosso aplicativo SAS. Um deles é um servidor de aplicativos que executa vários aplicativos no IIS e o outro, aquele que sofreu a lentidão, é uma caixa do Windows Server 2008 que executa o SQL Server 2005. O SQL hospeda algo entre 100 e 200 bancos de dados.
O problema / investigação
O serviço fica praticamente paralisado. Algumas solicitações passam, mas a maioria sofre timeouts de SQL. A CPU e a RAM da máquina SQL parecem bem, com uma média de 25% da carga de trabalho da CPU e 85% da RAM. Eu não pensei em verificar a atividade do disco no momento, pois fui direto para 'EXEC sp_who2'
O resultado mostrou centenas de tarefas bloqueadas pela ID 123, que era ela mesma e com outras centenas bloqueadas pela ID 456. A execução normal geralmente não tem nenhuma tarefa de bloqueio. Quando re-executei o sp_who2 após 15-20 segundos, diferentes IDs de bloqueio apareceram, mas a quantidade de tarefas bloqueadas / bloqueadas pareceu permanecer a mesma. (não contei os grupos por causa do modo de emergência)
A maioria das tarefas estava bloqueando com instruções como "SELECT INTO" ou "CREATE INDEX on tentable".
A solução alternativa
Mate o processo SQL e reinicie-o para restaurar o serviço. A desaceleração não reapareceu, mas sabemos que estamos em risco.
Minha pergunta
O que posso fazer para corrigir este problema, de preferência antes de voltar a ocorrer?
Sub-perguntas:
- Existe outro caminho que eu possa investigar durante a atividade normal?
- Se / quando o problema voltar a ocorrer, que informações devo reunir? (Precisa ser rápido para obter, pois isso significa que estaremos enfrentando uma falha de serviço novamente)
O que eu fiz até agora
A partir dos sintomas, suspeitamos que o problema tenha sido uma contenção de algum tipo no tempdb. (Outro sintoma foi que clicar com o botão direito no tempdb para ver as propriedades durante o problema gerou um erro após um curto período)
Nenhum registro indicou que um evento de crescimento automático ocorreu em tempdb, embora, até onde eu saiba, os sucessos de crescimento automático não estejam registrados, apenas falhas.
Eu tenho lido muitas fontes diferentes de informação desde então na contenção de tempdb, não limitada a mas incluindo:
link
link
Pelo que entendi, é uma boa prática ter arquivos tempdb com tamanho inicial definido e ter um por núcleo, até 8 arquivos. É nosso plano colocar isso em prática (8 núcleos, portanto, 8 arquivos) o mais rápido possível, já que é uma prática recomendada. Eles estariam todos no mesmo disco rígido (por enquanto), mas acreditamos que o pior caso é não melhorar, e o melhor é ganhar a diferença entre o gargalo da contenção lógica e o gargalo de E / S do disco.
No entanto, não podemos ter certeza da correlação com o problema que tivemos. Pelo que entendi, a divisão em vários arquivos temporários ajudaria o tipo de espera "PAGELATCH_XX", mas executando a consulta de Paul S. Randal (consulte o primeiro link publicado) durante a atividade normal, esse tipo de espera está ausente. Os 3 principais que vejo durante a atividade normal são:
CXPACKET 68,63%
LATCH_EX 18,46%
PAGEIOLATCH_SH 4,35%
Não tenho como saber que tipo de bloqueio estava ocorrendo durante a desaceleração, já que não tínhamos todas essas informações.