O monitoramento do banco de dados tempdb é uma tarefa crítica para administradores de SQL Server, pois o esgotamento de espaço nesse recurso pode interromper transações e impactar a disponibilidade do ambiente. Diferente de bancos de dados comuns, o tempdb gerencia objetos internos e versões de linha que não aparecem em visualizações tradicionais como sys.partitions ou através do comando sp_spaceused.

Estrutura de Uso de Espaço

Para uma análise precisa, é necessário classificar o uso do espaço em três categorias principais:

• Objetos do Usuário: Tabelas temporárias (#temp), variáveis de tabela e índices.
• Objetos Internos: Tabelas de trabalho usadas para operações de sort, hash joins e cursores.
• Versões de Linha (Version Store): Dados mantidos para dar suporte a níveis de isolamento de snpashot, gatilhos (triggers) e operações de índice online.

Visualizações de Gerenciamento Dinâmico (DMVs) essenciais

As DMVs abaixo premitem mapear como o espaço está sendo distribuído entre os arquivos e as sessões ativas:

1. sys.dm_db_file_space_usage

Esta visão detalha a alocação por arquivo físico dentro do tempdb.

2. sys.dm_db_session_space_usage

Utilizada para identificar quais sessões estão consumindo recursos excessivos de páginas temporárias. Importante notar que os contadores são atualizados conforme as tarefas são concluídas.

Scripts de Diagnóstico de Espaço

O script a seguir consolida informações de uso global e por sessão, permitindo identificar consultas "ofensoras" que estão saturando o disco:

-- Consulta de resumo de utilização global do TempDB
SELECT 
    DB_NAME(database_id) AS NomeBanco,
    CAST(SUM(user_object_reserved_page_count) * 8.0 / 1024 AS DECIMAL(10,2)) AS EspacoUsuario_MB,
    CAST(SUM(internal_object_reserved_page_count) * 8.0 / 1024 AS DECIMAL(10,2)) AS EspacoInterno_MB,
    CAST(SUM(version_store_reserved_page_count) * 8.0 / 1024 AS DECIMAL(10,2)) AS VersionStore_MB,
    CAST(SUM(unallocated_extent_page_count) * 8.0 / 1024 AS DECIMAL(10,2)) AS EspacoLivre_MB
FROM sys.dm_db_file_space_usage
WHERE database_id = 2 -- ID do TempDB
GROUP BY database_id;

-- Identificação de sessões com alto consumo de objetos internos e temporários
SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    (usage.user_objects_alloc_page_count * 8 / 1024) AS AlocUsuario_MB,
    (usage.user_objects_dealloc_page_count * 8 / 1024) AS DesalocUsuario_MB,
    (usage.internal_objects_alloc_page_count * 8 / 1024) AS AlocInterno_MB,
    (usage.internal_objects_dealloc_page_count * 8 / 1024) AS DesalocInterno_MB,
    st.text AS TextoSQL
FROM sys.dm_db_session_space_usage AS usage
INNER JOIN sys.dm_exec_sessions AS s ON usage.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE usage.session_id > 50 
  AND (usage.user_objects_alloc_page_count > 0 OR usage.internal_objects_alloc_page_count > 0);

Monitoramento do Version Store

O Version Store pode crescer indefinidamente se houver transações de longa duração abertas sob o isolamento de snapshot. Para localizar a transação mais antiga que está impedindo a limpeza do tempdb, utilize:

SELECT 
    at.transaction_id,
    at.elapsed_time_seconds,
    st.session_id,
    st.is_snapshot
FROM sys.dm_tran_active_snapshot_database_transactions at
INNER JOIN sys.dm_tran_session_transactions st ON at.transaction_id = st.transaction_id
ORDER BY at.elapsed_time_seconds DESC;

Análise de Gargalos de I/O

Como o tempdb é intensamente acessado, o disco pode se tornar um limitador de performance. Podemos medir a latência de leitura e escrita através da DMV sys.dm_io_virtual_file_stats:

SELECT 
    file_id,
    num_of_reads,
    io_stall_read_ms / NULLIF(num_of_reads, 0) AS LatenciaLeitura_ms,
    num_of_writes,
    io_stall_write_ms / NULLIF(num_of_writes, 0) AS LatenciaEscrita_ms
FROM sys.dm_io_virtual_file_stats(2, NULL);

Valores ideais para o tempdb devem estar abaixo de 10ms para leitura e 5ms para escrita. Latências superiores a 20ms indicam a necessidade de mover os arquivos para discos mais rápidos (como SSDs/NVMe) ou revisar a configuração de arquivos de dados.

Identificação de Contenção de Metadados (DDL)

A criação e destruição excessiva de tabelas temporárias pode causar contenção em páginas de metadados do sistema (PFS, GAM, SGAM). Para observar se há sessões aguardando recursos do tempdb, monitore os latches:

SELECT 
    session_id, 
    wait_duration_ms, 
    resource_description, 
    wait_type
FROM sys.dm_os_waiting_tasks
WHERE resource_description LIKE '2:%' 
  AND wait_type LIKE 'PAGE%LATCH_%';

Se houver muitos registros onde o resource_description aponta para as primeiras páginas dos arquivos (ex: 2:1:1, 2:1:2, 2:1:3), considere habilitar a flag 1118 (em versões antigas) ou garantir que múltiplos arquivos de dados de tamanho igual foram criados para o tempdb.