Recomendações para reduzir a contenção de alocação no banco de dados tempdb do SQL Server.
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
SQL Server 2016 e versões posteriores:
Revisão
Otimizando o desempenho do banco de dados tempdb no SQL Server.
TEMPDB – Arquivos, Sinalizadores de Rastreamento e Atualizações!
Aplique a relevante para SQL Server 2016 e 2017 para aproveitar a atualização a seguir. Foi feita uma melhoria que reduz ainda mais a contenção no SQL Server 2016 e no SQL Server 2017. Além da alocação round-robin em todos os arquivos de dados tempdb, a correção melhora a alocação de página PFS executando alocações round-robin em várias páginas PFS no mesmo arquivo de dados. Para obter mais informações, consulte KB4099472 – Melhoria do algoritmo de rodízio de página PFS no SQL Server 2014, 2016 e 2017.
Para obter mais informações sobre essas recomendações e outras alterações que foram introduzidas no SQL 2016, revise
SQL Server 2014 e versões anteriores:
Para melhorar a simultaneidade de tempdb, tente os seguintes métodos:
Aumente o número de arquivos de dados em tempdb para maximizar a largura de banda do disco e reduzir a contenção nas estruturas de alocação. Como regra, se o número de processadores lógicos for menor ou igual a oito (8), use o mesmo número de arquivos de dados que os processadores lógicos. Se o número de processadores lógicos for maior que oito (8), use oito arquivos de dados. Se a contenção continuar, aumente o número de arquivos de dados em múltiplos de quatro (4) até o número de processadores lógicos até que a contenção seja reduzida a níveis aceitáveis. Como alternativa, faça alterações na carga de trabalho ou no código.
Considere implementar as recomendações de práticas recomendadas em Trabalhando com tempdb no SQL Server 2005.
Se as etapas anteriores não reduzirem significativamente a contenção de alocação e a contenção estiver em páginas SGAM, implemente o sinalizador de rastreamento -T1118. Sob esse sinalizador de rastreamento, o SQL Server aloca extensões completas para cada objeto de banco de dados, eliminando assim a contenção em páginas SGAM.
Observação
Esse sinalizador de rastreamento afeta todos os bancos de dados na instância do SQL Server. Para obter informações sobre como determinar se a contenção de alocação está em páginas SGAM, consulte a contenção de monitoramento causada por operações DML.
Para ambientes do SQL Server 2014, certifique-se de aplicar o Service Pack 3 para aproveitar a correção documentada no artigo da base de dados de conhecimento a seguir. A melhoria reduz ainda mais a contenção em ambientes do SQL Server 2014. Além da alocação round-robin em todos os arquivos de dados tempdb, a correção melhora a alocação de página PFS executando alocações round-robin em várias páginas PFS no mesmo arquivo de dados.
KB4099472 - Melhoria do algoritmo PFS page round robin no SQL Server 2014, 2016 e 2017
Blog da equipe do MSSQL Tiger: Arquivos e sinalizadores de rastreamento e atualizações no tempdb do SQL Server
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.