TempDb Veritabanının Konfigürasyonu

TempDb Veritabanı Dosya İşlemleri

Bugün sizlere SQL Server sistem veritabanlarından birisi olan ve geçici işlemler için kullanılan TempDb  veritabanından , dosya yapısının nasıl konfigüre edilmesi gerektiğinden ve nasıl taşıyacağımızdan bahsetmek istiyorum. TempDb her instance yeniden başlatıldığında yeniden başlatılır ve geçici işlemler için kullanılır.

 

Sistem  veritabanlarından olan ve her instance için bir tane olan TempDb'de aşağıdaki veriler tutulur.

• Cursor, Temp Table , Join , Aggregation ve INSTEAD OF Trigger gibi objeler
• Read Commited Snapshot Isolation ,Snapshot Isolation ve Online Index  işleminde tutulan row version bilgileri
• Sorting, Hash Matches ve spools işlemleri için oluşturulan tablolar
• XML variables ve LOB kullanımında oluşturulan sorgular

TempDb veritabanı yukarıdada bahsettiğim gibi  geçici işlemler için kullanılır.   Bu nedenle çok yoğun geçici işlem kullanan Axapta, Sap gibi uygulamaların kullanmış olduğu SQL Server databaselerde yüksek performans sağlayabilmek için  TempDb  database yapısının  çok iyi yapılandırılması gerekmektedir.

Bunun için yapılacak işlemleri sıralayacak olursak 3-4 adımda tempdb veritabanından maksimum performans sağlayabiliriz.

Birinci adımda disk bant genişliğini maksimum seviyede kullanabilmek için tempdb dosyası birden fazla oluşturulmalıdır. Bu sayede tempdb veritabanında yapılacak işlemlerin paralel olarak yapılması sağlanır ve iş yükü önemli bir ölçüde ölçeklendirilebilir. Ancak çok fazla sayıda oluşturmak ise yönetim yükünü artırır ve performansda düşmeye sebeb olur. 

 

Bunun için genel kural tempdb veritabanı dosyaları oluşturulurken her CPU için bir dosya olarak yapılandırılmalı ve dosya adeti sekizi geçmemelidir.(Dual Core işlemciler iki CPU olarak kabul edilir.) (CPU sayısı sekizden fazla ise TempDB performans sorunu devam ediyorsa 4'er  4'er artırılabilir.)

Oluşturulan dosya boyutları bütün hepsinde aynı boyutta olmalıdır. Bu sayede dosya doluluk oranında optimizasyon sağlanmış olur.

 

Oluşturma esnasında datafile boyutları uygun olarak verilmeli ve autogrowth oranı çok iyi ayarlanmalıdır. Çok büyük dosya boyutu verildiğinde ise restart durumunda TempDb veritabanı yeniden oluşturulacağı için SQL Server'ın hazır duruma geçmesi esnasında biraz beklemek zorunda kalabiliriz.

Çok küçük verilir ise dosya boyutu devamlı büyüme gereksinimi duyar. Bu  işlem dosya seviyesinde exclusive lock’a neden olur buda auto growth işlemi sırasında file’a erişim engellenir ve eğer çok fazla auto growth oluşursa performans olumsuz etkilenir.

Bunun için genel uygulanan yöntem

tempdb file size	FILEGROWTH increment
0 to 100 MB	10 MB
100 to 200 MB	20 MB
200 MB or more	10%*

İkinci adımda ise dosyanın koyulacağı disk yapısı seçimi. Diskler yüksek oranda I/O yapacağı için hızlı bir I/O sistemi üzerine konulması tavsiye edilir. Bu disklerin yapılandırması stripping olması önerilmektedir. Ayrıca Tempdb  veritabanı diğer kullanıcı veritabanlarından farklı alanlar üzerine konulmalıdır ve tempdb data dosyaları ve log dosyalarıda bir birinden farklı disklere konulmalıdır. Bu sayede yüksek I/O elde ederek maksimum performans elde etmiş oluruz.

 

Üçüncü adımda ise 1117 numaralı trace flag aktif edilmelidir.  Bu sayede veritabanımızdaki tüm dosyaların büyüme oranları sabitlenmiş olur. Örneğin 400 MB lık 8 tane dosyamız var. Her birinin otomatik büyüme değerleri 100MBlık olarak verilmiş. Eğer flag kapalı ise TempDb dolduğunda sadece bir tanesi 100 MB büyür. Bu dosya tamamen dolmadan diğerlerine veri yazılmazki buda ister istemez performans kaybına yol açar.

