SQL Server Eğitimi: tempdb Çekişmesini Ölçme ve Kalıcı Azaltma

SQL Server eğitimi ile tempdb çekişmesini doğru sınıflandırmak

tempdb sorunu denince doğrudan dosya eklemek, çoğu üretim ortamında yanlış ilk müdahaledir. Önce beklemenin latch mi, disk I/O mu, yoksa transaction log flush mı olduğunu ayırın. Allocation bitmap sayfalarındaki eşzamanlı erişim tipik olarak PAGELATCH_UP veya PAGELATCH_EX üretir; bu bellek içi latch'tir ve depolama IOPS artırmak bunu çözmez. Buna karşılık PAGEIOLATCH_* beklemeleri fiziksel okuma, WRITELOG ise log flush gecikmesine işaret eder. Aşağıdaki sorguyu 5 dakikalık yoğun pencerede iki kez çalıştırın, sonuçları saklayın ve wait_time_ms delta'sını karşılaştırın.

SELECT TOP (20)
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    CAST(wait_time_ms * 1.0 /
         NULLIF(waiting_tasks_count, 0) AS decimal(12,2)) AS avg_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN (
    'PAGELATCH_UP', 'PAGELATCH_EX',
    'PAGEIOLATCH_SH', 'PAGEIOLATCH_EX', 'WRITELOG'
)
ORDER BY wait_time_ms DESC;

Bekleme istatistikleri sunucu başlangıcından beri birikir; tek başına bu çıktı değişiklik etkisini kanıtlamaz. Kontrollü önce-sonra ölçümü için bakım penceresinde sayaçları sıfırlayın veya daha güvenlisi iki örnek arasında delta alın. Sıfırlama, aynı instance üzerindeki tüm iş yüklerinin görünürlüğünü etkilediğinden yalnızca onaylı test ortamında kullanılmalıdır: DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);. Bu ayrım, veritabanı eğitimi kapsamında sık atlanan bir noktadır: latch beklemesi ile I/O beklemesini aynı 'tempdb yavaş' etiketi altında toplamak, yanlış katmana yatırım yapılmasına neden olur.

PFS, GAM ve SGAM sayfalarını DMV ile kanıtlamak

Allocation latch çekişmesinin tempdb kaynaklı olduğunu doğrulamak için anlık bekleyen görevlerdeki resource_description alanını inceleyin. tempdb dosya kimliği 2 ise 2:1:1 PFS, 2:1:2 GAM ve 2:1:3 SGAM gibi sayfa kimlikleri güçlü kanıttır. Bu sorgu, bekleyen oturumun programını ve SQL metnini beraber getirir; böylece yalnızca altyapıyı değil, çekişmeyi üreten uygulama rotasını da bulursunuz.

