Bu makalemizde SQL Server camiasında adından bir o kadar bahsedilmesine rağmen gereken özeni göstermediğimiz hakkında birçok yanlış bilgiye sahip olduğumuz bir veri tabanı üzerine konuşacağız. Başlıktan da anladığınız gibi TEMPDB. Temporary işlerin yapıldığı veri tabanıdır.
Soru: Yeni bir server kurdum tempdb data file ayarlarını nasıl yapmalıyım?
Bu konuyla ilgili net bir rakam ya da boyut verememekteyiz. Ancak tek bir data dosyanız varsa aşağıdaki linke uyabilirsiniz. https://support.microsoft.com/en-us/help/2154845/recommendations-to-reduce-allocation-contention-in-sql-server-tempdb-d 8 logical core ve altında logical core’unuz varsa core sayısı kadar datafile yaratın. Daha fazla logical core’unuz varsa toplam core sayınızı geçmeden data file eklemeye devam edin. Makalemizin konusu da bu aslında bize kaç adet datafile lazım?
Soru: Peki Tempdb Datafile boyutları eşit mi olmalı, nasıl bir storage kullanmalıyım?
Tempdb nin datafileları paralel olarak kullanabilmesi için her datafile boyutunun aynı olması gerekir. Eğer autogrow seçeneğiniz aktif ise tüm datafile’ ların eşit bir şekilde büyüyüp hepsinin aynı boyutta olmasını sağlamalıyız.
2014 ve öncesi için T1117 traceflag’ i tüm datafile’ ların eşit büyümesini sağlar. Sadece tempdb değil. Tüm veri tabanları.
2016 ve sonrasında bu traceflag e ihtiyaç kalmadı. Veri tabanı bazında hatta filegroup bazında autogrow işleminde nasıl davranacağını belirtebiliyoruz. Detay: https://msdn.microsoft.com/en-us/library/bb522469.aspx
Storage olarak SSD kullanabiliyorsak SSD, SSD diskimiz yoksa Raid 1 ya da Raid 10 kullanmalısınız. Raid 5 kullanmayın extra parity diske yazma maliyetinden kaçınmalıyız. Diğer dosyaların olduğu diskten farklı bir disk kullanın, Storage uzmanı arkadaşlardan destek alabilirsiniz. Amacımız hızlı yazıp hızlı okumak. Tüm database log file’ larında olduğu gibi tempdbde de logfile’ a sequential yazmaktadır.
Tempdb datafile’ larınızı önceden reserve edilmiş büyütülmüş olarak konfigure ederseniz extra autogrow maliyetinden kurtulmuş olursunuz. Bu noktada bir performans kazanımı elde edilir. Bu operasyonu sadece tempdb ye değil diğer tüm databaselere de yapmanızı öneririm.
Tempdb allocation contention ı daha fazla data file ekleyerek bizim için çok büyük bir problem olmaktan çıkarabiliriz. Nasıl tespit edeceğiz bizim böyle bir problemimiz olduğunu?
Eğer 2 numaralı bizim tempdb veri tabanımızda PAGELATCH_UP, PAGELATCH_SH ya da PAGELATCH_EX gibi wait eventler görüyorsak şüphelenmeye başlayabiliriz. Adam Mechanic’in sp_whoisactive ya da Brent Ozar’ın sp_BlitzFirst scriptleriyle bu wait typeları görebilirsiniz. Ya da SQL Server dmwlerle tespit edebiliriz. Dipnot: Diğer scriptlerde zaten bu dmwleri kullanmaktadır.
Devam etmeden önce biraz SQL Server storage kavramlarını yeniden bir kez daha hatırlayalım. Sürekli kullanmadığımız için bu ilgiler unutulup gidebiliyor.
Page: SQL Server’ in disk üzerindeki en küçük objesidir. 8KB yer tutar.
Extend: Bir extend 8 page’ den oluşur. 8X8 = 64 KB yer tutar. SQL Server için kullanacağımız disklerin 64K ile formatlamamız buradan gelmektedir. İki tipi vardır. Uniform: Bir objeye aittir, Mixed: Birden fazla objeye ait pageleri barındırır.
Daha detaylı bilgi için SQL Server Storage konusuna bakabilirsiniz.
Eğer waittypelarda PFS,GAM,SGAM gibi data pagelerde wait görüyorsak bizim Tempdb allocation problemimiz var ve bunu çözmemiz gerektiğini anlamalıyız.
Birden fazla sessionın bir alana ihtiyacı olduğunda ve alan kısıtlı olduğunda doğal olarak bir darboğaz oluşur. Bu darboğaz da bize performans kaybı olarak yansır. Processler sıraya girer sürekli boş çalışma alanı ile ilgili bir muhabbet döner. Bu işleme de Contention, anlaşmazlık denir.
Tempdb üzerinde Allocation contention veya system table contention diğer bir deyimle metadata contention typeları görebiliriz. Çok yoğun sistemlerde ne kadar contention varsa hepsini birlikte görürüz genelde J
Allocation contentionın nedeni, sistem yük altındadır ve çalışacak bir temp alana ihtiyac duyuyor olmasıdır. Her bir process kendisi için, temporary bir yer ayırma, bir çalışma alanı yaratmak istemektedir. Bu alanı yaratmaya çalışırken öncelikle ne kadar boş yer var gibi bilgileri PageFreeSpace page’inden öğrenir. PFS page’ini görme sebebimiz bu konuşmadır.
Yoğunluğun çok olduğu paralel çalışmanın hat safhada olduğu bir ortamda sürekli hey ne kadar boş yerin var, nereye yazacağım gibi bir diyalog döner. Konuyu internal memory senkronizasyonu yada bir nevi lock mekanizması gibi de düşünebilirsiniz. Bu yer benim kimse buraya access etmesin ki komple patlamayalım.
Bu noktada aklınıza mixed extend ve uniform extend gelmeli. Uniform extendde blok olarak rezerve ediyordu.
Hadi ekran görüntüleriyle olayı biraz daha anlaşılır kılalım. Bizim bir tane datafile’ımız var. Bir tane de log.
Şimdi bir yük yaratalım ve tempdb de neler olduğuna bakalım. Tempdb ye concurrent 12 insert isteği yapalım. Tablo şemasını ve stored procedureleri ek olarak aşağıda paylaşacağım
Gördüğünüz gibi kaynak bekliyor. Bu görüntüye görüyorsanız ve latch eventleriniz SH,UP veya Ex ise sisteminizde tempdb allocation contention performans problemi var demektir. Bu trafiği engelleyip processlerin çalışmasına izin vermek için daha fazla datafile ekleyelim. Ekleyelim ki kimse kavga etmesin. 
Allocation contentionı engellemek için yaratmış olduğunuz tüm data fileların aynı boyutta olması çok önemli. İlk satırda var olan tempdb datafile boyutunu diğerleriyle aynı olması için modify ediyorum. Tüm data fileların aynı boyutta olduklarından emin oluyoruz.
Testimizi tekrar çalıştıralım.
Sürelerin ne kadar düştüğünü görüyoruz. Bu testleri gücünün yettiği ölçüde kendi laptopumda yaptım. Sunucularda bu düşüş çok daha belirgin olacaktır. Artık çok daha hızlı insert yapıyoruz işlerimiz çok daha hızlı çıkıyor.
Artık eskisi kadar yoğun latch eventide görünmüyor. Bu ekran görüntüsü sizi yanıltmasın her zaman allocation event görürüz ancak onlarca yüzlerce görmeyiz. Gördükten sonra hemen kaybolur. Benchmark yapmak için çalıştırdığım proje açık kaynak kodlu bir projedir. Üzerinde sadece DB exec time ve AVG time ı göstermek için çok ufak tefek değişiklikler yaptım. Bu sayede yapmış olduğum değişikliğin bana faydası mı zararı mı var görmüş oluyorum. Buradan indirebilirsiniz. https://github.com/cankaya07/SQL-Load-Generator
Bu makaleyi SQL Server 2016 engine üzerinde testler yaparak yazdım. Ancak daha alt bir sürümde çalışıyorsanız SQL Server 2014 veya daha aşağısı Uniform extend i kullanabilmek için T1118 Traceflagini sunucu seviyesinde açmanız gerekir. Bu traceflagi açarak SQL Server’a uniform extend kullanmak istediğimizi bildirmiş oluyoruz.
Bu şekilde bu traceflag i enable edebilirsiniz. Bu konfigürasyonun geçerli olabilmesi için SQL Serverınızı yeniden başlatmanız gerekli. Ve tüm datafilelar için geçerli olacaktır bu ayar.
Soru: SQL Server 2016 kullanılıyorum. 1118 Trace flag’e ihtiyacım var mı ?
SQL Server 2016 ve üzerinde bir versiyonla çalışıyorsanız bu traceflag’e ihtiyacınız yok.
Eski sistemlerde bunu T1118 traceflag i ile yaptığımızı söylemiştik. SQL Server 2016 ve üzeri sistemlerde ise mixed allocationı kontrolünü aşağıda ekran görüntüsünü attığım dmw ile yapabilirsiniz Veri tabanı bazında değişiklik yapabilirsiniz.
SQL Server 2016 da veri tabanı bazında uniform ya da mixed page allocation set edebiliyoruz. Değiştirmek istersek,
ALTER DATABASE <dbname> SET MIXED_PAGE_ALLOCATION { ON | OFF }
Detaylı bilgi için https://msdn.microsoft.com/en-US/library/bb522682.aspx
SQL Server 2016 ve daha yeni bir engine kullanıyorsanız tempdb bakımından kutudan çıkar çıkmaz diğer mühendislere göre daha hızlı ve daha performanslı çalıştığını söyleyebiliriz. Eski sürümlerin kararlılığını yeni sürümlerin vizyoner ve teknolojik gelişmelerini göz önüne almakta fayda var.
Umarım verimli bir makale olmuştur ve size yararı dokunur.
Not: “tempd_allocation_contention” SQL Komutuna aşağıdaki link üzerinden ulaşabilirsiniz
http://www.cozumpark.com/files/folders/yuklemeler/entry535075.aspx
Bir sonraki makalede görüşmek üzere.