Crie um site como este com o WordPress.com
Comece agora

Configuração do banco de dados tempDB

Algumas vezes respondi a perguntas em que o assunto era lentidão nas consultas do SQL Server, a relação dessa lentidão com o banco de dados tempDB, e como definir a quantidade de arquivos que devem ser criados para o tempDB. Certa vez me deparei com alguém afirmando que “… o limite máximo reconhecido pelo SQL Server é de 8 arquivos”:

p035_limite_8_arquivos_tempdb

Será que a afirmação (sublinhada na imagem acima) está correta?   😐

Introdução

O banco de dados tempDB é um dos quatro bancos de dados preexistentes em toda instância SQL Server e é utilizado para armazenar:

  • objetos temporários de usuário como tabelas temporárias (e respectivos índices, se houver), variáveis de tabelas, cursores, tabelas retornadas por funções do tipo table-valued, dentre vários outros objetos;
  • objetos internos gerados pelo database engine, como work tables, work files, arquivos intermediários de processos de ordenação;
  • repositórios de versão, no caso de versionamento de linhas

dentre outras funções.

O conteúdo do banco de dados tempDB é criado a cada vez que o serviço da instância SQL Server é iniciado, sem qualquer informação inicial. Tudo que nele existe desaparece sempre que o serviço é encerrado.

close up of menu

Antes de prosseguir, um esclarecimento. Este artigo é uma compilação de outros artigos e vídeos selecionados sobre o assunto “contenção no banco de dados tempDB”. Considere-o como um menu degustação em que os próximos links são itens do menu e que devem ser degustados à medida que são relacionados.

tempDB contention

Quando o banco de dados tempDB é intensamente utilizado podem ocorrer gargalos no momento em que o gerenciador SQL Server tenta alocar páginas. Dependendo do grau de contenção, isto pode acarretar que consultas e requisições que envolvam o banco de dados tempDB fiquem lentas ou mesmo parem de responder por instantes. Na documentação do banco de dados tempDB há dois itens que contém informações iniciais sobre a configuração: Otimizar o desempenho do TempDB no SQL Server.

As orientações atuais da Microsoft para evitar (ou mitigar) esta situação estão no documento Recommendations to reduce allocation contention in SQL Server tempdb database, que inclusive deixa bem claro que não há o limite de 8 arquivos para o banco de dados tempDB.

No artigo de Brent Ozar encontramos simplificação dessas orientações: Cheat Sheet: How to Configure TempDB for Microsoft SQL Server. Achei interessante a sugestão para definir um volume para o banco de dados tempDB e dividir de imediato todo o espaço do volume em n partes iguais. Isso evita a necessidade do SQL Server aumentar o tamanho dos arquivos sempre que necessário, pois eles já estarão configurados no maior tamanho possível e todos do mesmo tamanho.

Antes de prosseguir sugiro a leitura atenta dos documentos citados neste bloco. Caso contrário corre-se o risco de não compreender completamente a variedade de cenários possíveis e optar por soluções simplistas, como a de ampliar a quantidade de arquivos a esmo, sem qualquer critério técnico.


Em junho de 2018 foi publicado no blog MSSQL Tiger Team o artigo TEMPDB – Files and Trace Flags and Updates, Oh My!, autoria de Pam Lahoud, onde ela resume os passos em:

  • update to the latest CU;
  • create multiple tempdb files;
  • if you’re on SQL 2014 or earlier enable TF 1117 and 1118;
  • if you’re on SQL 2016 enable TF 3427.

O artigo está bem detalhado e com ilustrações que facilitam a compreensão do tema. Novamente sugiro leitura atenta do artigo citado neste bloco antes de prosseguir.


Aproveito para exibir a apresentação em vídeo do Vitor Fava, Are you having problem with your TEMPDB? Tell more about it, proferida no canal SQL Norte em julho de 2019.


Mas o mundo gira e a Lusitana roda e na versão 2019 do SQL Server foi implementado o Memory-Optimized TempDB Metadata. Há um vídeo (novamente Pam Lahoud!) sobre o assunto no Channel 9, “How and when to memory optimized TempDB metadata”, e com exemplos disponíveis no GitHub em Memory-Optimized TempDB Metadata.


Uma observação sobre a TF 1117, obtida no artigo Trace Flags 1117, 1118, and Tempdb Configuration, de Kendra Little: Not everyone likes to implement this trace flag, particularly because it impacts every database on the instance and not just tempdb. Personally, I prefer to pre-grow tempdb files so they fill the tempdb drive, just leaving room for any “free space monitoring” you have.

Até a versão 2014 do SQL Server, inclusive, ativar a TF 1117 interfere não somente no banco de dados tempDB mas também em todos os demais bancos de dados da instância. Para evitar tal situação a sugestão é pré-alocar os arquivos do tempDB no tamanho máximo desejado, desativar o autocrescimento deles e também desativar a TF 1117.

A partir do SQL Server 2016 a TF 1117 deixa de ter efeito e é possível definir para cada banco de dados o tipo de autocrescimento através do comando ALTER DATABASE: AUTOGROW_SINGLE_FILE ou AUTOGROW_ALL_FILES.


Espero que o objetivo desta compilação sobre artigos de tempDB tenha sido alcançado. O texto é pequeno mas conto que você leu cada um dos artigos indicados.

Publicidade

Uma consideração sobre “Configuração do banco de dados tempDB”

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

Este site utiliza o Akismet para reduzir spam. Saiba como seus dados em comentários são processados.

%d blogueiros gostam disto: