top of page

Quando a CPU está em 10%: identificando e solucionando a contenção silenciosa no TempDB

5 de nov.
6 min de leitura

Em atendimentos recentes a ambientes de missão crítica, nos deparamos com um sintoma clássico e recorrente: o servidor possui dezenas de núcleos, gigabytes de sobra de memória RAM, a CPU geral raramente passa de 15%, mas a aplicação inteira congela em horários de pico. Ao analisar as tarefas em espera ativa através da DMV sys.dm_os_waiting_tasks, o diagnóstico revela um volume elevado de esperas com assinaturas semelhantes a:

session_id: 84 | wait_type: PAGELATCH_UP | wait_duration_ms: 1240 | resource_description: 2:1:1 (PFS Contention) session_id: 92 | wait_type: PAGELATCH_UP | wait_duration_ms: 1180 | resource_description: 2:1:3 (SGAM Contention)

Nesse cenário, CPU baixa acompanhada de lentidão severa é o sintoma clássico de que o gargalo migrou do processamento para filas de espera (wait stats). Embora muitos pensem imediatamente em I/O de disco saturado ou bloqueios lógicos entre tabelas (locks/deadlocks), a causa oculta frequentemente reside na disputa interna por travas transitórias em memória (latches) sobre as páginas de controle e alocação do banco temporário. Qualquer tentativa de reiniciar a instância ou superdimensionar o hardware apenas adia o sintoma e encarece desnecessariamente a fatura de infraestrutura.


1. O que significa o resource_description: 2:1:1?


No Microsoft SQL Server, o banco de dados tempdb possui ID fixo 2. A convenção de do recurso ID_Banco:ID_Arquivo:ID_Página. em espera segue o padrão.

Quando múltiplas sessões criam e destroem simultaneamente tabelas temporárias (#temp), variáveis de tabela (@tabela) ou executam consultas pesadas que exigem ordenações e junções em disco (Sort Spills / Hash Spills), o motor precisa registrar continuamente onde há espaço livre para gravar dados.

Esse controle é realizado por páginas especiais de alocação de 8 KB mantidas no Buffer Pool:

  • Página 1 (2:1:1 – PFS / Page Free Space): Rastreia o percentual de espaço livre (0%, 1-50%, 51-80%, 81-95%, 96-100%) em cada página de um intervalo de 8.088 páginas (aproximadamente 64 MB). Novas páginas PFS repetem-se a cada 8.088 páginas no arquivo.

  • Página 2 (2:1:2 – GAM / Global Allocation Map): Rastreia quais extensões (extents de 64 KB, formadas por 8 páginas de 8 KB) já foram alocadas para algum objeto.

  • Página 3 (2:1:3 – SGAM / Shared Global Allocation Map): Rastreia extensões mistas (mixed extents) que ainda possuem ao menos uma página livre disponível para alocação compartilhada.


Quando a instância opera com apenas um único arquivo de dados (.mdf) no TempDB, centenas de threads concorrentes competem exatamente pela mesma página de 8 KB na memória. Instala-se o fenômeno da serialização forçada: dezenas de transações entram em fila aguardando frações de milissegundos para obter um latch (trava transitória em memória), degradando brutalmente o rendimento da aplicação.


2. Query de Diagnóstico em Tempo Real

Para identificar em tempo real se o ambiente sofre com contenção de alocação no TempDB e capturar os comandos SQL causadores, execute:

SELECT 

wt.session_id,

wt.wait_type,

wt.wait_duration_ms,

wt.blocking_session_id,

wt.resource_description,

er.command,

SUBSTRING(st.text, (er.statement_start_offset/2) + 1,

((CASE er.statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE er.statement_end_offset

END - er.statement_start_offset)/2) + 1) AS active_statement

FROM sys.dm_os_waiting_tasks AS wt

INNER JOIN sys.dm_exec_requests AS er

ON wt.session_id = er.session_id

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st

WHERE wt.wait_type LIKE 'PAGELATCH_%'

AND wt.resource_description LIKE '2:%';

Se o retorno apresentar múltiplas sessões com PAGELATCH_UP ou PAGELATCH_EX apontando para o banco 2 em páginas de alocação (como 2:1:1, 2:1:2, 2:1:3 ou múltiplos de PFS como 2:1:8088, 2:1:16176), a arquitetura de arquivos e a gestão de metadados do TempDB devem ser priorizadas.


3. A Correção: equalização simétrica de arquivos

A principal solução arquitetural consiste em particionar a carga criando múltiplos arquivos secundários de dados (.ndf). Dessa forma, cada arquivo passa a ter sua própria cadeia isolada de páginas PFS, GAM e SGAM, distribuindo as operações de alocação por diferentes buffers.


Diretrizes de Engenharia recomendadas:

  • Regra dos Núcleos Lógicos (Cores): Para instâncias com até 8 núcleos lógicos, configure 1 arquivo de dados por núcleo. Para ambientes com mais de 8 núcleos lógicos, inicie com 8 arquivos de dados e monitore. Apenas aumente em blocos de 4 se a contenção de alocação persistir.

  • Simetria Absoluta (Proportional Fill): Todos os arquivos de dados do TempDB devem possuir exatamente o mesmo tamanho inicial e a mesma taxa fixa de autogrowth em MB (nunca em percentual). Isso assegura que o algoritmo nativo de Proportional Fill distribua as alocações de forma perfeitamente homogênea.

  • Restrição estrita do Arquivo de Log (.ldf): A divisão simétrica se aplica exclusivamente aos arquivos de dados (.mdf e .ndf). O TempDB deve possuir rigorosamente apenas 1 arquivo de Transaction Log. Como a gravação do log de transações é estritamente sequencial e monothread por banco, criar múltiplos arquivos de log não traz ganho de paralelismo, fragmenta o overhead de I/O e degrada a performance do motor.

  • Pré-requisito Crítico de IFI (Instant File Initialization): O autogrowth simétrico exige que o privilégio de segurança local do Windows 'Perform volume maintenance tasks' (SE_MANAGE_VOLUME_NAME) esteja explicitamente concedido à conta de serviço do SQL Server. Sem o IFI habilitado, qualquer crescimento de 1 GB configurado no script forçará o sistema operacional a preencher todo o novo espaço com zeros no disco (zeroing). Esse processo causa picos severos de I/O de escrita e congela todas as transações que dependem do TempDB durante os segundos em que o arquivo se expande.

  • Isolamento físico de Storage: Em ambientes de missão crítica, qualquer aumento na latência de storage reflete diretamente na duração dos PAGELATCHes em memória ao persistir e reciclar estruturas. O TempDB deve residir em volumes dedicados de altíssimo rendimento (NVMe / All-Flash Tier 0), fisicamente ou logicamente isolados dos arquivos de dados de usuário (.mdf das aplicações), dos logs de produção e do sistema operacional.


Script T-SQL balanceado e validado para equalização com 4 arquivos de dados:

USE [master];

GO

ALTER DATABASE [tempdb] MODIFY FILE (

NAME = N'tempdev',  SIZE = 1048576KB,  FILEGROWTH = 1048576KB ); ALTER DATABASE [tempdb] ADD FILE (  NAME = N'tempdev02',  FILENAME = N'S:\Data\tempdev02.ndf', 

SIZE = 1048576KB, 

FILEGROWTH = 1048576KB 

); 

ALTER DATABASE [tempdb] ADD FILE ( 

NAME = N'tempdev03', 

FILENAME = N'S:\Data\tempdev03.ndf', 

SIZE = 1048576KB, 

FILEGROWTH = 1048576KB 

);

ALTER DATABASE [tempdb] ADD FILE ( 

NAME = N'tempdev04', 

FILENAME = N'S:\Data\tempdev04.ndf', 

SIZE = 1048576KB, 

FILEGROWTH = 1048576KB 

);

GO


4. Engenharia de plicação: Cuidado com Índices em #temp

Um dos erros mais comuns de desenvolvimento é criar tabelas temporárias e, logo em seguida, aplicar comandos DDL tardios como CREATE NONCLUSTERED INDEX. Instruções DDL executadas após o CREATE TABLE invalidam o TempTable Caching do SQL Server. A cada execução da procedure, a tabela precisa ser recriada e destruída no catálogo do sistema, gerando uma severa disputa de metadados (Metadata Contention). Para manter o TempTable Caching funcional e permitir a reutilização das páginas sem disputar o catálogo, declare os índices inline diretamente na instrução CREATE TABLE (recurso suportado a partir do SQL Server 2014):


-- Sintaxe recomendada (mantém o TempTable Caching ativo):


 CREATE TABLE #ProcessamentoVendas (


  ID INT NOT NULL,


  DataProcessamento DATETIME NOT NULL,


  Valor DECIMAL(18, 2) NOT NULL,


  INDEX IX_ProcessamentoVendas_DataProcessamento (DataProcessamento)


 );


5. A evolução do mecanismo através das versões do SQL Server

A forma de mitigar a contenção no TempDB evoluiu substancialmente ao longo das gerações da plataforma de dados:

Linha do Tempo e Evolução Arquitetural do TempDB


• Ambientes Legados (SQL Server 2012 / 2014): 

O balanceamento automático de alocação não existia por padrão. Nesses ambientes, era mandatório habilitar globalmente as Trace Flags 1118 e 1117 via parâmetros de inicialização (-T1118 -T1117). O TF 1118 elimina a disputa na SGAM ao forçar a alocação de extensões uniformes (uniform extents), enquanto o TF 1117 obriga todos os arquivos de dados a crescerem em sincronia quando o autogrowth é acionado. 


• SQL Server 2016 e 2017: 

Os comportamentos dos antigos TF 1118 e TF 1117 foram incorporados nativamente ao motor de banco de dados, e o programa de instalação passou a detectar o número de núcleos lógicos para criar múltiplos arquivos de dados automaticamente durante o setup inicial. 


• SQL Server 2019: 

Para solucionar a contenção em nível de catálogo (Metadata Contention provocada por excesso de DDL em tabelas temporárias), a Microsoft introduziu o recurso 'Memory-Optimized TempDB Metadata', movendo as tabelas internas do catálogo do TempDB para estruturas não bloqueantes em memória (In-Memory OLTP), ativável via: ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON.


• SQL Server 2022 (System Page Latch Concurrency Enhancements): 

O SQL Server 2022 revolucionou a disputa de alocação ao introduzir concorrência nativa em páginas do sistema. Em vez de exigir travas exclusivas (Exclusive Latches) ou travas de atualização (Update Latches) que serializavam todas as operações na página PFS (2:1:1), o motor passou a utilizar travas compartilhadas (Shared Latches) para atualizações concorrentes. Isso elimina o gargalo estrutural diretamente no código do motor e diminui sensivelmente a necessidade de criar dezenas de arquivos de dados em instâncias de altíssima escala.


Conclusão

A contenção de alocação e metadados no TempDB é o exemplo definitivo de que capacidade computacional bruta não compensa falhas de arquitetura de dados. Compreender a transição entre o gargalo de processamento e o de espera, garantir o isolamento de storage ultraveloz, ativar o IFI no sistema operacional, manter a simetria estrita dos arquivos de dados e adotar boas práticas de DDL inline são as premissas essenciais que transformam servidores subutilizados em ambientes de missão crítica estáveis, previsíveis e de máxima performance.

 
 
 

Comentários


Logo_quadrada.png

CLOUD DB LTDA - 28.961.479/0001-41

© 2020 Direitos Reservados CloudDB ®  O plágio é considerado crime e sua principal referência é a lei 9.610 - ♥ SEO WIX Partner - Phoenix

bottom of page