SQL Server sistemlerinde performans sorunlarının en sık görülen nedenlerinden biri tempdb veritabanındaki yoğun kullanımıdır. Özellikle yüksek işlem hacmine sahip sistemlerde, tempdb üzerindeki latch contention adı verilen bekleme türü, sistem kaynaklarını tüketir ve sorgu sürelerinin uzamasına neden olur.
Bu makalede, tempdb kaynaklı latch contention sorunlarının tespit edilmesi, anlaşılması ve çözümü için izlenmesi gereken adımları ele alacağız.
Veritabanı dosya sayısı, sisteminizdeki CPU sayısına göre optimize edilmelidir. Bu, Microsoft’un resmi önerilerine dayanan bir uygulamadır.
Genel Kural:
• 8 veya daha az mantıksal CPU varsa: TempDB veri dosyası sayısı = CPU sayısı kadar
• 8’den fazla CPU varsa: Genelde 8 dosyayla başlanır, daha fazlası gerekiyorsa performans izlenerek artırılır.
Not: Bütün dosyalar aynı boyutta ve aynı otomatik büyüme ayarında olmalıdır. Bu, tempdb üzerinde “GAM/SGAM page contention” sorunlarını önler.
Latch Contention Nedir?
Latch, SQL Server’ın veri sayfalarına güvenli şekilde erişimi sağlamak için kullandığı hafif kilitleme (lightweight lock) mekanizmasıdır. TempDB gibi yoğun yazma/okuma yapılan veritabanlarında, bu latch’lerin çakışması performans darboğazına yol açar. Bu duruma latch contention denir.
TempDB fiziksel olarak diskte olsa da, SQL Server’ın çalışma mantığı gereği tüm işlemler önce bellekte (buffer pool) gerçekleşir. Latch contention’ın temel nedeni: TempDB’deki GAM (Global Allocation Map), SGAM (Shared GAM) ve PFS (Page Free Space) sayfalar tüm tahsis işlemlerinde kilitlenir.(latch) Tüm kullanıcılar ve sistem işlemleri tek bir TempDB kullanır. Geçici tablolar, tablo değişkenleri, sıralama işlemleri için ortam sağlar. Her TempDB dosyasının kendi GAM/SGAM/PFS sayfaları vardır. Dosya sayısı artınca latch çekişmesi dağılır. Artık buffer üzerinde birden fazla tempdb dosyası işlem yapmaktadır. Bu sebepten TempDB dosya sayısı azsa aşağıdaki bekleme türleri görülmektedir.
En yaygın latch contention bekleme türleri: Bu bekleme türleri, SQL Server’ın bellek içi veri sayfalarına (buffer pool) erişim sırasında oluşan kilitlenme (latch) durumlarını gösterir.
• PAGELATCH_SH – Çok sayıda işlemin aynı veri sayfasını okumaya çalıştığı durumlarda shared kilit, aynı anda birden fazla işlemin sayfayı okumasına izin verir.
• PAGELATCH_EX – Bir işlemin sayfaya yazma yapması gerektiğinde exclusive kilit, diğer tüm erişimleri engeller.
• PAGELATCH_UP – Okumadan yazmaya geçiş anındaki latch
Bu bekleme türleri özellikle tempdb için sorunlu olabilir.
Aşağıdaki adımlarla latch contention olup olmadığını tespit edebilirsin:
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms / 1000.0 AS wait_time_sec,
100.0 * wait_time_ms / SUM(wait_time_ms) OVER () AS percent_total_waits
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGELATCH%'
ORDER BY wait_time_ms DESC;
Yukarıda dönen kavramların ne olduğunu açıklayalım:
1. PAGELATCH_SH (%43.81)
• Shared latch —> çok sayıda okuma işlemi sayfa düzeyinde çakışıyor. Genellikle tempdb’deki sıralama (ORDER BY), hash join, spool gibi işlemlerde olur.
2. PAGELATCH_EX (%39.52)
• Exclusive latch —> yazma işlemleri çakışıyor. Tempdb’de aynı GAM/SGAM sayfasına çok sayıda yazma olmasıyla oluşur. En net tempdb contention belirtisidir.
3. PAGELATCH_UP (%16.66)
• Upgrade latch — shared’dan exclusive’e geçmeye çalışırken bekleme. Bu da yine contention’ın habercisidir.
Çözüm Adımları:
1. TempDB veri dosyası sayısını artırılmalıdır. Az sayıda tempdb dosyan varsa (örneğin 8), 16 veya 32 eşit boyutlu tempdb data file oluşturulması gerekmektedir. Contention halen devam ederse data file sayısının 64’e çıkarılması gerekmektedir.
2. Dosyalar aynı boyutta ayarlanmalıdır. Büyük sistemlerde tempdb dosyalarının boyutunun büyük olması önerilir. Otomatik büyümenin dengeli ve dosyaların eşit olması önemlidir. AutoGrowth değeri 1024 MB olacak şekilde ayarlanmalıdır.
3. Autogrowth değeri yüzde değil, sabit MB olarak ayarlanmalıdır. %10 gibi oranlar sorun yaratır. 512-1024MB gibi sabit değerler kullanın.
Sunucunda çok ciddi tempdb contention problemi varsa bu, paralel işlemleri yavaşlatır ve işlem sürelerini uzatır.
Hangi dosyada contention olduğunu öğrenmek için aşağıdaki komut kullanılmaktadır:
SELECT
wt.session_id,
wt.wait_type,
wt.resource_description,
wt.blocking_session_id,
wt.wait_duration_ms,
t.text
FROM sys.dm_os_waiting_tasks wt
LEFT JOIN sys.dm_exec_requests r ON wt.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE wt.wait_type LIKE 'PAGELATCH%'
AND wt.resource_description LIKE '%2:2:%'; -- 2 = tempdb file_id
-- tempdb birden fazla file varsa komut file sayısına göre LIKE '[2-16]:%' Sütunların Anlamı:
• session_id: Beklemeye giren oturumun ID’si
• wait_type: Bekleme türü (PAGELATCH_EX, PAGELATCH_UP vs.)
• resource_description: Hangi sayfa/dosya bekleniyor: file_id:page_id:slot
• blocking_session_id: Bu oturumu engelleyen session ID (varsa)
• wait_duration_ms: Bu session’ın ne kadar süredir beklediği (ms)
• text: Bu session tarafından çalıştırılan SQL komutu (aktifse)
Yukarıdaki kodumuza örnek vermek gerekirse resource_description: 2:1:12345 ifadesini açıklayalım.
• 2: database_id — tempdb’in database id’si her zaman 2’dir..
• 1: file_id — tempdb’nin 1. dosyası olduğunu göstermektedir.
• 12345: page_id
Sunucu üzerinde contention olduğunu anlamak için:
1. Sürekli aynı file_id’de beklemeler varsa:
Örneğin tüm beklemeler 2:… yani file_id = 2 ise bu, tempdb’de ilk veri dosyası(tempdev) olduğunu göstermektedir. Birden fazla veri dosyası varsa bu değer 3,4,5… şeklinde devam etmektedir. Daha geniş file_id değerlerini kapsama almak için yukarıdaki kod bloğunda wt.resource_description LIKE ‘[2-16]:%’ şeklinde olması gerekmektedir.
2. wait_type = PAGELATCH_EX / UP ise:
Bu, yazma işlemi contention’ı demektir. Dosya sayısı artırılmalı.
3. t.text ile hangi sorgulara denk geldiğini görebilirsin:
Hangi sorgular bu beklemeyi yaratıyor? Büyük temp tablolar mı? Sıralamalar mı? Hash operasyonlar mı?
Tempdb değişiklikleri yeniden restart sonrası geçerli olmaktadır.
TempDB Veri Dosyası Sayısını Artırma Scripti:
USE master;
GO
-- tempdb data file'larının bulunduğu dizini belirtin
DECLARE @path NVARCHAR(500) = 'C:\Program Files\Microsoft SQL Server\MSSQL14.TEST23\MSSQL\DATA\tempdb_mssql_';
DECLARE @i INT = 2;
DECLARE @sql NVARCHAR(MAX);
WHILE @i <= 6
BEGIN
SET @sql = '
ALTER DATABASE tempdb ADD FILE (
NAME = temp' + CAST(@i AS NVARCHAR) + ',
FILENAME = ''' + @path + CAST(@i AS NVARCHAR) + '.ndf'',
SIZE = 512MB,
FILEGROWTH = 128MB
)';
PRINT @sql; -- İstersen önce sadece kontrol et
EXEC(@sql);
SET @i += 1;
END
GODikkat ederseniz FilePath kısmında yukarıda uzantıda son kısma ne yapmışsak o geldi. Name kısmında NAME = temp kısmında ne isim vermişsek FileName kısmında ona göre temp2-temp3-temp4-temp5-temp6 şeklinde tempdb dosyalarımızı ekleyebiliriz.
Sonuç:
TempDB üzerindeki latch contention problemleri SQL Server performansını ciddi biçimde etkiler. Bu nedenle:
• CPU sayısına uygun sayıda eşit boyutlu tempdb veri dosyası oluşturulmalı,
• Dosyalar arasındaki büyüme ayarları uyumlu olmalı,
• Contention olup olmadığı izlenmeli ve tespit edilen sorunlara göre yapılandırma yeniden ele alınmalıdır.
Bu adımları uyguladığınızda sistemdeki tempdb kaynaklı bekleme süreleri önemli ölçüde azalacaktır.
Hangi Durumlarda RAM Yerine TempDB Kullanılır?
SQL Server aşağıdaki durumlarda RAM yerine TempDB’yi kullanır:
1. Sıralama (Sort) İşlemleri
- Büyük ORDER BY işlemleri
- GROUP BY, DISTINCT gibi karmaşık sorgular
- Bellek yetersiz kaldığında sıralama işlemleri TempDB’ye taşınır
2. Hash ve Birleştirme (Join) İşlemleri
- Büyük tablolar arasında hash join yapılırken
- Merge join işlemlerinde ara sonuçlar için
3. Geçici Tablolar ve Tablo Değişkenleri
- #temp tablolar
- @table_variable’lar
- Büyük sonuç kümeleri işlenirken
4. Snapshot İzolasyonu ve MVCC
- SNAPSHOT izolasyon seviyesi kullanıldığında
- READ COMMITTED SNAPSHOT aktif olduğunda versiyon depolama için
5. Büyük DML İşlemleri
- Büyük UPDATE/DELETE işlemleri
- Toplu veri yükleme (BULK INSERT) işlemleri
- Tablo yeniden yapılandırmaları (REBUILD-REORGANIZE)
Performans Optimizasyonu İçin Öneriler
- TempDB Yapılandırması:
- Birden fazla TempDB dosyası oluşturun (4-8 arası, core sayısına göre)
- Dosyaları eşit boyutta tutun
- SSD üzerinde konumlandırın
- Bellek Yönetimi:
- max server memory ayarını doğru yapılandırın
- memory grants’i izleyin (sys.dm_exec_query_memory_grants)
- Sorgu Optimizasyonu:
- Büyük sıralama işlemlerinden kaçının
- Uygun indeksler oluşturun
- Sorgu tasarımını iyileştirin
Not: Temp_meta_data_optimization sql server 2022 ile birlikte gelen bir özellik olarak karşımıza çıkmaktadır. SQL Server’da geçici tablolar (#temp tabloları) ve tablo değişkenleri ile çalışırken ortaya çıkan meta veri kilitlenmelerini (metadata contention) optimize eden bir özelliktir. Bu yapını faydalarına söylemek gerekirse. Geçici tablo oluşturma/silme işlemlerinde sistem tabloları üzerindeki kilit çakışmalarını önler. Özellikle yoğun tempdb kullanımı olan sistemlerde performansı ciddi şekilde artırır. Aynı anda çok sayıda kullanıcının geçici tablo işlemleri yaptığı durumlarda sistemin daha iyi ölçeklenmesini sağlar. Sistem tabloları (sys.sysschobjs vb.) üzerindeki PAGELATCH_EX kilit çakışmalarını minimize eder. tempdb kaynaklı sıkıntılar varsa bu özellik aktif edilebilir.
Başka makalede görüşmek üzere..
Sizin dostunuz (veliniz), ancak Allah, O’nun elçisi, rüku ediciler olarak namaz kılan ve zekatı veren mü’minlerdir. Maide Suresi, 55. Ayet
