MSSQL Server Trace Flag- Startup Parametreleri

Bu makalede Sql server Trace Flag-Startup Parameter’ın ne işe yaradığını nasıl eklendiğini veya silindiğini ele almış olacağız. Sql Server Startup Parameters ile sql server servisinin  başlangıcında sunucu ve instance bazlı bir yapılandırma seçeneğidir. Bir sıkıntı anında bu parametreleri kullanarak performans ve güvenlik anlamında iyi sonuçlar elde ederiz.

SQL Server Kurulumu sırasında, sql server default startup parametrelerini windows Registry’ye yazar. Bu parametreleri ekledikten sonra çalışması için servisin Restart edilmesi gerekmektedir.

Run kısmına Regedit dedikten sonra  gelen ekranda aşağıdaki uzantıya gidilince  Trag flag’lar görülmektedir.

“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.TEST23\MSSQLServer\Parameters”

Sql Server Startup parameters bölümüne ulaşmak için  Windows arama kısmına SQL Server Configuration Manager ekranına girilir.

Gelen ekranda SQL Server Services sekmesinden ilgili instance’a çift tıklanır veya sağ tıklanıp properties ekranına girilir.

Gelen ekranda Startup Parameters kısmına girilir. Burada sql server için faydalı olan parametreleri ekleyebiliriz. Eklenen her parametreden sonra sql servisi Restart edilip devreye alınır.

İlgili parameters eklerken Specify a startup parameter kısmına ilgili Trage flag yazılır ve Add tuşunu basılır aktif edilmesi için servisin Restart olması gerekmektedir.

Restart ettikten sonra parametremiz eklenmiş oldu.

Tekrardan eklemiş olduğumuz parametreyi silmek istersek ilgili parametreyi tıklayıp remove dememiz yeterli oluyor.

Aşağıda bulunan parametreler bulunmuş olduğum ortamda gerçek sistemden alınmış bir bölüm buradaki parametrelerin ne işe yaradığını öğrenip bir iki ek bilgiyle konuyu sonlandırmayı düşünüyorum. Gerçek sistemde eklenmesi gereken parametreleri görünce makaleyi her geçen gün güncellemeyi planlamaktayım.

Bu parametrelerin ne iş yaptığına değinelim.

• -d: Master veritabanının mdf dosyasının path’inin bilgisini içerir.
• -e: ErrorLog’un path bilgisini içermektedir.
• -l: Master veritabanının ldf dosyasının path’inin bilgisini içerir.

Yukarıdaki 3 parametre default olarak her instance altında mevcuttur.

Trace Flag’ları DBCC TRACEON ve DBCC TRACEOFF komutları kullanılarak etkinleştirilebilir veya devre dışı bırakılabilir. 

-T: SQL Server’ın -T’den sonra belirtilen Trace flag’ları kullanıma alır.

-T174: Bucket count limitini 40 binden 160 bine çekmeye yarar. Varsayılan bucket count değeri 40001’dir. Normal değerde toplam cache üzerinde bulanabilecek max. plan sayısı 160000’dir. T174 trace flag ile bucket count sayısı 160001, max plan cache sayısı 640000’e çıkarılır.

SQL Server, sorguların derlenmiş halini (query planları) Plan Cache içinde saklar. Bu sayede aynı sorgular tekrar çalıştırıldığında performans kazanılır çünkü derleme süreci atlanır.

• Plan cache, bir hash tablosu gibi çalışır.
• Her plan, bir hash değeriyle bir bucket’a yerleştirilir.
• Bu yüzden “bucket count” kavramı önemlidir: Bu, planların dağıtılacağı toplam slot sayısıdır.
• Bucket sayısı arttıkça, planların hash çakışması (collision) riski düşer ve planlar daha uzun süre cache’de kalabilir.

• -T174, bucket count sayısını artırır.
• Varsayılan olarak bucket count genellikle 40001’dir.
• Bu trace flag ile bucket count sayısı 160001’e çıkarılabilir.
• Bu durum, plan cache kapasitesini artırır, yani daha fazla sorgu planı daha uzun süre bellekte tutulabilir.

Hesaplama:

SQL Server plan cache mekanizmasında her bucket’ta ortalama 4 plan olabilir.

Yani:

• Varsayılan (40001 bucket x 4) ≈ 160,000 plan
• TF 174 (160001 bucket x 4) ≈ 640,000 plan

TF 174, memory kullanımı artışına neden olabilir. Daha fazla plan daha uzun süre bellekte tutulur. Plan cache’in büyümesi, özellikle çok sayıda farklı dinamik sorgunun olduğu sistemlerde bloat (şişme) yaratabilir. Eğer ad-hoc workload çok fazlaysa, bu durumda faydadan çok zarar verebilir. (Örneğin tek seferlik çalıştırılan yüz binlerce farklı sorgu varsa.)

select name, type, buckets_count
from sys.dm_os_memory_cache_hash_tables
where name IN ( 'SQL Plans' , 'Object Plans' , 'Bound Trees' );
select name, type, pages_kb, entries_count
from sys.dm_os_memory_cache_counters
where name IN ( 'SQL Plans' , 'Object Plans' ,  'Bound Trees' );

