Biz veritabanı yöneticilerinin en sık karşılaştığı çıkmazlardan biri de şu olsa gerek: Yavaş çalışan bir sorgu tespit ettik, çözümü de biliyoruz ama sorguyu maalesef değiştiremiyoruz.
Bu durum özellikle şu senaryolarda ortaya çıkar:
- ERP ve hazır paket yazılımlar: Sorgu, uygulamanın kendi katmanında üretilir. Kaynak koda erişiminiz yoktur, üreticiye açtığınız destek talebi ise aylarca bekleyebilir.
- ORM tarafından üretilen SQL: Entity Framework, Hibernate gibi araçların ürettiği sorgular çoğu zaman elinizin altında değildir ve müdahale edemezsiniz.
- Üçüncü parti raporlama araçları: BI araçlarının arka planda çalıştırdığı sorgulara müdahale şansınız sınırlıdır.
- Sorgu elimizin altındadır fakat çok kompleks : Bu durumda sorgu optimizasyonu yaparken çalışan fonksiyonların bozulması veya veri tutarlılığı kaybı da mümkün olabilir.
Perakende sektöründe ERP sistemleri yöneten biri olarak bu gibi case’lerle bir çok kez karşılaştım. Peki sorgu metnine tek karakter dokunmadan elimizde hangi silahlar var?
Aslında olay basit ve 3 adımdan oluşuyor. Bu yazıda en etkili olanından başlayarak bu 3 yaklaşımı da ele alacağım: Doğru index tasarımı, Query Store ipuçları ve İstatistik bakımı.
Önce Teşhis: Yürütme Planını Okumak
Tedaviye geçmeden önce doğru teşhis şart. SSMS’te gerçek yürütme planını (Actual Execution Plan, Ctrl+M) ve I/O istatistiklerini açarak başlıyoruz:
SET STATISTICS IO, TIME ON;Plan üzerinde sırasıyla şunlara bakıyorum:
- Tahmini ve gerçek satır sayıları arasındaki uçurum — İstatistik sorununa işaret eder.
- En çok süre harcayan operatör — SQL Server 2016 SP1 ve sonrasında her operatörün üzerinde geçen süre planda görünür.
- Uyarı işaretleri — Sarı ünlemler (implicit conversion, tempdb spill vb.) genellikle ilk bakılması gereken yerlerdir.
Burada kritik bir nokta var: SQL Server’ın “missing index” önerisi her zaman görünmez. Bazı pahalı operatörler için optimizer hiç öneri üretmez. Bunların en sinsisi de Eager Index Spool‘dur.
Gizli Düşman: Eager Index Spool
Eager Index Spool, optimizer’ın şu itirafıdır: “Bana lazım olan index tabloda yok, ben de her çalıştırmada tempdb’de kendime geçici bir index inşa ediyorum.”
Sorun şu ki bu geçici index’in maliyetini her sorgu çalışmasında yeniden ödersiniz. Üstelik bu operatör bloklayıcıdır — alt operatöründen gelen tüm satırları çalışma tablosuna yazmadan üst operatöre tek satır bile göndermez. Daha da kötüsü: Eager Index Spool için missing index önerisi üretilmez. Yani “Index Advisor temiz, demek ki index sorunu yok” diye düşünürseniz yanılırsınız.
Örnek Senaryo
Bir e-ticaret veritabanında, uygulamanın ürettiği ve değiştiremediğimiz şuna benzer bir korelasyonlu alt sorgu olduğunu düşünelim — her bayinin kendi kategorisindeki en güncel fiyatını çeken bir yapı:
SELECT f.BayiKodu,
f.UrunKodu,
f.Fiyat,
(SELECT TOP (1) f2.GecerlilikTarihi
FROM dbo.FiyatListesi AS f2
WHERE f2.BayiKodu = f.BayiKodu
ORDER BY f2.GecerlilikTarihi DESC) AS SonGuncelleme
FROM dbo.FiyatListesi AS f
ORDER BY f.BayiKodu;Birkaç milyon satırlık bir tabloda bu sorgunun planında tipik olarak şunu görürsünüz: dış tablo için bir Clustered Index Scan ve hemen ardından sorgu süresinin aslan payını yiyen bir Eager Index Spool. Mantıksal okuma sayısı (logical reads) yüz binlerle ifade edilir, CPU süresi geçen sürenin birkaç katıdır (yani sorgu paralel çalışıp birden çok çekirdeği meşgul etmektedir).
Spool’dan Index Reçetesi Çıkarmak
İşin güzel tarafı: Spool operatörü, ihtiyaç duyduğu index’in reçetesini kendi üzerinde taşır. Planda spool operatörünün üzerine gelip özelliklerine baktığınızda iki kritik bilgi görürsünüz:
- Seek Predicate → Oluşturacağınız index’in anahtar sütunları (örneğimizde BayiKodu)
- Output List → INCLUDE ile ekleyeceğiniz veya sıralama da yapılacaksa anahtara dahil edeceğiniz sütunlar (örneğimizde GecerlilikTarihi)
Alt sorguda ORDER BY GecerlilikTarihi DESC ile sıralama da yapıldığı için, bu sütunu INCLUDE yerine anahtara almak hem spool’u hem de ardından gelen Sort operatörünü ortadan kaldırır:
CREATE NONCLUSTERED INDEX IX_FiyatListesi_Bayi_Tarih
ON dbo.FiyatListesi (BayiKodu, GecerlilikTarihi);Yeni index’i oluşturmadan önce mevcut index’leri mutlaka kontrol edin — belki tek sütunlu mevcut bir index’i genişletmek, sıfırdan yenisini eklemekten daha doğrudur:
EXEC sys.sp_helpindex N'dbo.FiyatListesi';Index oluştuktan sonra aynı sorguyu tekrar çalıştırdığınızda spool’un yerini bir Index Seek‘in aldığını, mantıksal okumaların ve sürenin dramatik biçimde düştüğünü görürsünüz. Kendi ortamımda, ERP’nin ürettiği ve dokunamadığım benzer bir rapor sorgusunu yalnızca doğru kapsayıcı index ile dakikalar mertebesinden saniyelere indirdiğim oldu — sorgu metni tek karakter değişmeden.
Dikkat: Her index’in bir yazma maliyeti vardır. Yoğun INSERT/UPDATE alan tablolarda yeni index eklemeden önce mevcut index kullanım istatistiklerini (sys.dm_db_index_usage_stats) inceleyin ve mümkünse mevcut bir index’i genişletmeyi tercih edin.
İkinci Silah: Query Store İpuçları (SQL Server 2022+)
Index eklemek her zaman yeterli olmaz. Bazen sorun parametre koklamasıdır (parameter sniffing), bazen de optimizer’ın ısrarla yanlış bir plan seçmesidir. SQL Server 2022 ve sonrası ile gelen Query Store Hints özelliği, sorgu metnine dokunmadan sorguya ipucu eklemenizi sağlar:
— Önce Query Store’dan sorgunun query_id değerini bulun
SELECT qsq.query_id, qst.query_sql_text
FROM sys.query_store_query AS qsq
JOIN sys.query_store_query_text AS qst
ON qsq.query_text_id = qst.query_text_id
WHERE qst.query_sql_text LIKE N'%FiyatListesi%';— Sonra ipucunu kalıcı olarak bağlayın
EXEC sys.sp_query_store_set_hints
@query_id = 1234,
@query_hints = N'OPTION (RECOMPILE)';MAXDOP, OPTIMIZE FOR UNKNOWN, USE HINT(…) gibi pek çok ipucunu bu yöntemle uygulayabilirsiniz. Uygulama yeni sürüm aldığında bile ipucu sorguya bağlı kalır.
Daha eski sürümlerde (2016–2019) benzer amaçla plan guide veya Query Store’un plan zorlama (force plan) özelliği kullanılabilir; kurulumu biraz daha zahmetlidir ama mantık aynıdır: koda dokunmadan optimizer’ı yönlendirmek.
Üçüncü Silah: İstatistikleri İhmal Etmeyin
Plandaki tahmini ve gerçek satır sayıları birbirinden çok farklıysa, sorun çoğu zaman ne sorguda ne de index’tedir — bayat istatistiklerdedir. Büyük tablolarda otomatik istatistik güncelleme eşiği geç tetiklenebilir. Kritik tablolar için planlı bakım şarttır:
UPDATE STATISTICS dbo.FiyatListesi WITH FULLSCAN;Özellikle sürekli artan tarih/kimlik sütunlarında (ascending key problemi) gece eklenen veriler istatistiklerin “görmediği” bölgede kalır ve sabah raporları sürünür. Düzenli istatistik güncellemesi, tek satır kod değişikliği gerektirmeyen en ucuz performans yatırımıdır.
Özet ve Karar Sırası
Sorguya dokunamadığınız bir performans sorununda izleyeceğim sıra şudur:
- Planı oku: En pahalı operatörü ve tahmin sapmalarını bul. Eager Index Spool görüyorsan missing index önerisi olmasa bile index eksiği var demektir.
- Index tasarla: Spool’un Seek Predicate ve Output List bilgilerinden reçeteyi çıkar; önce mevcut index’leri genişletmeyi değerlendir.
- İstatistikleri tazele: Tahmin sapması varsa FULLSCAN ile güncelle.
- Query Store ipuçları / plan zorlama: Optimizer hâlâ yanlış plan seçiyorsa son koz budur.
- Donanım: Yukarıdakilerin hepsi tükendiyse RAM, CPU ve disk hızı gündeme gelir — ama tecrübeyle sabittir ki doğru bir index, çoğu zaman bir donanım yükseltmesinden daha fazlasını kazandırır.
Sorguyu yeniden yazabildiğiniz durumlarda elbette önce onu deneyin; ama kodun kilitli/gizli olduğu ERP ve diğer tüm uygulamalarda, yürütme planını okuyabilmek ve doğru index’i tasarlayabilmek şapkadan tavşan çıkarmaya eşdeğer bir durumdur 🙂
Sorularınızı ve kendi tecrübelerinizi yorumlarda paylaşabilirsiniz.
Kaynaklar:
https://www.mssqltips.com/sqlservertip/11560/optimize-sql-server-query-without-changing-the-query
