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

  • Artigo

Este artigo ajuda você a resolve 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 de KB original: 2154845

Sintomas

Em um servidor que está executando o Microsoft SQL Server, você observa 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 temporários . Além disso, o tipo de espera é PAGELATCH_UP, e o recurso de espera aponta para páginas no tempdb. Essas páginas podem ser do 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 uniformemente divisível por 8088, ela será uma página PFS. Por exemplo, a página 2:3:905856 é um PFS em file_id=3 em tempdb.

As operações a seguir usam o tempdb extensivamente:

  • Operação repetitiva de criação e queda de tabelas temporárias (local ou global).
  • Variáveis de tabela que usam tempdb para armazenamento.
  • Tabelas de trabalho associadas ao 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 PLANOS DE HASH.

Essas atividades podem causar problemas de contenção.

Motivo

Quando o banco de dados tempdb é fortemente usado, SQL Server pode ter contenção quando tenta alocar páginas. Dependendo do grau de contenção, isso pode fazer com que consultas e solicitações que envolvem tempdb não respondam brevemente.

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

Para alocar uma página da extensão misturada, SQL Server deve examinar a página PFS (Espaço Livre de Página) para determinar qual página misturada é gratuita para ser alocada. A página PFS mantém o controle do espaço livre disponível em cada página e cada página do PFS rastreia cerca de 8.000 páginas. A sincronização apropriada é mantida para fazer alterações nas páginas PFS e SGAM; e que podem travar outros modificadores por curtos períodos.

Quando SQL Server pesquisa uma página misturada a ser alocada, ela sempre inicia a verificação no mesmo arquivo e na página 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 desa alocação também devem modificar as páginas. Isso pode contribuir para o aumento da contenção.

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

Resolução

Aumentar o número de arquivos de dados tempdb que têm dimensionamento igual

Como exemplo, se o tamanho do arquivo de dados único do tempdb for de 8 GB e o tamanho do arquivo 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 tamanho igual) e deixar o arquivo de log como está. Ter os diferentes arquivos de dados em discos separados seria um benefício de desempenho adicional. No entanto, isso não é necessá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 o tempdb para ser pelo menos igual ao número de processadores lógicos atribuídos para SQL Server. Para sistemas de ponta superior, 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 de arquivos de dados. SQL Server 2000 Service Pack 4 (SP4) introduziu uma correção que usa um algoritmo round robin para alocações de páginas misturadas. Devido a esse aprimoramento, o arquivo inicial é diferente para cada alocação de página misturada consecutiva (se houver mais de um arquivo). O novo algoritmo de alocação para SGAM é puro round robin e não honra o preenchimento proporcional para manter a velocidade. Recomendamos que você crie todos os arquivos de dados tempdb com o 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 que têm tamanho igual reduz a contenção:

  • Se você tiver um arquivo de dados para o tempdb, você 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 efetivamente cria 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 uma forma de round robin enquanto honra o preenchimento proporcional. Portanto, se você tiver 10 arquivos igualmente dimensionados, a primeira alocação será do File1, a segunda do File2, a terceira do File3 e assim por diante.

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

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

Observação

Esta seção só se aplica a versões SQL Server 2014 e anteriores.

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

  • -T11118 é uma configuração em todo o servidor.
  • Inclua o sinalizador de rastreamento -T11118 nos parâmetros inicialização para SQL Server para que o sinalizador de rastreamento permaneça em vigor mesmo após SQL Server ser reciclado.
  • -T11118 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) de cada vez em um objeto em oposição a uma única página de uma extensão para as oito primeiras (8) páginas de um objeto, sem o sinalizador de rastreamento.
  • As páginas IAM ainda usam as alocações de página única da página SGAM, mesmo que -T1118esteja ativado. No entanto, quando ele é combinado com o hotfix 8.00.0702 e o aumento dos arquivos de dados tempdb , o efeito líquido é uma redução na contenção na página SGAM. Para obter preocupações de espaço, confira a próxima seção.

Desvantagens

A desvantagem de usar -T11118 é 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 assim 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, SQL Server pode alocar sete (7) páginas adicionais durante a primeira alocação apenas para novos objetos que nunca crescem além de uma (1) página.

Referências