SSMS (SQL Server Management Studio) içerisinde yer alan Database Engine Tuning Advisor (DTA), veritabanı yöneticileri ve geliştiriciler için hayat kurtaran, yapay zeka benzeri bir analiz mekanizmasıdır. SQL Server’ın performansını optimize etmek için kullanılan bu araç, sorguların nasıl daha hızlı çalışabileceğine dair somut ve uygulanabilir reçeteler sunar.
Veritabanları büyüdükçe ve sorgu trafiği karmaşıklaştıkça, hangi tabloda hangi indeksin eksik olduğunu veya hangi sorgunun neden yavaş çalıştığını manuel olarak tespit etmek zorlaşır. Database Engine Tuning Advisor, SQL Server sorgu iyileştirici (Query Optimizer) motorunu kullanarak veritabanı yapısını analiz eder. Temel amacı, sistem kaynaklarını en az seviyede tüketerek sorgu sonuçlarını en kısa sürede getirecek yapılandırmayı (indeksler, bölümler vb.) belirlemektir.
SSMS arayüzünde bulunan Tools bölümünde Database Engine Tuning Advisor yapısına tıklanır.
Windows arama bölümünden de ilgili yapıya girilebilir.
Gelen ekranda bağlantı sağlanacak üzerinde işlem yapacağımız sunucu seçimi yapılmaktadır.
Bağlantı sağlandıktan sonra ilgili ekran karşımıza çıkmaktadır. Şimdi bu kavramların ne işe yaradığını detaylı bir şekilde ele alalım.
Aşağıdaki ekran görüntüsünde gördüğün o 4 ana seçenek, DTA’nın “yakıtını” nereden alacağını belirler:
Şimdi bu kavramları detaylı bir şekilde ele alalım:
A. File
Bu ifade bir Profiler Trace (.trc) veya bir SQL Script (.sql) dosyasını seçmek istediğimizde kullanılmaktadır.
- Ne zaman kullanılır? Belirli bir zaman dilimindeki trafiği veya kendi yazdığın özel bir sorgu setini analiz etmek istediğinde kullanılmaktadır.
B. Table
Profiler kaydını bir dosyaya değil de, SQL Server içinde bir tabloya kaydettiysen bu seçeneği kullanırsın.
- Avantajı: Dosya boyutuyla uğraşmazsın, SQL üzerinde saklandığı için yönetimi kolaydır.
C. Plan Cache
SQL Server, çalıştırdığı sorguların planlarını hafızasında tutar. Bu seçeneği seçersen, DTA o an sunucunun belleğinde (RAM) duran ve en çok kaynak tüketen sorguları otomatik olarak çeker.
- Avantajı: Dışarıdan dosya yüklemene veya Profiler açmana gerek kalmaz. Mevcut canlı sistemin durumunu analiz eder.
D. Query Store
Eğer veritabanında Query Store özelliği aktifse (SQL 2016 ve sonrası), bu seçenek en modern ve sağlıklı yöntemdir. Query Store, veritabanı performans geçmişini sürekli kaydeder.
- Avantajı: Sadece anlık değil, geçmişe dönük (örneğin son 24 saatteki) en ağır sorguları otomatik olarak analiz kapsamına alır.
Yukarıdaki bölümün en sağında bulunan dürbün simgesine tıklayıp Trace dosyasını seçtikten sonra, hemen altındaki “Database for workload analysis” kısmından trace dosyasının olduğu veritabanının seçilmesi gerekmektedir. Bu işlem yukarıda table bölümünü seçtikten sonra trace tablomuzu seçiyoruz.
Select databases and tables to tune kısmında ise seçtiğimiz database veya altında seçilen tablo için seçtiğimiz trace tablosunda ilgili ifadelerin olup olmadığına bakmaktadır. Eğer sadece tek bir tabloyu optimize etmek istiyorsan, Trace dosyanın içinde sadece o tabloya giden sorguların olduğundan emin olunmalıdır. Eğer Trace boşsa veya o tabloya dair sorgu içermiyorsa DTA hata verebilir veya “Öneri yok” diyebilir.
DTA, elindeki Trace (izleme) dosyasını okurken içinde onlarca farklı veritabanına ait sorgular bulabilir. Bu listede bir veritabanını işaretlediğinde, DTA’ya şunu demiş olursun: “Trace dosyasının içindeki binlerce satırdan sadece bu veritabanı ile ilgili olanları ayıkla ve analiz et.” Eğer Trace dosyasında X veritabanına ait sorgular varsa ama sen bu listede sadece Y veritabanını seçtiysen, DTA analiz edecek veri bulamaz ve boş sonuç döndürür.
Aşağıdaki yapı seçilmiş olan ProfileTraceTable23 trace kaydında seçilen veritabanı tablosunun olup olmadığının sonucunu vermektedir.
Yukarıdaki General sekmesinde açıklamalar yaptıktan sonra Tuning Options kısmında ilgili incelemeler sonucu nelerin rapor olarak sunulmasını istiyorsak belirtiyoruz.
Bölüm bölüm inceleyelim:
1. Limit tuning time (Analiz Süresini Sınırla)
Bu bölüm, DTA’nın analiz için ne kadar süre harcayacağını belirler.
- Çok büyük iş yüklerini (Workload) analiz etmek saatler, hatta günler sürebilir.
- Stop at: Belirlediğin tarih ve saatte analiz henüz bitmemiş olsa bile DTA durur ve o ana kadar bulduğu en iyi önerileri sunar. Eğer sistemin çok yoğunsa veya kısa sürede sonuç almak istiyorsan burayı kısıtlayabilirsin.
2. Physical Design Structures (PDS) to use in database
DTA’nın hangi veritabanı nesnelerini oluşturabileceğini veya değiştirebileceğini seçtiğin yerdir.
- Indexes: Sadece standart indeksler (Clustered/Non-clustered) önerir. Genelde en çok kullanılan seçenektir.
- Indexes and indexed views: İndekslere ek olarak, karmaşık sorguları hızlandırmak için “Indexed View” (fiziksel olarak saklanan görünümler) oluşturulmasını da önerir.
- Nonclustered indexes: Mevcut Clustered indekslere dokunmaz, sadece ek Non-clustered indeksler önerir.
- Evaluate utilization of existing PDS only: Yeni bir şey önermez, sadece mevcut indekslerinin ne kadar verimli kullanıldığını analiz eder. Kullanılmayan indeksleri bulmak için harikadır.
- Include filtered indexes: Belirli bir WHERE koşuluna göre çalışan “filtreli indeksler” önerir. (Örneğin: Sadece Durum = 1 olan satırları indeksle gibi).
3. Partitioning strategy to employ (Bölümleme Stratejisi)
Büyük tabloların (TB seviyesindeki veriler gibi) fiziksel olarak parçalara bölünmesiyle ilgilidir.
- No partitioning: Herhangi bir tablo bölleme önerisi yapmaz.
- Full partitioning: Performansı artırmak için tabloların nasıl bölümlenmesi (Partitioning) gerektiğini hesaplar.
- Aligned partitioning: Yeni oluşturulacak indekslerin, tablonun mevcut bölümlendirme yapısıyla uyumlu (hizalanmış) olmasını sağlar.
4. Physical Design Structures (PDS) to keep in database
Analiz bittiğinde, mevcut yapının korunup korunmayacağına karar verirsin.
- Keep all existing PDS: En güvenli seçenektir. Mevcut hiçbir indeksi silmez, sadece üstüne yenilerini eklemeyi önerir.
- Do not keep any existing PDS: Mevcut tüm indeksleri geçersiz sayabilir ve “her şeyi baştan tasarlasaydık nasıl olurdu?” mantığıyla öneri sunar. (Tehlikelidir, mevcut performansı bozabilir).
- Keep indexes only: Sadece indeksleri korur, diğer yapıları (view vb.) temizlemeyi önerebilir.
Yukarıdaki açıklamalardan sonra sağ üst tarafta bulunan Advanced Options… kısmına girilir.
Gelen ekranda bulunan ifadelerin ne işe yaradığına değinelim.
1. Define max. space for recommendations (MB)
DTA’nın önereceği yeni indekslerin ve yapıların toplamda kaplayacağı maksimum disk alanını sınırlar.
- Eğer çok büyük tabloların (örneğin 15 TB’lık devasa ortamlar gibi) üzerinde çalışıyorsan, DTA bazen performans için yüzlerce GB yeni indeks önerebilir.
- Disk alanın kısıtlıysa buraya bir sınır (MB cinsinden) girebilirsin. Eğer boş bırakırsan, DTA disk alanını dert etmeden en iyi performans için gereken tüm indeksleri önerir.
2. Max. columns per index
Bir indeksin içinde bulunabilecek maksimum kolon sayısını belirler.
- SQL Server normalde bir indekste 16 ana kolon (key column) ve çok sayıda dahil edilmiş (included) kolon destekler.
- Buradaki varsayılan değer genelde yeterlidir. Ancak çok fazla kolon içeren “şişkin” indekslerin oluşmasını istemiyorsan bu sayıyı düşürebilirsin. Az kolonlu indeksler daha az yer kaplar ve bakım maliyeti (rebuild/reorganize) daha düşüktür.
3. Online Index Recommendations
Bu bölüm, önerilen indekslerin sisteme nasıl uygulanacağıyla ilgili scriptler üretilmesini sağlar. Canlı sistemlerde (Production) en kritik ayar burasıdır:
- All recommendations are offline: Önerilen indeksler oluşturulurken ilgili tablolar kilitlenir (Lock). Bu da uygulama tarafında sorguların beklemesine veya hata almasına neden olabilir. (En hızlı indeks oluşturma yöntemidir ama kesinti yaratır).
- Generate online recommendations where possible: SQL Server’ın Enterprise sürümünü kullanıyorsan, DTA indeks oluşturma komutlarını WITH (ONLINE = ON) ibaresiyle hazırlar. Böylece indeks oluşturulurken kullanıcılar tabloya erişmeye devam edebilir. Eğer online işlem desteklenmeyen bir veri tipi (LOB gibi) varsa, o zaman otomatik olarak offline öneri sunar.
- Generate only online recommendations: Sadece online (kesintisiz) olarak oluşturulabilecek indeksleri önerir. Eğer bir indeksin online oluşturulma imkanı yoksa, o öneriyi hiç sunmaz.
Yukarıdaki açıklamalardan sonra sol üst köşeden Run denilince oluşturduğumuz kontrol işlemi görülmektedir.
Gelen ekranda hiç bir raporun veya önerinin oluşmadığı görülmektedir.Aşağıdaki resimde ise ilgili rapor ve sunucu bilgilerini görmekteyiz
İkinci bir tuning bölümü açmak istiyorsanız sağ tıklayıp new session yapılması gerekmektedir.
Avantajları ve Dezavantajları
| Özellik | Açıklama |
| Zaman Tasarrufu | Manuel olarak saatler sürecek Execution Plan analizini dakikalar içinde yapar. |
| Uygulanabilirlik | Önerdiği tüm iyileştirmeler için hazır SQL scriptleri oluşturur; sadece “Execute” demeniz yeterlidir. |
| Maliyet Analizi | Mevcut yapı ile önerilen yapı arasındaki performans farkını yüzde (%) olarak raporlar. |
| Risk Notu | Bazen çok fazla indeks önererek yazma (Insert/Update) performansını düşürebilir. Önerileri körü körüne uygulamamak gerekir. |
Database Engine Tuning Advisor, SQL Server ekosistemindeki en güçlü optimizasyon araçlarından biridir. Özellikle karmaşık veritabanı şemalarında ve binlerce satırlık prosedürlerin olduğu sistemlerde, darboğazları tespit etmek için eşsiz bir rehberlik sunar. Ancak unutulmamalıdır ki; DTA bir karar verici değil, bir danışmandır. Sunduğu önerileri bir uzman gözüyle değerlendirip sisteme entegre etmek, sürdürülebilir bir performans için altın kuraldır.
Başka makalede görüşmek dileğiyle..
“Şüphesiz, Rabbin sana verecek ve sen de hoşnut olacaksın.”. Duha Süresi-5 Ayet