CACHE bulunan sql plans  ekran resmi sürekli değişmektedir.

Bucket count yapısına daha detaylı değinmek gerekirse, MS SQL Server’da bucket count, bir hash algoritmasına dayalı yapılar (örneğin, hash index ya da hash dağıtımı) ile ilişkili bir terimdir. Genellikle memory-optimized tablolar (Bellek içi optimize edilmiş tablolar) veya hash index’leri oluştururken karşılaşılır. Bucket terimi, bir hash tablosunda belirli bir hash değerine karşılık gelen bir bölümü ifade eder. Her bir bucket, o hash değeriyle eşleşen veri satırlarını saklar. Bucket Count, hash tablosunda kaç adet bucket bulunduğunu belirtir. Yani bu, tablonun ya da index’in hash dağılımının ne kadar ince ya da geniş olacağını belirleyen bir parametredir. Hash index oluşturulurken BUCKET_COUNT değeri, veri dağılımını optimize etmek için kullanılır. Bucket sayısı, index’e eklenecek veri miktarına göre belirlenir. Eğer bucket sayısı düşük seçilirse, hash çakışmaları (collisions) artabilir ve performans düşebilir. Eğer bucket sayısı gereksiz yere yüksek seçilirse, bellekte gereksiz yer tüketimi olabilir. Bucket sayısı, satır sayısından biraz büyük olmalı (örneğin, satır sayısının 1.5 katı)Bucket count sayısı manuel olarak eklenmesi gerekir. default bir değeri yoktur. Özetle: Hash Tablo, Verileri kovalar içinde organize eden bir yapıdır. Hash Index SQL Server gibi veritabanlarında, veriye hash algoritmasıyla hızlı erişim sağlamak için kullanılan bir indeks türüdür. Özellikle eşitlik bazlı sorgularda çok etkilidir.

Başka bir kaynaktan ise aşağıdaki açıklamayı görmekteyiz.

Hash tablo, verileri hızlı bir şekilde bulmak için kullanılan bir veri yapısıdır. Veriler, bir hash algoritması ile bir anahtar (key)kullanılarak bir bölüme (bucket/kova) atanır. Amaç: Bir veriyi hızlı bir şekilde aramak, bulmak ya da eklemektir. Nasıl Çalışır?1. Bir anahtar (örneğin, bir “ID”) kullanılarak bir hash değeri oluşturulur. 2. Hash değeri, belirli bir bucket (kova) numarasını gösterir. 3. Veri bu kovada saklanır. 4. Daha sonra aynı anahtarla bir veri arandığında, hash değeri hesaplanarak verinin bulunduğu kova doğrudan bulunur. Avantajı: Veriye çok hızlı ulaşılır (genelde O(1) zaman karmaşıklığı). Bir kütüphanede kitapları bir “kategori numarasına” göre bölümlere ayırmak gibi düşünün. Kategori numarası bir hash değeri gibi çalışır ve kitap doğrudan bu numaraya göre bulunur. Hash index, bir veritabanında belirli bir sütuna hızlı erişim sağlamak için kullanılan bir indeks türüdür. Veritabanı, bir sütundaki her değere bir hash değeri atar ve bu değerlere göre veriyi kovalar içine yerleştirir. Nasıl Çalışır? 1. Bir tablo oluşturulurken ya da index eklenirken, belirli bir sütuna hash index tanımlanır. 2. Veri eklendiğinde, hash fonksiyonu kullanılarak veriler bir kovaya atanır. 3. Sorgu sırasında, hash fonksiyonu sayesinde veri doğrudan kovadan alınır. Avantajı: Özellikle eşitlik tabanlı sorgular (örneğin, WHERE ID = 123) için çok hızlıdır. Kısıtlamalar: Aralık tabanlı sorgular(örneğin, WHERE ID > 100) hash index ile çalışmaz. – Kova sayısı (bucket count) iyi ayarlanmazsa performans düşebilir.

Not:Bucket sayısını çok yüksek seçmek belleği israf edebilir, çok düşük seçmek ise hash çakışmalarını artırarak performansı düşürebilir. Bu nedenle, bu değeri dikkatle ayarlamak gerekir.

-T1117: Filegroup içerisindeki tüm data fileların aynı oranda büyümesini sağlar. Sql 2019 ile birlikte default olarak getirilmiştir. Tempdb dosyalarınında varsayılan olarak aynı oranda büyümesini sağlar. 1117 numarası Trace Flag numarasını temsil ederken -1 parametresi ise yapacağımız işlemin sadece o bağlantıya ait değil de global olarak yapılacağı anlamına gelir. Sadece 1117 yazılması session bazlı olmaktadır.

DBCC TRACEON(1117) -- session bazlı
DBCC TRACEON(1117,-1) -- instance bazlı

Trace Flag’ı pasif etmek için kullanılan komut ise session bazlı ve global olarak gelmektedir.

