Partilhar via

Recomendações para reduzir a contenção de alocação no banco de dados tempdb do SQL Server.

  • Artigo

Este artigo ajuda a resolver o problema em que você percebe um bloqueio severo quando o servidor está enfrentando uma carga pesada.

Versão original do produto: SQL Server
Número original do KB: 2154845

Sintomas

Em um servidor que está executando o Microsoft SQL Server, você percebe um bloqueio severo quando o servidor está enfrentando uma carga pesada. Exibições de Gerenciamento Dinâmico [sys.dm_exec_request ou sys.dm_os_waiting_tasks] indica que essas solicitações ou tarefas estão aguardando recursos tempdb . Além disso, o tipo de espera é PAGELATCH_UP, e o recurso de espera aponta para páginas em tempdb. Essas páginas podem ter o formato 2:1:1, 2:1:3 e assim por diante (páginas PFS e SGAM em tempdb).

Observação

Se uma página for divisível uniformemente por 8088, é uma página PFS. Por exemplo, a página 2:3:905856 é um PFS em file_id=3 em tempdb.

As seguintes operações usam tempdb extensivamente:

  • Operação repetitiva de criação e descarte de tabelas temporárias (locais ou globais).
  • Variáveis de tabela que usam tempdb para armazenamento.
  • Tabelas de trabalho associadas a CURSORS.
  • Tabelas de trabalho associadas a uma cláusula ORDER BY.
  • Tabelas de trabalho associadas a uma cláusula GROUP BY.
  • Arquivos de trabalho associados a HASH PLANS.

Essas atividades podem causar problemas de contenção.

Causa

Quando o banco de dados tempdb é muito usado, o SQL Server pode experimentar contenção ao tentar alocar páginas. Dependendo do grau de contenção, isso pode fazer com que as consultas e solicitações que envolvem tempdb parem de responder brevemente.

Durante a criação do objeto, duas (2) páginas devem ser alocadas de uma extensão mista e atribuídas ao novo objeto. Uma página é para o Mapa de Alocação de Índice (IAM) e a segunda é para a primeira página do objeto. O SQL Server rastreia extensões mistas usando a página SGAM (Mapa de Alocação Global Compartilhada). Cada página do SGAM rastreia cerca de 4 gigabytes de dados.

Para alocar uma página da extensão mista, o SQL Server deve verificar a página PFS (Espaço Livre de Página) para determinar qual página mista está livre para ser alocada. A página PFS rastreia o espaço livre disponível em cada página, e cada página PFS rastreia cerca de 8000 páginas. A sincronização apropriada é mantida para fazer alterações nas páginas PFS e SGAM; e isso pode atrasar outros modificadores por curtos períodos.

Quando o SQL Server procura uma página mista para alocar, ele sempre inicia a verificação no mesmo arquivo e na mesma página do SGAM. Isso causa intensa contenção na página SGAM quando várias alocações de páginas mistas estão em andamento. Isso pode causar os problemas documentados na seção Sintomas .

Observação

As atividades de desalocação também devem modificar as páginas. Isso pode contribuir para o aumento da contenda.

Para saber mais sobre os diferentes mecanismos de alocação usados pelo SQL Server (SGAM, GAM, PFS, IAM), consulte a seção Referências .

Solução

Aumentar o número de arquivos de dados tempdb com dimensionamento igual

Por exemplo, se o tamanho do arquivo de dados único de tempdb for de 8 GB e o tamanho do arquivo de log for de 2 GB, a recomendação será aumentar o número de arquivos de dados para oito (8) (cada um de 1 GB para manter o mesmo dimensionamento) e deixar o arquivo de log como está. Ter os diferentes arquivos de dados em discos separados forneceria um benefício adicional de desempenho. No entanto, isso não é obrigatório. Os arquivos podem coexistir no mesmo volume de disco.

