tempdb: l'unica risorsa condivisa che ammazza tutto

Ogni database utente è un’isola. File propri, utenti propri, backup propri. Ma ogni istanza di SQL Server ha un database condiviso che ogni sessione tocca: tempdb. È il blocco appunti. È il tavolino del caffè della sessione. È dove vivono le tabelle temporanee, dove finiscono in spool le operazioni di sort che non entrano in memoria, dove vengono memorizzati i dati di versione per l’isolamento RCSI e SNAPSHOT.

È anche la singola fonte più comune di problemi di performance “misteriosamente lenti” su un server affollato. Se gestisci un SQL Server e non hai mai passato un pomeriggio a tunare tempdb, prima o poi ti capiterà. La lezione di oggi è quel pomeriggio.

Cosa usa tempdb

Più di quel che ti aspetti:

#tabelle_temporanee e variabili tabella (@vars) — finiscono in tempdb di default.
Operazioni di sort che non entrano in memoria — vanno in spool su tempdb.
Hash join che hanno bisogno di più memoria di quella concessa — spool su tempdb.
Worktable che il motore costruisce internamente per piani di query complessi.
Version store — righe tenute vive per RCSI, isolamento SNAPSHOT, rebuild online di indici, trigger.
Stored procedure, funzioni, indici temporanei — qualsiasi cosa col prefisso #.
Carichi SSIS / ETL — spesso martellano tempdb.
Operazioni di rebuild di indici — WITH (ONLINE = ON, SORT_IN_TEMPDB = ON) usa tempdb come area di lavoro.

Tutto questo condivide un’unica tempdb sull’istanza. Quando una sessione la riempie, tutti gli altri si bloccano.

tempdb si resetta al riavvio

Curiosità: tempdb viene ricreata da zero ogni volta che SQL Server parte. Niente backup necessari (e comunque non puoi farne il backup). Quando il motore riparte, qualsiasi cosa avessi in tempdb è andata.

Questo significa anche che la dimensione di tempdb all’avvio è determinata dal database model più le dimensioni dei file che imposti esplicitamente. Se pre-dimensioni i tuoi file di tempdb a 4 GB ciascuno alla creazione, quella è la dimensione di partenza ogni volta.

Numero di file: il punto cruciale

Default di tempdb sulle versioni più vecchie di SQL Server: un solo file dati. Ogni sessione finisce per contendersi le stesse pagine di allocazione in memoria (PFS, GAM, SGAM — le tre piccole pagine che tracciano lo spazio libero).

La contesa si manifesta come attese PAGELATCH_* sulle pagine 2:1:1, 2:1:2, 2:1:3 — tempdb (database 2), primo file, prime tre pagine. Quando vedi quelle attese, non hai abbastanza file di tempdb.

Regola: un file dati di tempdb per core CPU logico, fino a 8. Oltre gli 8 hai rendimenti decrescenti; aggiungine altri solo se vedi ancora contesa dopo i primi 8.

Controlla la situazione attuale:

SELECT name, size * 8 / 1024 AS size_mb, type_desc
FROM tempdb.sys.database_files;

Aggiungi file:

-- Aggiunge altri 7 in modo da averne 8 totali su un server con 8 core logici
USE master;

