SQL Server’da performans açısından belki de en önemli noktalardan biri sistem database’lerinden biri olan TempDB’nin iyi bir şekilde configure edilmesi ve yönetilmesidir. Bugünkü yazımda TempDB ve TempDB best practice’leri üzerine konuşuyor olacağız.

[more]

TempDB Nedir?

TempDB, SQL Server kuruluşu ile beraber gelen 4 sistem database’inden biridir. Her instance için 1 adet olan TempDB’de örnek olarak aşağıdaki bilgiler tutulur:

  • Kullanıcı tarafından create edilen temp table, table variable ya da cursor gibi geçici objeler
  • SQL Server tarafından sort, spool, hash join gibi işlemler için create edilen work table ya da ara sonuçlar gibi internal objeler
  • Read committed snapshot isolation ya da snapshot isolation özelliği kullanıldığında generate edilen row versioning bilgileri
  • Online index operation işlemlerinde tutulan row versioning bilgileri TempDB’de tutulurlar.

Yukarıda da görüldüğü gibi hem kullanıcı hem de internal işlemler için çok fazla durumda kullanılan TempDB bazı durumlarda performans sıkıntısı oluşturabilmektedir. Bu yüzden çok iyi configure ve monitor edilmelidir.

Ayrıca daha önce de belirttiğim gibi tüm instance için sadece 1 adet olan TempDB, konfigurasyonun ve monitoring’in önemini daha da arttırmaktadır.

 

TempDB Best Practice’leri

Bu bölümde TempDB için uyguladığımız aşaıdaki best practice’lerden bahsediyor olacağım.

  • TempDB Data File Sayısı
  • TempDB Data File’larının Aynı Boyutta Olması
  • TempDB File’larının Bulunduğu Storage
  • Yoğun TempDB Kullanımının Analiz Edilmesi
  • T1118 Trace Flag’i

TempDB Data File Sayısı

TempDB üzerinde yapılan allocation ve deallocation işlemlerinde diğer database’lerde de olduğu gibi PFS, GAM ve SGAM page’leri kullanılır. Çok yoğun TempDB kullanılan workload’larda, örneğin sürekli ufak boyutlu temp table’lar create ve drop edildiğinde bu page’ler üzerinde contention yani beklemeler görülebilir. (Örneğin 2:1:3 SGAM page’inde PAGELATCH_XX wait tipi gibi)

Allocation bitmap page’lerinde oluşan bu contention’i azaltmak için 1’den fazla tempdb data file’i kullanılabilir. Bu şekilde page sayısı arttırılacak ve round robin kullanım sayesinde contention azaltılacaktır.

TempDB data file sayısının best practice’i workload’a ve core CPU sayısına göre değişiklik göstermektedir. Bu konu üzerinde internette çok fazla best practice’e rastlayabilirsiniz. Benim kullandığım ise şu şekilde;

  • Core CPU sayısı 8’den az ise core cpu sayısı kadar data file yapmak
  • Core CPU sayısı 8’den fazla ise TempDB data file sayısının 8 yapılması ve wait type’larda PAGELATCH_XX wait type’ının analiz edilmeye devam edilmesidir. Contention görülmeye devam edildiği sürece data file sayısı her defasında 4 arttırılabilir.
“ But there’s now even better guidance, and what I also recommend. At PASS in 2011, my good friend Bob Ward, who’s the top guy in SQL Product Support, espoused a new formula: if you have less than 8 cores, use #files = #cores. If you have more than 8 cores, use 8 files and if you’re seeing in-memory contention, add 4 more files at a time.”
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

Bu noktada akla TempDB data file sayısını her defasında neden 4’er 4’er arttırıp tek seferde 32 ya da 40 yapmadığımız sorusu gelebilir. TempDB data file sayısının fazla olması da performance’a eksi yönde etki eden bir durumdur. Bu yüzden belirli bir sayıdan (8) başlanarak contention görüldüğü sürece (contention analizi için sys.dm_os_wait_stats DMV’si sonucunda çıkan result set’teki PAGELATCH_XX wait type’I gözlenebilir) data file sayısının arttırılması daha iyi bir yaklaşımdır.

Temp DB data file sayısının çok olmasının performans üzerindeki etkisi için aşağıdaki yazıya göz atılabilir;

“So, why is one-to-one not always a good idea? Too many tempdb data files can cause performance problems for another reason. If you have a workload that uses query plan operators that require lots of memory (e.g. sorts), the odds are that there won’t be enough memory on the server to accommodate the operation, and it will spill out to tempdb. If there are too many tempdb data files, then the writing out of the temporarily-spilled data can be really slowed down while the allocation system does round-robin allocation. The same thing can happen with very large temp tables in tempdb too.

Why would round-robin allocation cause things to slow down for memory-spills to tempdb with a large number of files? A couple of possibilities:

