SQL Server TempDB Contention: PFS/GAM/SGAM Allocation Latches & Multi-File Sizing
Eliminating PAGELATCH_UP (PFS) and PAGELATCH_EX (GAM/SGAM) allocation contention in TempDB caused by intensive temporary objects: multi-file sizing, Trace Flags 1117/1118, and memory-optimized table variables.
Operational Safety & Risk Notice
This runbook contains operational intervention steps; several are write-impacting (KILL, terminate, DROP, RESIZE). Verify in a staging environment first, and apply sequentially in production with verification.
1. Symptom & Signals
- `sys.dm_os_wait_stats` içinde `PAGELATCH_UP` veya `PAGELATCH_EX` wait süreleri toplam beklemenin %30'unu aşar.
- Wait kaynaklarında `2:1:1` (PFS - sayfa 1), `2:1:2` (GAM - sayfa 2), `2:1:3` (SGAM - sayfa 3) gibi TempDB (DB 2) tahsis sayfaları görünür.
- Yüksek eşzamanlılıkta sorgularda ani kilitlenme hissi ve CPU yükselmesi olmaksızın yanıt sürelerinde uzama.
2. Business & Technical Impact
P2 - Yüksek Performans Bozulması. TempDB paylaşılan bir sistem kaynağı olduğu için tek bir uygulamanın kötü geçici tablo kullanımı tüm instance genelindeki bağımsız veritabanlarını ve sorguları yavaşlatır.
3. Diagnosis & Root-Cause Analysis
SELECT wait_type, waiting_tasks_count, wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGELATCH%'
ORDER BY wait_time_ms DESC;
-- PAGELATCH_UP: PFS (1:1, 1:8088, 1:16128) -> Data page allocation
-- PAGELATCH_EX: GAM/SGAM -> Extent allocationSELECT session_id, wait_type, wait_resource, wait_time, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%' AND resource_description LIKE '2:%';
-- 2 = TempDB database_id, sonrası page id -> 2:1:1 = PFS page 1SELECT t.session_id,
t.internal_objects_alloc_page_count / 128 AS internal_MB,
t.user_objects_alloc_page_count / 128 AS user_MB,
r.cpu_time, r.logical_reads, s.text
FROM sys.dm_db_task_space_usage t
JOIN sys.dm_exec_requests r ON t.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
ORDER BY t.internal_objects_alloc_page_count DESC;SELECT name, physical_name, size * 8 / 1024 AS size_MB, state_desc
FROM tempdb.sys.database_files;4. Remediation & Action Plan
### Senaryo A: PAGELATCH_UP (PFS Contention) — En Yaygın
Neden: Tek veri dosyası veya eşit olmayan dosya boyutları, çok fazla küçük geçici tablo oluşturma.
-- 1. TempDB veri dosyası sayısını artırın (vCPU sayısı <= 8 ise vCPU kadar; > 8 ise 8 dosya ile başlayın)
ALTER DATABASE tempdb ADD FILE (NAME='tempdev2', FILENAME='T:\TempDB\tempdb2.ndf', SIZE=4096MB, FILEGROWTH=512MB);
ALTER DATABASE tempdb ADD FILE (NAME='tempdev3', FILENAME='T:\TempDB\tempdb3.ndf', SIZE=4096MB, FILEGROWTH=512MB);
ALTER DATABASE tempdb ADD FILE (NAME='tempdev4', FILENAME='T:\TempDB\tempdb4.ndf', SIZE=4096MB, FILEGROWTH=512MB);
-- 2. TÜM DOSYALAR KESİNLİKLE EŞİT BOYUTTA OLMALIDIR!
-- Eşitlemek için gerekirse shrink/resize yapın:
-- DBCC SHRINKFILE(tempdev, 4096);
-- 3. Trace Flag 1118 ve 1117 kontrolleri (SQL 2016+ otomatik ama kontrol edin)
DBCC TRACEON(1118, -1); -- Uniform extent allocation
DBCC TRACEON(1117, -1); -- Tüm dosyalar aynı anda büyür---
### Senaryo B: Temp Table / Table Variable Kötü Kullanımı
-- Uygulamada sık görülen: SELECT INTO #Temp veya aşırı @table variable
-- Hızlı fix: Memory-optimized table variable (SQL 2019+)
DECLARE @t TABLE (id INT PRIMARY KEY NONCLUSTERED) WITH (MEMORY_OPTIMIZED = ON);
-- Uygulama kodunda: SELECT INTO yerine CREATE TABLE #temp (...) + INSERT INTO tercih edin.---
### Senaryo C: Version Store Şişmesi (Uzun Süren Transaction)
-- Version store büyüklüğünü kontrol edin
SELECT SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_MB
FROM sys.dm_db_file_space_usage;
-- Hangi transaction tutuyor?
SELECT transaction_id, transaction_begin_time,
DATEDIFF(minute, transaction_begin_time, GETDATE()) AS duration_min,
session_id
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY transaction_begin_time;
-- Açık unutulmuş oturumu sahibiyle koordine ederek sonlandırın: KILL <session_id>;5. Verification & Health Checks
- 5 dakika sonra `PAGELATCH` beklemelerinin toplam bekleme süresindeki payı düştü mü:
SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats WHERE wait_type LIKE 'PAGELATCH%';- Dosyaların eşit kullanıldığını doğrulayın:
SELECT file_id, num_of_reads, num_of_writes, io_stall_ms
FROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), NULL);
-- Hepsi benzer io_stall olmalıdır; biri %80 ise dosya boyutları dengesizdir.6. Rollback & Contingency Plan
- TempDB dosyalarını kaldırmak için dosya boşaltılmalıdır: `DBCC SHRINKFILE ('tempdevX', EMPTYFILE);` ardından `ALTER DATABASE tempdb REMOVE FILE tempdevX;`.
- **Kalıcı Önleme Standartları — dbaguide Standardı:**
- [ ] TempDB: 1 file per vCPU (maksimum 8 ile başlayın), hepsi eşit başlangıç boyutu (ör. 4GB) ve eşit autogrowth (512MB).
- [ ] TempDB dosyalarını ayrı ve hızlı fiziksel disk (NVMe / Premium SSD) üzerine konumlandırın.
- [ ] `tempdb` için `AUTOGROW_ALL_FILES = ON` (SQL 2016+ varsayılan).
- [ ] Uygulama kodu incelemesi: `SELECT INTO #temp` yerine `CREATE TABLE #temp` + `INSERT` + indeks standardı.
- [ ] Haftalık `sp_BlitzFirst @SinceStartup=1` denetimi.
Engineering Triad: Diagnostic Scripts
Diagnostic scripts recommended during this runbook triage to pinpoint the root cause:
SQL Server Wait Stats: Bottleneck Analysis
Lists top waits with percentages. The starting point to tell CPU, disk, locking or parallelism bottlenecks apart.
Read Recent SQL Server Deadlocks from system_health
Pulls deadlock graphs from the system_health Extended Events session. Lets you analyze past deadlocks without enabling trace flags.
SQL Server TempDB Usage and Top Space Consumers
Shows sessions using TempDB and the usage type (user, internal, version store). Finds the source of TempDB-full problems.