O número ideal de arquivos de dados tempdb depende do grau de contenção visto em tempdb. Como ponto de partida, você pode configurar tempdb para ser pelo menos igual ao número de processadores lógicos atribuídos ao SQL Server. Para sistemas de ponta, o número inicial pode ser oito (8). Se a contenção não for reduzida, talvez seja necessário aumentar o número de arquivos de dados.

Recomendamos que você use o dimensionamento igual dos arquivos de dados. O SQL Server 2000 Service Pack 4 (SP4) introduziu uma correção que usa um algoritmo round robin para alocações de páginas mistas. Devido a essa melhoria, o arquivo inicial é diferente para cada alocação consecutiva de página mista (se houver mais de um arquivo). O novo algoritmo de alocação para SGAM é puro round robin e não respeita o preenchimento proporcional para manter a velocidade. Recomendamos que você crie todos os arquivos de dados tempdb no mesmo tamanho.

Como aumentar o número de arquivos de dados tempdb reduz a contenção

A lista a seguir explica como aumentar o número de arquivos de dados tempdb com dimensionamento igual reduz a contenção:

  • Se você tiver um arquivo de dados para o tempdb, terá apenas uma página GAM e uma página SGAM para cada 4 GB de espaço.

  • Aumentar o número de arquivos de dados que têm os mesmos tamanhos para tempdb cria efetivamente uma ou mais páginas GAM e SGAM para cada arquivo de dados.

  • O algoritmo de alocação para GAM aloca uma extensão por vez (oito páginas contíguas) do número de arquivos em um modo round robin, respeitando o preenchimento proporcional. Portanto, se você tiver 10 arquivos de tamanhos iguais, a primeira alocação será do Arquivo1, a segunda do Arquivo2, a terceira do Arquivo3 e assim por diante.

  • A contenção de recursos da página PFS é reduzida porque oito páginas por vez são marcadas como COMPLETAS porque o GAM está alocando as páginas.

Como a implementação do sinalizador de rastreamento -T1118 reduz a contenção

Observação

Esta seção se aplica apenas ao SQL Server 2014 e versões anteriores.

A lista a seguir explica como o uso do sinalizador de rastreamento -T1118 reduz a contenção:

  • -T1118 é uma configuração para todo o servidor.
  • Inclua o sinalizador de rastreamento -T1118 nos parâmetros de inicialização do SQL Server para que o sinalizador de rastreamento permaneça em vigor mesmo após a reciclagem do SQL Server.
  • -T1118 remove quase todas as alocações de página única no servidor.
  • Ao desabilitar a maioria das alocações de página única, você reduz a contenção na página SGAM.
  • Se -T1118 estiver ativado, quase todas as novas alocações serão feitas a partir de uma página GAM (por exemplo, 2:1:2) que aloca oito (8) páginas (uma extensão) por vez para um objeto, em vez de uma única página de uma extensão para as primeiras oito (8) páginas de um objeto, sem o sinalizador de rastreamento.
  • As páginas do IAM ainda usam as alocações de página única da página SGAM, mesmo que -T1118 esteja ativado. No entanto, quando ele é combinado com o hotfix 8.00.0702 e arquivos de dados tempdb aumentados, o efeito líquido é uma redução na contenção na página SGAM. Para questões de espaço, consulte a próxima seção.

Desvantagens

A desvantagem de usar -T1118 é que você pode ver aumentos no tamanho do banco de dados se as seguintes condições forem verdadeiras:

  • Novos objetos são criados em um banco de dados de usuário.
  • Cada um dos novos objetos ocupa menos de 64 KB de armazenamento.

Se essas condições forem verdadeiras, você poderá alocar 64 KB (oito páginas * 8 KB = 64 KB) para um objeto que requer apenas 8 KB de espaço, desperdiçando 56 KB de armazenamento. No entanto, se o novo objeto usar mais de 64 KB (oito páginas) em seu tempo de vida, não haverá desvantagem para o sinalizador de rastreamento. Portanto, na pior das hipóteses, o SQL Server pode alocar sete (7) páginas adicionais durante a primeira alocação apenas para novos objetos que nunca ultrapassam uma (1) página.

Referências