SQL Server TempDB Contention: PFS/GAM/SGAM Latch ve Eşit Boyutlu Dosya Yapılandırması
TempDB üzerinde aşırı geçici tablo ve tablo değişkeni kullanımından kaynaklanan PAGELATCH_UP (PFS) ve PAGELATCH_EX (GAM/SGAM) tahsis darboğazlarını çoklu eşit veri dosyası, Trace Flag 1117/1118 ve bellek-optimize yapılarla çözme.
Operasyonel Güvenlik ve Risk Uyarısı
Bu runbook müdahale adımları içerir; birçok adım yazma etkilidir (KILL, terminate, DROP, RESIZE). Önce test ortamında deneyin, üretimde adımları sırayla ve doğrulayarak uygulayın.
1. Belirti / Symptom
- `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. Etki / 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. Teşhis / Diagnosis
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. Müdahale / Response
### 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. Doğrulama / Verification
- 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. Geri Alma / Rollback
- 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.
Mühendislik Triadı: Doğrudan Teşhis Scriptleri
Bu runbook kriz müdahalesinde darboğazı tespit etmek için önerilen hazır SQL / Shell scriptleri:
SQL Server Wait Stats: Darboğaz Analizi
Sistemin en çok hangi kaynakta beklediğini yüzdeleriyle listeler. CPU, disk, kilit veya paralellik kaynaklı darboğazı ayırt etmenin başlangıç noktasıdır.
SQL Server Son Deadlock'ları system_health Oturumundan Okuma
system_health Extended Events oturumundaki deadlock grafiklerini çeker. Trace flag açmadan geçmiş deadlock'ların nedenini analiz etmenizi sağlar.
SQL Server TempDB Kullanımı ve Alanı Tüketen Oturumlar
TempDB'de yer kullanan oturumları ve kullanım türünü (user, internal, version store) gösterir. TempDB dolması sorunlarının kaynağını bulur.