SELECT
    wt.session_id,
    wt.wait_type,
    wt.wait_duration_ms,
    wt.resource_description,
    s.program_name,
    r.command,
    SUBSTRING(t.text,
      (r.statement_start_offset / 2) + 1,
      ((CASE r.statement_end_offset
          WHEN -1 THEN DATALENGTH(t.text)
          ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1) AS statement_text
FROM sys.dm_os_waiting_tasks AS wt
JOIN sys.dm_exec_sessions AS s
  ON s.session_id = wt.session_id
LEFT JOIN sys.dm_exec_requests AS r
  ON r.session_id = wt.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE wt.wait_type IN ('PAGELATCH_UP', 'PAGELATCH_EX')
  AND wt.resource_description LIKE '2:%';

Yaygın edge case şudur: birden fazla tempdb data file varsa resource_description içindeki ilk sayı her zaman 2 olmayabilir; bu değer database_id'dir, ikinci bileşen file_id'dir. Ayrıca bekleyen görev anlık olduğu için sorgu boş dönebilir. Bu durumda Extended Events ile sqlos.wait_info, sqlserver.sql_batch_completed ve sqlserver.rpc_completed olaylarını kısa süreli bir oturuma alın. 30-60 saniyelik hedefli iz, sürekli Profiler çalıştırmaktan daha düşük toplama maliyeti yaratır. Bu pratik yaklaşım, sql eğitimi içeriğinde DMV sorgusunu üretim teşhisine bağlar.

Veritabanı optimizasyonu için tempdb dosya geometrisini düzeltmek

Kanıt allocation latch çekişmesini gösteriyorsa data file'ları eşit başlangıç boyutu ve eşit autogrowth ile yapılandırın. Amaç, SQL Server'ın proportional fill davranışında tek bir dosyaya yönelmesini önlemektir. Başlangıç noktası olarak mantıksal CPU sayısına körü körüne eşit dosya açmayın; dört eşit data file ile başlayıp ölçün, çekişme sürerse kademeli artırın. Her dosyayı aynı disk katmanına koymak da şart değildir, fakat gecikme ve kapasite profilleri eşdeğer olmalıdır.

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (
    NAME = tempdev,
    SIZE = 8192MB,
    FILEGROWTH = 512MB
);
ALTER DATABASE tempdb
MODIFY FILE (
    NAME = temp2,
    SIZE = 8192MB,
    FILEGROWTH = 512MB
);
ALTER DATABASE tempdb
MODIFY FILE (
    NAME = templog,
    SIZE = 4096MB,
    FILEGROWTH = 512MB
);
GO

Bu değişiklikten önce sys.master_files üzerinden fiziksel dosya boyutlarını kaydedin; sonra aynı yük testi altında PAGELATCH bekleme oranını, batch request/sec değerini ve p95 istek süresini karşılaştırın. Dosya eklemek log çekişmesini çözmez: tempdb transaction log tek dosyadır ve WRITELOG baskınsa VLF düzeni, log büyüklüğü, büyüme olayı ve depolama flush gecikmesi ayrıca incelenmelidir. Veritabanı performans ayarlama çalışmasında başarısız bir örnek, 16 dosya ekleyip aslında disk doluluğu yüzünden her dosyada küçük autogrowth tetiklemektir; bu durumda metadata çekişmesi azalırken büyüme duraklamaları görünür hale gelir.

Database indexleme ve sorgu tasarımıyla tempdb üretimini azaltmak

Dosya düzeni yalnızca allocation yolundaki çekişmeyi dağıtır; yoğun tempdb üretiminin kaynağı hash spill, sort spill, cursor, snapshot version store veya aşırı #temp kullanımının kendisi olabilir. Gerçek sorgu planında sarı uyarı olarak görünen spill'i doğrulamak için Query Store'da yüksek tempdb_space_used veya execution plan XML içindeki SpillToTempDb bilgisini inceleyin. Test ortamında SET STATISTICS IO, TIME ON; ile aynı parametre için önce-sonra logical read, CPU ve elapsed time değerlerini kaydedin.

Geçici tabloya indeks eklemek her zaman doğru değildir. Örneğin milyon satırlık bir #temp tabloya satır satır insert yapılırken clustered index mevcutsa her insert B-tree bakım maliyeti taşır. Bunun yerine veriyi heap'e yükleyip, filtre veya join anahtarına göre yükleme sonrasında indeks oluşturmayı ölçün. Aşağıdaki desen, CustomerId ile yapılan sonraki join'lerde hash spill'i azaltabilir; karar, gerçek plan ve ölçülen write maliyetiyle verilmelidir.

SELECT CustomerId, OrderDate, TotalAmount
INTO #RecentOrders
FROM dbo.Orders
WHERE OrderDate >= @StartDate;

CREATE CLUSTERED INDEX CX_RecentOrders
ON #RecentOrders (CustomerId, OrderDate);

SELECT c.Region, SUM(r.TotalAmount)
FROM #RecentOrders AS r
JOIN dbo.Customers AS c ON c.CustomerId = r.CustomerId
GROUP BY c.Region;

Database indexleme burada kalıcı tablodaki indeks reçetelerinden farklıdır: #temp nesnesi kısa ömürlü olduğu için create index süresi, sorgu tasarrufundan küçük kalmalıdır. Bir diğer incelik, table variable'ın güncel SQL Server sürümlerindeki iyileştirmelerine rağmen her karmaşık iş yükünde #temp yerine geçmemesidir. Büyük ara sonuçlarda istatistik ve yeniden derleme davranışı plan kalitesini değiştirir. Aynı veri hacmi, aynı parametre dağılımı ve en az 30 tekrar ile iki seçeneğin p50/p95 süresini karşılaştırmadan nesne türünü standartlaştırmayın.

NoSQL eğitimi perspektifiyle version store ve geçici iş yükleri

NoSQL eğitimi alan ekiplerde sık görülen bir varsayım, SQL Server'daki okuma izolasyonunun belgesel veritabanlarındaki sürümleme maliyetiyle aynı şekilde görünmez olduğudur. READ_COMMITTED_SNAPSHOT veya SNAPSHOT isolation altında row version'lar tempdb version store'a yazılır. Uzun süren bir okuyucu, artık iş yükü küçük olsa bile eski sürümlerin temizlenmesini geciktirebilir. Aşağıdaki DMV sorgusu version store alanını veritabanı bazında MB olarak gösterir; bunu aktif snapshot transaction listesiyle birlikte değerlendirin.

SELECT
    DB_NAME(database_id) AS database_name,
    reserved_page_count * 8.0 / 1024 AS version_store_mb
FROM sys.dm_tran_version_store_space_usage
ORDER BY reserved_page_count DESC;

SELECT
    transaction_id,
    elapsed_time_seconds,
    session_id,
    is_snapshot
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;

Uzun snapshot oturumunu bulduğunuzda izolasyonu instance genelinde kapatmak yerine, oturumu üreten rapor veya API akışını düzeltin: sonuç setini stream ederek saatlerce açık transaction tutmayın, pagination için tutarlı ama sınırlı transaction sınırı kullanın ve bağlantı havuzuna transaction açıkken bağlantı iade etmeyin. Bu, veritabanı optimizasyonu çalışmasında kritik bir ayrımdır; data file sayısını artırmak version store'ın büyüme nedenini ortadan kaldırmaz. Sonuçları operasyon ekibiyle paylaşmak için 15 dakikada bir DMV çıktısını tabloya yazıp version_store_mb ile en uzun snapshot süresinin korelasyonunu grafiğe dökün.

Sık Sorulan Sorular

Veritabanı performans ayarlama sırasında PAGELATCH_UP beklemesi nasıl yorumlanır?

PAGELATCH_UP bellek içi bir sayfa latch beklemesidir. sys.dm_os_waiting_tasks içindeki resource_description değerinde tempdb allocation sayfalarını, örneğin 2:1:1 veya 2:1:3, doğrulayın. PAGEIOLATCH veya WRITELOG baskınsa data file eklemek yerine sırasıyla okuma gecikmesini veya log flush yolunu ölçün.

SQL Server eğitiminde tempdb için kaç data file önerilir?

Sabit bir sayı önerisi doğru değildir. Eşit boyut ve eşit FILEGROWTH ile dört data file üzerinden başlayın, temsil' yük altında PAGELATCH delta'sını ve p95 gecikmeyi ölçün. Çekişme sürerse dosya sayısını kademeli artırın. Her eklemeden sonra sys.master_files boyut eşitliğini doğrulayın.

Database indexleme tempdb kullanımını gerçekten azaltır mı?

Doğru seçilmiş bir indeks, join veya sort için gereken bellek miktarını ve spill olasılığını azaltabilir; fakat indeks oluşturma da tempdb ve CPU tüketebilir. #temp tablo için yükleme öncesi ve sonrası CREATE INDEX alternatiflerini SET STATISTICS IO, TIME ON ile en az 30 tekrar ölçün ve gerçek plan üzerindeki SpillToTempDb uyarılarını karşılaştırın.

NoSQL eğitimi alan ekipler SQL Server version store büyümesini nasıl izlemeli?

sys.dm_tran_version_store_space_usage ile veritabanı başına ayrılan alanı, sys.dm_tran_active_snapshot_database_transactions ile en uzun snapshot transaction'ı birlikte toplayın. Uzun yaşayan okuyucu varsa önce o akışın transaction sınırını ve connection pool kullanımını düzeltin; tempdb kapasitesini artırmak sadece taşmayı geciktirir.