Dicas SQL Server

Melhorar a contenção na tempdb do SQL Server

Melhorar a contenção na tempdb do SQL Server
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

 

Melhorar a contenção na tempdb do SQL Server
The following two tabs change content below.

Wesley Mota

DBA SQL Server
Profissional graduado em Banco de Dados e Sistemas de Informação com mais de 7 anos de experiência em empresas de software. Certificado MCSA Microsoft SQL Server possui intensa vivência em administração de banco de dados, Tunning, Performance SQL Server, levantamento de melhorias e monitoramento de banco de dados e servidores SQL Server. Consultoria SQL Server em diversos clientes no Brasil e ao redor do mundo. Escritor no blog dbasqlserverbr.com.br/blog. Onde compartilha conhecimento, experiências e dicas de performance para DBAs SQL Server. Conhecimentos em Oracle e ambientes de alta disponibilidade. Desenvolvimento de softwares web e mobile.Gerenciamento de equipe e projetos.

Latest posts by Wesley Mota (see all)