Bu makalede System database’lerinden biri olan Tempdb yapısının nasıl konfigürasyonun yapıldığını ele alacağız. TEMPDB önemli bir system veritabanıdır. İyi bir configürasyon yapılmaz ise performans anlamında çok ciddi sıkıntılara sebebiyet verecektir.
Sql Server kurulum işleminde Database Engine Configuration kısmına geldiğinizde Tempdb sekmesine geçiyoruz ve aşağıdaki gördüğünüz alanlardan tempdb file sayısını, initial size’ını, auto growth’unu data ve log dosyalarının path’lerini ayarlayabiliyoruz. Kurulum yapıldıktan sonra SSMS arayüzünden de yapılabilir.
TempDB, SQL Server’ın geçici nesneler, geçici tablolar, sıralama işlemleri ve diğer geçici işlemler için kullandığı özel bir sistem veritabanıdır. Doğru yapılandırılmadığında performans sorunlarına yol açabilir. İşte TempDB için detaylı yapılandırma önerileri:
1. Dosya Yerleşimi ve Disk Yapılandırması
- TempDB dosyalarını kullanıcı veritabanlarından ve işletim sisteminden ayrı fiziksel disklerde tutun.
- Yüksek IOPS gereksinimleri için SSD veya NVMe diskler tercih edin.
- RAID 0 veya RAID 10 yapılandırması önerilir (performans ve dayanıklılık dengesi için).
- Her TempDB dosyasını farklı fiziksel sürücülere yerleştirin.
Tempdb disklerinin bozulması sonucu sql server servisi çalışır ama sql server ile bağlantı sağlayamayız. Tempdb diskinin yavaş olması sonucu etkilenen işlemler kısacası Stored procedure’ler çalışmaz. Order by group by olan karmaşık sorgular çalışmaz. Geçici tablo olan tüm işlemler çalışmaz. Index rebuild reorganize işlemleri ve dbcc bakım işlemlerinde sıkıntıya sebebiyet verebilir.
2. Dosya Sayısı ve Boyutu
Çekirdek sayısı gözlemlenerek TempDB dosyası oluşturulması gerekmektedir.
Örnek yapılandırma:
8 çekirdekli sunucu: 4-8 TempDB dosyası
16+ çekirdekli sunucu: 8-16 TempDB dosyası
Tüm dosyaların eşit boyutta tutulması gerekmektedir. (SQL Server round-robin algoritması kullanır).
Otomatik büyüme maksimum 512 veya 1024 MB olması gerekmektedir.
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. Ama nadiren 8’den fazlasına gerek olur. Çekirdek sayısı 8 ve üzeri olan sistemlerde 1mdf-7 ndf dosyasının olması gerekmektedir.
3. Bellek Yapılandırması
TempDB genellikle bellek değil disk üzerinde çalışır, ancak yeterli bellek disk I/O işlemlerini azaltarak performansı artırır. Bellek üzerinden işlemler yapılır tempdb üzerinden işlemler yapılmayacağından bahsediyor. Yüksek bellekli sistemlerde Lock Pages in Memory ayarını etkinleştirin. Bu Windows politikası, SQL Server’ın ayrılan belleğin disk üzerine takas edilmesini (page out) engeller.
4. SQL Server Yapılandırma Ayarları
TempDB her zaman SIMPLE kurtarma modelindedir, değiştirilemez. COMPATIBILITY_LEVEL SQL Server sürümüne uygun olmalıdır.
Trace Flags TF 1118 (SQL Server 2016 öncesi): Her tablo için ayrı uniform extent ayırır, tempdb kilitlenmelerini azaltır.
TF 1117: Tüm dosyaların eşit şekilde büyümesini sağlar. SQL Server 2016 ve sonrasında bu özellikler varsayılan olarak etkindir.
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.
Latch contention, SQL Server’ın bellek içi veri yapılarına erişim sırasında oluşan bir performans darboğazıdır. Temelde çok sayıda iş parçacığının (thread) aynı bellek yapılarına eşzamanlı erişmeye çalışmasından kaynaklanır.
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.
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.
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ılması gerekmektedir.
Autogrowth özelligi açık olmalı. Tempdb her otomatik büyüme esnasında “exclusive lock” mekanizmasıyla kilitlenir. Bu da performansı doğrudan kötü etkiler. Veritabanı kurulurken en uygun başlangıç boyutunu ve auto growth boyutunu vermek bu yüzden çok önemlidir.
Aşağıdaki script ile tempdb dosyalarının boyutu, bulunduğu path bilgisi ve growth değerini görebiliriz.
USE tempdb;
GO
SELECT
name AS [FileName],
size * 8 / 1024 AS [CurrentSizeMB], -- Şu anki boyut
growth * 8 / 1024 AS [GrowthIncrementMB], -- Artış miktarı
max_size * 8 / 1024 AS [MaxSize_MB], -- Maksimum boyut
physical_name AS [FilePath]
FROM sys.database_files;Genelde system’de bulunan CPU sayısını göre tempdb file sayımızı oluşturmamız gerektiğini söylerler. Bu tamamen tempdb okuma ve yazma sayısını refarans alarak oluşturulur. Eğer 20 ms üzerindeyse yeni bir tempdb file oluşturulabilir. İlgili makale sayesinde veritabanı üzerinde contention olup olmadığını görebiliriz. Bu makale aracılığıyla tespit edip yeni tempdb dosyalarımız oluşturulabilir. Eğer systemimizde ciddi anlamda contention yaşanıyorsa Task Manager bölümünden tempdb dosyalarımızın okuma yazmamızı görebiliriz.
Aşağıdaki bölümde her bir dosya için ortak bir boyut belirliyoruz. Sağ tarafta bulunan total kısmında ise toplam boyutu gösterilmektedir. Aşağıdaki ekran görüntüsünde data ve log dosya için ayrı konumlar belirtebilir. Gerçek sistemlerde data ve log dosyalarının ayrı path’de olması tavsiye edilir. İlgili disklerde tempdb dışında başka system dosyalarının olmaması gerekmektedir.
Kurulum işlemini tamamladıktan sonra ilgili klasör altında tempdb data ve log dosyalarımızı görmüş oluyoruz.
Veritabanımızın sorgularında PAGELATCH_EX, PAGELATCH_UP, CXPACKET bekleme türü görüp yeni bir tempdb eklemek istersek aşağıdaki yol izlenir.
Kurulum ekranı dışında tempdb veritabanı properties ekranında Files bölümünde Add denilerek tempdb dosyalarımız eklenir. Sql server üzerinde herhangi bir contetion olduğu fark edilirse sayfamızdaki makaleden adımlar izlenerek manuel veya scriptlerle oluşturulur.
İlgili bölümler doldurulup işlemin script’i alınır. Autogrowth bölümü diğer tempdb yapılarıyla aynı yapılmalıdır.
Yukarıdaki ekran resminin çıktısı:
USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp7', FILENAME = N'C:\C1\Tempd\tempdb_mssql_7.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GOİlgili komut çalıştırıldıktan sonra 7. Temp dosyamızda oluşmuş durumdadır.
Not: Gerçek sistemde Tempdb log dosyası tempdb diski altında diğer klasörlerle birlikte bulunmaktadır. Aşağıdaki resim 2 TB ram 192 CPU olan makina üzerinden alınmıştır.
Not: Tempdb başlangıç boyutu maksimum tempdb kullanımın %25-30 kadar uygulanmalıdır. Bu dilim varsayılandır. İyi diskleriniz veya memory konusunda sıkıntı yaşamıyorsan bu boyut artırılabilir. Tempdb dosyalarının autogrowth değeri 512 MB veya 1 GB’lık büyütme adımlarıyla ayarlamamız gerekmektedir.
Not: Gerçek sistem üzerinde tempdb başlangıç olarak initial size değeri tempdb sayısına bağlı 50-100 GB olarak yapılandırılabilir.
Not: Bazen sorarlar tempdb dolduğu zaman kırmızı görünen disk neden sunucu kapanıp açıldığında kaybolur. Çünkü tempdb her açılıp kapanmada yeniden oluşmaktadır. Bu sadece sunucu seviyesinde değil sql servis seviyesinde de gerçekleşir.
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.
Aşağıdaki komut ile tempdb anlık boyutunu görebiliriz.
SELECT
name
,size*8.0/1024/1024 'Current Size in GB'
FROM
tempdb.sys.database_filesAşağıdaki komut yardımıyla tempdb dosyalarının initial size değerini görebiliriz.
SELECT
name
, size*8.0/1024/1024
'Initial Size in GB'
FROM master.sys.sysaltfiles WHERE dbid = 2Bu makalede Sql Server Tempdb konfigürasyonun nasıl yapıldığını ve yeni bir tempdb dosyası eklendiğini görmüş oluyoruz. Başka bir makalede görüşmek dileğiyle..
“Eğer yüz çevirirlerse de ki: “Bana Allah yeter. O’ndan başka hiçbir ilâh yoktur. Ben ancak O’na tevekkül ettim. O, yüce Arş’ın sahibidir.”” Tevbe-129