ALTER DATABASE tempdb ADD FILE
    (NAME = 'tempdev2', FILENAME = 'D:\tempdb\tempdb_2.ndf', SIZE = 4096MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb ADD FILE
    (NAME = 'tempdev3', FILENAME = 'D:\tempdb\tempdb_3.ndf', SIZE = 4096MB, FILEGROWTH = 512MB);
-- ... e così via.

Tutti i file dati di tempdb dovrebbero avere la stessa dimensione con le stesse impostazioni di autogrowth. Dimensioni diseguali fanno sì che l’allocazione di SQL Server favorisca il file più grande, vanificando il senso di avere più file.

L’instant file initialization (lezione 30) si applica anche a tempdb — abilitalo se possibile.

Trace Flag 1118 — ora di default

Per molti anni dovevi abilitare TF 1118 per ottenere “uniform extent allocation” su tempdb — il che impediva un tipo specifico di contesa SGAM.

SQL Server 2016+ lo abilita di default per tempdb, rendendo il trace flag inutile. Non devi impostarlo. Ma se lavori su versioni più vecchie e vedi contesa di allocazione mixed-extent, è questo che la gente intende quando dice “accendi TF 1118.”

Metadati tempdb memory-optimized (2019+)

SQL Server 2019 ha introdotto un’opzione per spostare i metadati interni di tempdb — il catalogo di tutti gli oggetti temporanei — in tabelle memory-optimized (Hekaton). Spinta enorme per i carichi che creano e droppano tabelle temporanee a raffica.

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;
-- Richiede un riavvio del servizio.

Dopo il riavvio, parte della contesa che vedevi prima sulle operazioni sui metadati (tabelle di sistema in tempdb) sparisce.

Se il tuo carico crea migliaia di #tabelle temporanee al minuto (i pacchetti SSIS sono famigerati per questo), è una vittoria enorme. Altrimenti, non fa male.

Dimensionamento di tempdb

Parti più grande di quanto pensi. Una tempdb di produzione di 8 file × 4 GB = 32 GB è ragionevole per un server OLTP di media taglia. L’autogrowth scatta solo se la base di partenza è troppo piccola.

Segni che tempdb è sottodimensionata:

Eventi di autogrowth frequenti (visibili nella default trace / XE).
Job che improvvisamente rallentano quando un altro job gira nello stesso momento (contesa sullo spazio di tempdb).
Attese PAGEIOLATCH_* sui file di tempdb (disco lento per quello che tempdb richiede).

Setup di tempdb in produzione di Runehold:

SSD veloce dedicato per tempdb (separato dai file dati e dal log).
8 file dati × 16 GB ciascuno = 128 GB.
Autogrowth 1 GB per file, uguale su ogni file.
Metadati memory-optimized abilitati.

Con quella configurazione, tempdb si comporta male di rado.

Version store: pressione di RCSI e snapshot

Quando RCSI è attivo (lezione 19), per ogni riga modificata la versione precedente viene tenuta nel version store in tempdb finché ogni reader che era partito prima della modifica non ha finito.

Pressione:

Transazioni di lettura long-running estendono la ritenzione del version store.
Carico di scritture pesante impila molte versioni in fretta.
Vecchie transazioni non committate bloccano la pulizia del version store a tempo indefinito.

Monitora:

SELECT SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb
FROM sys.dm_db_file_space_usage;

SELECT
    s.session_id, s.login_name, s.status,
    at.transaction_begin_time,
    DATEDIFF(SECOND, at.transaction_begin_time, GETDATE()) AS seconds_open
FROM sys.dm_tran_active_snapshot_database_transactions AS sast
JOIN sys.dm_exec_sessions                               AS s
    ON s.session_id = sast.session_id
JOIN sys.dm_tran_active_transactions                     AS at
    ON at.transaction_id = sast.transaction_id
ORDER BY at.transaction_begin_time;

Se vedi una transazione aperta da un’ora, killala (o chiedi al proprietario). Una sola sessione abbandonata può inchiodare il version store e far esplodere tempdb.

Errori comuni con tempdb

Usare una variabile tabella per risultati intermedi enormi. Le variabili tabella vivono in tempdb ma hanno statistiche pessime. Per dataset grandi, una #tabella temporanea ha statistiche reali e l’optimizer ci sa lavorare. Le variabili tabella vanno bene per insiemi piccoli (< 100 righe); oltre, passa a tabelle temporanee.

Non pulire le tabelle temporanee. Si autodroppano quando la sessione termina, ma se la sessione è long-lived (un connection pool), le vecchie tabelle temporanee si accumulano. Droppale esplicitamente o usa CREATE TABLE #temp dentro le procedure dove si autodroppano all’uscita della procedura.

Uso pesante di colonne di tipo MAX nelle tabelle temporanee. NVARCHAR(MAX) forza l’allocazione di pagine LOB, che è più lenta. Dimensiona le colonne come si deve, anche nelle tabelle temporanee.

Usare tempdb per roba dall’aria permanente. Alcuni codebase creano tabelle semi-permanenti in tempdb per ETL. Al riavvio spariscono. È sempre un bug scoperto la mattina dopo.

La query “chi sta usando tempdb”

SELECT
    s.session_id,
    s.login_name,
    s.status,
    su.user_objects_alloc_page_count * 8 / 1024 AS user_objects_mb,
    su.internal_objects_alloc_page_count * 8 / 1024 AS internal_objects_mb,
    (su.user_objects_alloc_page_count + su.internal_objects_alloc_page_count) * 8 / 1024 AS total_mb
FROM sys.dm_db_session_space_usage AS su
JOIN sys.dm_exec_sessions          AS s ON s.session_id = su.session_id
WHERE su.user_objects_alloc_page_count > 0
   OR su.internal_objects_alloc_page_count > 0
ORDER BY total_mb DESC;

Una riga per sessione che usa tempdb. Quelle in cima la stanno riempiendo. Query forensica perfetta quando tempdb cresce inaspettatamente.

Prova questo sulla tua macchina

-- 1. Controlla la configurazione attuale di tempdb
SELECT name, size * 8 / 1024 AS size_mb, growth, is_percent_growth, type_desc
FROM tempdb.sys.database_files;

-- 2. Cerca page-latch wait su tempdb (indicatore di contesa)
SELECT TOP (10)
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGELATCH%'
ORDER BY wait_time_ms DESC;

-- 3. Chi sta usando tempdb proprio adesso
SELECT
    s.session_id,
    s.login_name,
    (su.user_objects_alloc_page_count + su.internal_objects_alloc_page_count) * 8 / 1024 AS tempdb_mb
FROM sys.dm_db_session_space_usage AS su
JOIN sys.dm_exec_sessions          AS s ON s.session_id = su.session_id
WHERE (su.user_objects_alloc_page_count + su.internal_objects_alloc_page_count) > 0
ORDER BY tempdb_mb DESC;

-- 4. Dimensione del version store (significativa solo se RCSI/SNAPSHOT è attivo)
SELECT SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb
FROM sys.dm_db_file_space_usage;

-- 5. Spazio libero attuale di tempdb
SELECT SUM(unallocated_extent_page_count) * 8 / 1024 AS free_mb
FROM tempdb.sys.dm_db_file_space_usage;

tempdb è la parte di SQL Server che ha più probabilità di sorprenderti alle 3 di notte. Il momento per tunarla è prima del disastro.

Prossima lezione: maintenance plan vs Ola Hallengren — perché il maintenance plan integrato fa schifo e perché alla fine tutti passano agli script di Ola.