DBCC TRACEOFF(1117) -- session bazlı
DBCC TRACEOFF(1117,-1) -- instance bazlı

Trace Flag’ı durumunu öğrenmek  için kullanılan komut ise session bazlı ve global olarak gelmektedir

DBCC TRACESTATUS(1117) -- session bazlı
DBCC TRACESTATUS(-1)   -- instance bazlı

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.

-T1118: Diskten veriyi mixed extent şeklinde değil de uniform extent şeklinde çekmesi sağlanmaktadır. Tempdb performansına olumlu etki yapmaktadır.

-T3459: Bu paremetre secondary sunucusundaki DIRTY_PAGE_TABLE_LOCK, REDO_THREAD_PENDING_WORK, PARALLEL_REDO_DRAIN_WORKER, PARALLEL_REDO_FLOW_CONTROL gibi bekleme türlerine sebebiyet vermektedir. Secondary sunucusunda parelelizmin önüne geçmek için bu parametre kullanılır. Gerçek sistem üzerinde bu özellik açılır.

-T7752 ve -T7745: Bu iki trace flag query store açıldığında secondary veritabanında eklenmesi gerekir. Herhangi bir failover durumunda query store daki verilerin restoresinin önüne geçer.

-T8048: Numa bazlı bölünmüş memory objelerini CPU bazlı bölünmüş hale getirir. Yüksek miktarda oluşan CPU kullanımını düşürmektedir.

-T8780: SQL Server sorgu iyileştiricisi birden çok sorgu planı adayı oluşturur ve ardından en düşük maliyete sahip sorgu planını seçer.

-T1236: Veritabanındaki lock kaynaklı performans problemlerini çözer.

-T9024: Log write waits’in artmasını önler.

–T2371 : İndex yeniden oluşturması sırasında, önceki index’in istatistiklerinin korunmasını sağlar. 

–T3226 : Her başarılı yedekleme sql server error log’a ve sistem günlüğüne log kayıdı atar. Gün içinde birden fazla yedekleme mesajının loglara düşmesi, bulmaya çalıştığımız bir ifadenin de aramasını zorlaştırır. Bu Trace Flag ile backup log girdilerinin gelmemesini sağlarız. Bunun için belirtilen T3226 trace flag’i kullanılır.

-f: Bu paremetre sql server servisinin minumum parametreyle çalışmasını sağlar. Bu parametre aracılığıyla bazı başlangıç konfigürasyonları ayarlayabilirsiniz. Servis single user modda çalışmaktadır. Sql server servisi memory yetersizliğinden dolayı açılmıyorsa bu parametre kullanarak düzenleme yapılabilir. İlgili parametre yazıldıktan sonra sevisi başlatmadan önce SQL Server Agent Servisinin kapalı olduğundan emin olun. Çünkü -f parametresiyle sql server hem minimum konfigürasyonda hem de single user mode’da çalışacaktır. Yani aynı anda sadece tek kullanıcı sql server’a bağlanabilir. SQL Server Agent Servis açık olursa o bağlanmaya çalıştığı için siz bağlanamayabilirsiniz. Ben genellikle böyle sorunları DAC ile çözüyorum.

-m: Sql Server maintance bakım modudur. Bu modu kullanırsak sql server servisi single user mod da  yani tek seferde sadece tek bir kullanıcı ile çalışır. -f parametresiyle sınırlı sayıda müdahale yapabilirken -m parametresiyle tüm bakım işlemleri yapılmaktadır. Çünkü bu mod’la giren kullanıcı sysadmin hakkına sahip olur. İlk gelen kullanıcı bu hakkı alır.

-E: Filegroup’taki her file için tahsis edilmiş extent sayısını artırır. OLTP sistemlerde kullanılmamalıdır.

-c: SQL Server cmd komut satırından başlatıldığında servisin başlama süresini kısaltır. Normalde SQL Server başlatıldığında Servis olarak çalışır. Bu startup parametresini ekleyerek servis olarak başlamayacağını set etmiş oluyor. Bu yüzden başlatılma süreside kısalıyor.

Aktif olan Trace Flag’ları görmek için aşağıdaki komut kullanılmaktadır.

DBCC TRACESTATUS(-1) 

Bu makalede sql serverda kullanılan  Trage flag startup parametrelerini görmüş oluyoruz. Ben genellikle canlı sistemde kullanılan paremetreleri ve kullanılabilme ihtimalleri olan parametreleri görmüş olduk.

Bir sonraki makalede görüşmek dileğiyle.

“Sûra üfürülür. Bir de bakarsın kabirlerden çıkmış Rablerine doğru akın akın gitmektedirler. Şöyle derler: “Vay başımıza gelene! Kim bizi diriltip mezarımızdan çıkardı? Bu, Rahman’ın vaad ettiği şeydir. Peygamberler doğru söylemişler.”Sadece korkunç bir ses olur. Bir de bakarsın hepsi birden toplanıp huzurumuza çıkarılmışlardır.” Yasin 51-53