Fiecare bază de date utilizator e o insulă. Propriile fișiere, proprii utilizatori, propriile backup-uri. Dar fiecare instanță SQL Server are o singură bază de date partajată pe care o atinge orice sesiune: tempdb. E carnetul de ciornă. E măsuța de cafea a sesiunii. E locul unde stau tabelele temporare, unde se varsă operațiunile de sortare când nu încap în memorie, unde se păstrează datele de versiune pentru izolarea RCSI și SNAPSHOT.
E și sursa cea mai comună de probleme de performanță „în mod misterios lente” pe un server încărcat. Dacă administrezi un SQL Server și n-ai petrecut niciodată o după-amiază tunând tempdb, vei petrece. Lecția de azi e acea după-amiază.
Ce folosește tempdb
Mai mult decât te-ai aștepta:
#tabele_temporareși variabilele de tip tabel (@vars) — stau în tempdb implicit.- Operațiuni de sortare care nu încap în memorie — se varsă în tempdb.
- Hash join-uri care au nevoie de mai multă memorie decât li s-a alocat — se varsă în tempdb.
- Tabele de lucru pe care motorul le construiește intern pentru planuri de query complexe.
- Version store — rânduri ținute în viață pentru RCSI, izolarea SNAPSHOT, rebuild-uri online de indecși, triggere.
- Proceduri stocate, funcții, indecși temporari — orice cu prefix
#. - Sarcini SSIS / ETL — adesea bombardează tempdb.
- Operațiuni de rebuild de index —
WITH (ONLINE = ON, SORT_IN_TEMPDB = ON)folosește tempdb ca spațiu de lucru.
Toate astea împart un singur tempdb pe instanță. Când o sesiune îl umple, toți ceilalți rămân înțepeniți.
tempdb se resetează la repornire
Curiozitate: tempdb e recreat de la zero de fiecare dată când pornește SQL Server. Nu e nevoie de backup-uri (nici nu poți face backup, oricum). Când motorul repornește, ce aveai în tempdb dispare.
Asta înseamnă și că mărimea tempdb la pornire e determinată de baza model plus mărimile fișierelor pe care le setezi explicit. Dacă pre-dimensionezi fișierele tempdb la 4 GB fiecare la creare, asta e mărimea de start de fiecare dată.
Numărul de fișiere: cel mai important lucru
tempdb implicit pe versiunile mai vechi de SQL Server: un singur fișier de date. Fiecare sesiune ajunge să se contesteze pe aceleași pagini de alocare în memorie (PFS, GAM, SGAM — cele trei pagini mici care urmăresc spațiul liber).
Contenția (page contention) apare ca așteptări PAGELATCH_* pe paginile 2:1:1, 2:1:2, 2:1:3 — tempdb (baza 2), primul fișier, primele trei pagini. Când vezi acele așteptări, n-ai destule fișiere tempdb.
Regulă: un fișier de date tempdb per nucleu CPU logic, până la 8. Peste 8, randamentul scade; adaugă mai multe doar dacă vezi în continuare contenție după primele 8.
Verifică starea curentă:
SELECT name, size * 8 / 1024 AS size_mb, type_desc
FROM tempdb.sys.database_files;Adaugă fișiere:
-- Adaugă încă 7, ca să ai 8 în total pe un server cu 8 nuclee logice
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);
-- ... și așa mai departe.Toate fișierele de date tempdb ar trebui să fie de aceeași mărime, cu aceleași setări de autogrowth. Mărimi inegale fac ca alocarea SQL Server să favorizeze cel mai mare fișier, anulând rostul de a avea mai multe fișiere.
Inițializarea instantă a fișierelor (lecția 30) se aplică și la tempdb — activează-o dacă e posibil.
Trace Flag 1118 — acum implicit
Mulți ani a trebuit să activezi TF 1118 pentru a obține „alocare uniformă de extents” pentru tempdb — care prevenea un anumit tip de contenție SGAM.
SQL Server 2016+ activează asta implicit pentru tempdb, făcând trace flag-ul inutil. Nu trebuie să-l setezi. Dar dacă lucrezi pe versiuni mai vechi și vezi contenție de alocare mixed-extent, asta e ce vor să spună oamenii când zic „pornește TF 1118”.
Metadate tempdb optimizate pentru memorie (2019+)
SQL Server 2019 a introdus o opțiune de a muta metadatele interne ale tempdb — catalogul tuturor obiectelor temporare — în tabele optimizate pentru memorie (Hekaton). Boost uriaș pentru sarcini care creează și șterg tabele temporare rapid.
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;
-- Necesită restart de serviciu.După restart, o parte din contenția pe care o vedeai înainte la operațiunile pe metadate (tabele de sistem din tempdb) dispare.
Dacă sarcina ta creează mii de tabele #temp pe minut (pachetele SSIS sunt notorii), e o victorie uriașă. Dacă nu, nu strică.
Dimensionarea tempdb
Pornește mai mare decât crezi. Un tempdb de producție cu 8 fișiere × 4 GB = 32 GB e rezonabil pentru un server OLTP de mărime medie. Autogrowth-ul intervine doar dacă baza de start e prea mică.
Semne că tempdb e subdimensionat:
- Evenimente frecvente de autogrowth (vizibile în default trace / XE).
- Joburi devin brusc lente când rulează simultan cu alt job (contenție pe spațiu tempdb).
- Așteptări
PAGEIOLATCH_*pe fișierele tempdb (disc lent pentru cât cere tempdb).
Configurația tempdb de producție la Runehold:
- SSD rapid dedicat pentru tempdb (separat de fișierele de date și log).
- 8 fișiere de date × 16 GB fiecare = 128 GB.
- Autogrowth de 1 GB per fișier, la fel pe fiecare fișier.
- Metadate optimizate pentru memorie activate.
Cu acea configurație, tempdb rareori se poartă urât.
Version store: presiunea RCSI și snapshot
Când RCSI e activat (lecția 19), versiunea anterioară a fiecărui rând modificat e păstrată în version store din tempdb până când fiecare cititor care a început înainte de modificare s-a terminat.
Presiune:
- Tranzacțiile lungi de citire prelungesc retenția version store.
- Sarcini grele de scriere stivuiesc multe versiuni rapid.
- Tranzacții vechi necommitate blochează curățenia versiunii pe nedefinit.
Monitorizare:
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;Dacă vezi o tranzacție deschisă de o oră, omoar-o (sau întreabă-l pe proprietar). O singură sesiune uitată poate ține version store-ul fixat și poate face tempdb să explodeze.
Greșeli comune cu tempdb
Folosirea unei variabile de tip tabel pentru rezultate intermediare uriașe.
Variabilele tabel stau în tempdb, dar au statistici slabe. Pentru seturi mari de date, un #temp table (tabel temporar) are statistici reale și optimizatorul poate lucra cu el. Variabilele tabel sunt în regulă pentru seturi mici (< 100 rânduri); peste atât, comută la tabele temporare.
Necurățarea tabelelor temporare.
Se șterg automat când se termină sesiunea, dar dacă o sesiune trăiește mult (un connection pool), tabelele temporare vechi se acumulează. Șterge-le explicit sau folosește CREATE TABLE #temp în interiorul procedurilor, unde se șterg automat la ieșirea procedurii.
Folosirea intensivă de coloane MAX în tabele temporare.
NVARCHAR(MAX) forțează alocarea de pagini LOB, care e mai lentă. Dimensionează coloanele corespunzător, chiar și în tabele temporare.
Folosirea tempdb pentru lucruri care par permanente. Unele baze de cod creează tabele semi-permanente în tempdb pentru ETL. La repornire, dispar. Întotdeauna e un bug descoperit a doua zi de dimineață.
Interogarea „cine folosește 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;Câte un rând per sesiune care folosește tempdb. Cele de sus îl umplu. Interogare forensică perfectă atunci când tempdb crește neașteptat.
Rulează asta pe propria mașină
-- 1. Verifică configurația curentă a tempdb
SELECT name, size * 8 / 1024 AS size_mb, growth, is_percent_growth, type_desc
FROM tempdb.sys.database_files;
-- 2. Caută așteptări de page-latch pe tempdb (indicator de contenție)
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. Cine folosește tempdb chiar acum
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. Mărimea version store (relevantă doar dacă RCSI/SNAPSHOT e activat)
SELECT SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb
FROM sys.dm_db_file_space_usage;
-- 5. Spațiul liber curent în tempdb
SELECT SUM(unallocated_extent_page_count) * 8 / 1024 AS free_mb
FROM tempdb.sys.dm_db_file_space_usage;tempdb e partea din SQL Server care e cel mai probabil să te surprindă la 3 dimineața. Momentul să-l tunezi e înainte de incident.
Lecția următoare: planuri de mentenanță vs Ola Hallengren — de ce planul de mentenanță încorporat e prost și de ce toată lumea ajunge până la urmă să treacă pe scripturile lui Ola.