Melhorar a contenção na tempdb do SQL Server

Saiba como Melhorar a contenção na tempdb do SQL Server

Hoje escreverei a vocês como consegui Melhorar a contenção na tempdb do SQL Server.

Durante uma visita recente, o cliente explicou que o desempenho em seu sistema relativamente pequeno era especialmente lento na segunda-feira de manhã, mas geralmente era ruim naquele dia inteiro.

A instância do SQL Server 2008 R2, que é executada em uma máquina virtual, tem 30 GB de RAM alocados, mas o uso de memória do SQL Server nunca excede 16 GB.

Indo mais a fundo, descobrimos que 98% de todo o I / O foi consumido pelo tempdb, então descobrimos que o sistema fazia uso extensivo de tabelas temporárias e variáveis de tabela em suas consultas.

O arquivo de dados tempdb gira em torno de 2,5 GB e o arquivo de log está em torno de 1,5 GB. Certamente não é um banco de dados enorme e intrigante porque esta pequena empresa está enfrentando problemas de desempenho.

Tudo se encaixa na memória! Que é rápido!

Observamos que a instância do SQL Server foi reiniciada no domingo anterior, como parte de um ciclo de manutenção, e que a alocação de arquivos para tempdb foi definida como os padrões:

8MB para arquivo de dados, 10% de crescimento

1 MB para arquivo de log, 10% de crescimento

Em outras palavras, não importa o tamanho do banco de dados tempdb antes do reinício, ele sempre diminuiria para esse tamanho minúsculo, precisando zerar o arquivo de log quando ele crescesse novamente.

Para chegar ao tamanho que era hoje, o tempdb tinha que aumentar o arquivo de dados 60 vezes e o arquivo de log 77 vezes. Cada vez demorou 10% a mais do que antes para crescer, exigindo 10% a mais de espaço.

É importante observar que o SQL Server não possui nenhum sinalizador de rastreamento global ativado, em particular, o TF1118.

A cada meia hora, uma captura instantânea da máquina virtual é obtida, congelando a E / S no servidor também.

Nossa resolução foi a seguinte:

• Definir o modo de economia de energia do Windows para alto desempenho
• Aumentar os arquivos de dados tempdb para corresponder ao número de núcleos da CPU
• Alterar tamanhos de arquivo padrão tempdb para o uso atual
• Alterar o crescimento do tempdb para um valor fixo, apropriado para o subsistema de E / S
• Encolher e redimensionar o log tempdb para o tamanho atual
• Ative os Sinalizadores de Rastreio 1118 e 1117 para melhorar a contenção de arquivos de dados
• Alternar de instantâneos de VM para o modo de recuperação total e habilitar backups de log de transações
• Teremos que esperar para descobrir o impacto dessas mudanças no meio ambiente ao longo de uma semana. Vou relatar de volta na próxima vez