Round-robin allocation is per filegroup, and you can only have one filegroup in tempdb. With 16, 32, or more files in tempdb, and very large allocations happening from just a few threads, the extra synchronization and work necessary to do the round-robin allocation (looking at the allocation weightings for each file and deciding whether to allocate or decrement the weighting, plus quite frequently recalculating the weightings for all files – every 8192 allocations) starts to add up and become noticeable. It’s very different from lots of threads doing lots of small allocations. It’s also very different from allocating from a single-file filegroup – which is optimized (obviously) to not do round-robin.

Your tempdb data files are not the same size and so the auto-grow is only growing a single file (the algorithm is unfortunately broken), leading to skewed usage and an I/O hotspot.

Having too many files can lead to essentially random IO patterns when the buffer pool needs to free up space through the lazywriter (tempdb checkpoints don’t flush data pages) for systems with not very large buffer pools but *lots* of tempdb data. If the I/O subsystem can’t handle the load across multiple files, it will start to slow down.”
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

TempDB’ye yeni bir data file ekleme işini hem SSMS hem de TSQL komutu ile yapabilirsiniz. Örneğin aşağıdaki komut TempDB’ye başlangıç boyutu 1024 MB, auto growth değeri 512 MB olan yeni bir data file ekler.

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE 
	( NAME = N'Tempdev2', 
	  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Tempdev2.ndf' , 
	  SIZE = 1048576KB , 
	  FILEGROWTH = 524288KB )
GO

TempDB Data File’larının Aynı Boyutta Olması

Bir önceki bölümde TempDB data file’larının sayısı üzerine konuştuk ve diyelim ki core CPU sayımız 4 ve 4 adet TempDB data file bulundurmaya karar verdik. Şimdi ikinci best practice’imiz yani bu data file’ların aynı boyutta olmasının öneminden bahsedeceğiz.

TempDB’de bulunan data file’lar round robin olarak proportional fill algorithm özelliğini kullanırlar. Bu özelliğin performanslı bir şekilde kullanılabilmesi için tüm data file’ların aynı boyutta olması gerekmektedir. Ayrıca TempDB’ye özel bir problem, tempdb data file’larından eğer herhangi biri diğerlerinden büyükse, auto growth esnasında her zaman büyük olan data file büyütülecektir. Bu durum da proportional fill algorithm özelliğinin sağlıklı bir şekilde kullanılmamasına sebebiyet verecektir.

Aslında burada başka bir best practice’den daha bahsetmek gerekiyor. Bilindiği gibi data file’larda yer bittiğinde eğer auto growth özelliği aktif halde bırakılırsa data file için yeni bir alan otomatik olarak alloce edilecektir. Data file allocation işlemi file seviyesinde exclusive lock’a sebebiyet verdiğinden dolayı auto growth işlemi sırasında file’a erişim engellenecek ve eğer çok fazla auto growth oluşursa performans eksi yönde etkilencektir. Bu yüzden

  • TempDB data file’larına uygun bir başlangıç değeri vermek
  • Auto growth değerlerini daha büyük chunk’lar halinde ayarlamak önemli bir diğer best practice’dir

Bu şekilde TempDB data file’ları -ideal durumda- auto growth’a yakalanmayacak, yakalansa bile büyük chunk’lar halinde büyütüldükleri için sürekli büyüme ihtiyacı duymayacaklardır.

Bu bölümdeki ilk best practice yani TempDB data file’larına uygun bir başlangıç değeri vermek üzerine önemli bir noktanın altını çizmek istiyorum. Bildiğiniz gibi TempDB her SQL Server restart’ında baştan create edilir. Eğer TempDB data file’larının ilk başlangıç değerlerini çok yüksek tutarsanız restart işlemleri sonrası SQL Server’ın hazır hale gelmesi uzun sürecektir. Dolayısıyla uygun bir değerin belirlenmesi önem tşaımaktadır.

Bu bölümdeki best practice’leri toparlayacak olursak. Diyelim ki core CPU sayımız 4 ve 4 TempDB data file’i kullanmaya karar verdik. Ve eğer toplamda 10GB’lık TempDB boyutu bizim için yeterli ise yapmamız gerekenleri şu şekilde sıralayabiliriz.

  • TempDB’ye 3 adet daha data file eklenerek toplam data file sayısı 4’e çıkarılır.
  • Tüm data file’ların başlangıç boyutları 2.5 GB (10 GB/4) olarak ayarlanır.
  • Data file’ların auto growth değerleri büyük chunk’lar halinde auto growth gerçekleşsin diye uygun bir değere (örneğin 512 MB ya da 256 MB) set edilir.