Bu işlem için  aşağıdaki T-SQL komutu kullanılabilir.  1117 numarası Trace Flag numarasını temsil ederken -1 parametresi ise yapacağımız işlemin sadece o bağlantıya ait değilde global olarak yapılacağı anlamına gelir.

DBCC

TRACEON(1117,-1)

Trace Flag global olarak aktifleştirildiği için sadece TempDb değil diğer birden fazla dosyaya sahip veritabanlarıda otomatik olarak büyür. Bunuda DBA arkadaşların göz önünde bulundurması gerekir.

Dördüncü adımda ise TempDb'nin yoğun kullanılmasının anlaşılması ve yeniden yapılandırılması gerekirse query tarafında düzeltilmeye gidilmesi gerekmektedir.

TempDb Dosya Boyutlarını ve Büyüme Parametrelerini Görmek

TempDb dosya boyutlarını ve otomatik büyüme parametrelerini görmek için aşağıdaki T-SQL kodunu kullanabiliriz veya Sql Server Management Studio kullanabiliriz.

SELECT     name AS FileName,
size*1.0/128 AS FileSizeinMB,
CASE max_size         WHEN 0 THEN 'Autogrowth is off.'
WHEN -1 THEN 'Autogrowth is on.'       
ELSE 'Log file will grow to a maximum size of 2 TB.'   
END,    growth AS 'GrowthValue',    'GrowthIncrement' =        
CASE            WHEN growth = 0 THEN 'Size is fixed and will not grow.'          
 WHEN growth > 0 AND is_percent_growth = 0                
 THEN 'Growth value is in 8-KB pages.'           
 ELSE 'Growth value is a percentage.'      
 END
FROM tempdb.sys.database_files;

TempDb Veritabanının Taşınması

TempDb veritabanını diğer veritabanlarını taşıdığımız yöntemlerle taşıyamayız. TempDb veritabanını taşımak için aşağıdaki scripti kullanabiliriz. Aşağıdaki kodu uyguladıktan SQL Server instance yeniden başlatılınca yeni TempDb ayarları geçerli olacaktır.

USE master
GO

ALTER DATABASE tempdb   MODIFY FILE (NAME = tempdev, FILENAME = 'C:\SQLTEMP\tempdb.mdf')
GO

ALTER DATABASE tempdb   MODIFY FILE (NAME = tempdev2, FILENAME = 'C:\SQLTEMP\tempdb1.ndf')
GO

ALTER DATABASE tempdb   MODIFY FILE (NAME = tempdev3, FILENAME = 'C:\SQLTEMP\tempdb2.ndf')
GO

ALTER DATABASE tempdb   MODIFY FILE (NAME = tempdev4, FILENAME = 'C:\SQLTEMP\tmpdb3.ndf')
GO

ALTER DATABASE tempdb   MODIFY FILE (NAME = tempdev5, FILENAME = 'C:\SQLTEMP\tempdb4.ndf')
GO

ALTER DATABASE tempdb   MODIFY FILE (NAME = tempdev6, FILENAME = 'C:\SQLTEMP\tempdb5.ndf')
GO

ALTER DATABASE tempdb   MODIFY FILE (NAME = tempdev7, FILENAME = 'C:\SQLTEMP\tempdb6.ndf')
GO

ALTER DATABASE tempdb   MODIFY FILE (NAME = tempdev8, FILENAME = 'C:\SQLTEMP\tempdb7.ndf')
GO

ALTER DATABASE tempdb   MODIFY FILE (NAME = templog, FILENAME = 'C:\SQLTEMP\templog.ldf') 

TempDB VeriTabanında Neler Yapılamaz?

• TempDb Veritabanı asla silinmemelidir.
• Backup alınmamalıdır.
• Simple olan Recovery Modu asla değiştirmemelidir.
• Birden fazla FileGroup oluşturulmamalı sadece Primary File Group kullanılmalıdır.
• Database Owner olan SA kullanıcısı değiştirilmemelidir.
• TempDB databasenin Snapshotu alınamaz
• DBCC CHECKALLOC T-SQL komutu çalıştırılamaz.
•  DBCC CHECKCATALOG T-SQL komutu çalıştırılamaz.
• TempDB veritabanı Offline yapılamaz.