Her ne kadar data file’lara uygun bir başlangıç değeri atansa da plansız ya da problemli bir durumda data file’lar büyüme ihtiyacı duyabilir. Bu durum da data file’lardan biri büyütülecek ve önemli best practice’lerimizden biri olan “TempDB data file’larının eşit boyutta olması” best practice geçersiz duruma gelecektir. Bu problemin önüne geçmek için T1117 trace flag’i kullanılabilir. Bu trace flag aktif hale getirildiğinde bir file group içerisindeki data file’lardan herhangi biri auto growth’a girdiğinde sadece ilgili file değil file group içerisinde bütün data file’lar beraber büyütülecek ve dolayısıyla TempDB data file’larının aynı boyutta olma best practice’i korunmuş olacaktır. Yalnız unutulmaması gereken önemli bir nokta T1117 trace flag’i sadece TempDB’yi değil instance’da bulunan tüm database’leri etkilemektedir.

TempDB data file’larına başlangıç değeri ve auto growth değeri verme işlemlerini SSMS’ten TempDB özelliklerine girerek yapabilirsiniz.

TempDB File’larının Bulunduğu Storage

TempDB’nin yoğun bir şekilde kullanıldığı ortamlarda TempDB performansının overall SQL Server performansını nasıl etkileyeceğinden bahsettik. Bu yüzden TempDB file’larının diğer user database file’larından ayrı bir yerde ve sistemde bulunan en yüksek performanslı disklerde (örneğin SSD disklerde) bulundurulması önemli bir best practice’dir.

TempDB data file’larının nasıl taşınabileceği ile alakalı şu blog post’umu okuyabilirsiniz.

Yoğun TempDB Kullanımının Analiz Edilmesi

Şu ana kadar bahsettiğimiz tüm best practice’ler, TempDB üzerinde yapabileceğimiz konfigurasyon değişiklikleri ile performans artışını sağlamak amacıyla kurgulayabileceğimiz değişiklikler. Aslında belki de bu best practice’lerden daha önce TempDB’nin neden yoğun bir şekilde kullanıldığı ve gerçekten bu kadar yoğun bir şekilde kullanılıp kullanılmamasını analiz etmek olmalıdır. Çünkü çoğu sorguda şunu görüyoruz ki; yoğun temp table’lar kullanılarak yapılan işlemler aslında temp table kullanılmadan da yapılabilmektedir.

Bu yüzden eğer yukarıdaki best practice’leri yapmanıza rağmen hala allocation bit page contention’ları görmeye devam ediyorsanız TempDB’nin neden yoğun bir şekilde kullanıldığının analiz edilmesi ve gerekiyorsa kodda değişiklik yapılarak bu kullanımın azaltılması gerekmektedir. Hatta daha da ideali bu analizi best practice’leri yapmadan önce yapmaktır.

Tekrar belirtiyorum; sadece 1 adet TempDB’miz var ve ona gerçekten iyi bakmalıyız.

T1118 Trace Flag’i

SQL Server’da tüm yeni allocation’larda ilk 8 page’e kadar yapılan page allocation’ları mixed extend’ler üzerinden yapılmaktadır. Obje için alloce edilen alan 8 page’i geçtikten sonra gelen yeni page istekleri artık uniform extend olarak sağlanmaktadır. İlk 8 page için mixed extend’lerden allocation yapılırken her allocation’da PFS, GAM ve SGAM allocation bit page’lerin tamamına bakılır. Bu durumda 8 page’in allocation’i için SGAM page’e 8 defa erişilmektedir ve bu da SGAM (örneğin 2:1:3) üzerinde contention görülmesine sebebiyet verir.

T1118 trace flag’i aktif edildiğinde tüm extend allocation’ları uniform olarak verilir. Bu durumda SGAM üzerindeki contention aşılabilir.

Eğer yukarıdaki best practice’leri uygulamanıza ragmen hala allocation bit page contention’larına rastlıyorsanız T1118 trace flag’ini aktif etmeyi düşünebilirsiniz

ÖZET

TempDB performans açısından SQL Server için oldukça önem taşımaktadır. Instance başına 1 TempDB olduğu için TempDB’nin konfigurasyonu ve yönetimi oldukça önemlidir.

TempDB performansı için base olarak şu best practice’ler uygulanabilir.

  • TempDB kullanımının azaltılması (örneğin gereksiz temp table kullanımının kaldırılması)
  • TempDB file’larının diğer kullanıcı database’lerinden farklı ve en yüksek performanslı disklere konulması.
  • TempDB data file’larının arttırılması
    • Core CPU sayısı 8’den az ise Core CPU sayısı kadar
    • Core CPU sayısı 8’den fazla ise 8 data file oluşturulup contention görülmeye devam edildiği sürece 4’er 4’er data file eklenmesi.
  • TempDB data file’larına uygun bir başlangıç boyutu verilmesi
  • TempDB data file’larının boyutlarının aynı yapılması (Proportional Fill Algorithm)
  • Auto growth değerlerine uygun bir değer verilmesi (Örneğin; 256 MB ya da 512 MB)
  • Gerekirse T1118 trace flag’i aktif hale getirilerek extend allocation’ının uniform extend olarak yapılmasının sağlanması.
Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan