Stored procedure performansını artırmanın temelini, içinde barındırdığı SQL sorgularının verimli yazılması ve yürütülmesi oluşturur. Bu, sadece doğru sonucu üretmekten ziyade, en az kaynak tüketimiyle en kısa sürede sonuca ulaşmak anlamına gelir. Cost-Based Optimizer (CBO)'nun kararlarını doğru yönlendirmek için istatistiklerin güncel olması kritik öneme sahiptir. Eksik veya eski istatistikler, optimizatörün yanlış yürütme planları seçmesine ve performansın dramatik şekilde düşmesine yol açabilir.

Sorgu yazımında en sık karşılaşılan anti-pattern'lerden biri, SELECT * kullanımıdır. Bu, gereksiz sütunların taşınmasına, indeks kullanımının etkinliğinin azalmasına ve I/O yükünün artmasına neden olur. Sadece ihtiyaç duyulan sütunların belirtilmesi, performans iyileştirmesinin ilk ve en basit adımıdır. Aynı şekilde, JOIN yapıları mümkün olduğunca basit tutulmalı ve gereksiz tablo katılımlarından kaçınılmalıdır.

Alt sorgular (subqueries) ve ortak tablo ifadeleri (CTE) bazen daha okunabilir kod sağlasa da, yürütme planı açısından her zaman en verimli seçenek olmayabilir. Özellikle correlated subquery'ler, ana sorgunun her satırı için tekrar çalıştırıldığından ciddi performans sorunları yaratabilir. Bu tür durumlarda, sorgunun JOIN'lere veya window fonksiyonlarına yeniden yazılması değerlendirilmelidir.

Anti-Pattern Potansiyel Sorun Önerilen Çözüm
SELECT * Kullanımı Gereksiz I/O, indeks atlama (index skip) kaybı Spesifik sütunları listelemek
Fonksiyonlarla Sarmalanmış Sütunlar (WHERE UPPER(Ad) = 'A') Indeks taramasını engeller, tablo taramasına (scan) zorlar Veriyi depolarken normalize etmek veya fonksiyonel indeks kullanmak
Implicit Data Type Dönüşümleri Optimizatörün indeks kullanamaması Açık ve uyumlu veri tipleri kullanmak
Nested Loop'un zorlanması (küçük/büyük tablo yanlış sıralaması) Optimal olmayan JOIN algoritması seçimi İstatistikleri güncellemek, sorguyu yeniden yazmak veya JOIN ipucu kullanmak

Index Kullanımı ve Yönetimi

Veritabanı performans optimizasyonunun bel kemiğini indeksler oluşturur. Doğru tasarlanmış bir indeks stratejisi, sorgu yanıt sürelerini katlanarak iyileştirebilir. Ancak, indekslerin maliyeti de vardır: INSERT, UPDATE ve DELETE işlemlerinde ek yük oluşturur ve disk alanı tüketirler. Bu nedenle, indeksleme dengeli bir şekilde yapılmalıdır. Performans kazanımı sağlayan, ancak yazma işlemlerini aşırı derecede yavaşlatmayan optimal indeks setini bulmak ana hedeftir.

Bileşik (composite) indekslerde sütun sıralaması hayati önem taşır. Sorguların WHERE, JOIN ve ORDER BY yan tümcelerinde en sık hangi sütun kombinasyonlarının ve hangi sırayla kullanıldığı analiz edilmelidir. Leading column (indeksin ilk sütunu) olmadan yapılan aramalar, çoğu durmda indeksin verimli kullanılmasını engeller. Örneğin, (Soyad, Ad) üzerine kurulu bir indeks, sadece 'Ad' üzerine yapılan bir filtrelemede etkisiz kalabilir.

Indeks parçalanması (fragmentation), zamanla indekslerin fiziksel veri sayfalarında dağınık hale gelmesi ve okuma verimliliğinin düşmesidir. Bu durum, özellikle yoğun yazma işlemi olan tablolarda sıkça gözlemlenir. Parçalanmış indeksler, gerekli veri sayfalarına ulaşmak için daha fazla mantıksal ve fiziksel I/O işlemi gerektirir. Düzenli bakım planları içinde REORGANIZE veya REBUILD işlemleri planlanarak bu sorun giderilmelidir.

Indeks Türü En İyi Kullanım Senaryosu Performans Katkısı
Kümelenmiş (Clustered) Sıklıkla aralık (range) sorguları veya sıralama yapılan anahtar sütunlar Veri erişiminde en yüksek, yazma maliyeti en fazla
Kümelenmemiş (Non-Clustered) Spesifik aramalar (nokta sorguları), kapsayan indeks (covering index) olarak Hedeflenmiş sorgularda çok hızlı, ek disk alanı gerektirir
Kapsayan (Covering) Sorgunun tüm ihtiyaç duyduğu sütunları içerdiği durumlar Tablo erişimini (key lookup) ortadan kaldırarak maksimum hız
Filtrelenmiş (Filtered) Tablonun belli bir alt kümesi üzerinde sık sorgulama yapıldığında Daha küçük, bakımı daha ucuz indeks, yüksek seçicilik

Geçici Nesneler ve Set Tabanlı Mantık

Stored procedure'lerde geçici tablolar (#temp) veya tablo değişkenleri (@table) kullanımı sıkça karşılaşılan bir durumdur. Ancak, bu nesnelerin yanlış seçimi performansı olumsuz etkileyebilir. Geçici tablolar, disk üzerinde (tempdb) fiziksel olarak oluşturulur ve istatistiklere sahiptir, bu da optimizatörün daha iyi kararlar almasını sağlar. Büyük veri setleri veya karmaşık JOIN'ler için daha uygundur. Tablo değişkenleri ise genellikle bellekte tutulur ve istatistiğe sahip değildir; bu nedenle optimizatör her zaman küçük bir kardinalite olduğunu varsayar. Bu varsayım, büyük veri setlerinde felaket derecede yanlış yürütme planlarına yol açabilir.

İşlem tabanlı (row-by-row) işlemler, veritabanı performansının en büyük düşmanlarından biridir. Cursor'lar veya while döngüleri içindeki tekil sorgular, ağ gecikmesi, ayrıştırma (parse) ve derleme (compile) maliyetlerini her iterasyonda tekrarlar. Buna karşılık, set tabanlı mantık, veriyi bir bütün olarak ele alır ve tek bir, optimize edilmiş sorgu ile işler. Bu yaklaşım, veritabanı motorunun gücünü ve paralel işleme yeteneklerini en üst düzeyde kullanmayı mümkün kılar. Büyük veri güncellemeleri veya silmeler her zaman bir cursor yerine, WHERE yan tümcesi uygun şekilde filtrelenmiş bir UPDATE/DELETE ifadesi ile yapılmalıdır.

Geçici nesne kullanımının bir diğer kritik yönü, temizlik ve ömür döngüsüdür. Açıkça DROP ifadesi kullanmak veya otomatik temizliğe güvenmek tempdb üzerindeki baskıyı etkiler. Ayrıca, aynı procedure'ün eşzamanlı çoklu çağrılarında, geçici tablo adlarının çakışmamasına dikkat edilmelidir. Nested procedure çağrılarında geçici tabloların görünürlük kapsamı (scope) da yürütme planı önbelleklemesi ve yeniden kullanımı üzerinde dolaylı bir etkiye sahiptir.

Kod Yapısı ve Derleme Optimizasyonları

Stored procedure'lerin iç yapısı ve yazım tarzı, yalnızca okunabilirlik değil aynı zamanda performans üzerinde de doğrudan etkiye sahiptir. Modüler yaklaşım için sıkça başvurulan, diğer procedure'leri çağırma (nesting) yöntemi, plan önbelleğini parçalayabilir ve her bir modülün ayrı ayrı derlenmesine neden olabilir. Büyük ve monolitik procedure'ler yerine, sık sık yeniden kullanılan ve optimize edilmiş küçük modüller tercih edilmelidir. Ayrıca, kontrol yapıları (IF-ELSE, WHILE) içinde mümkün olduğunca büyük sorgular yazmaktan kaçınılmalı, sorguların bu yapıların dışına alınması değerlendirilmelidir.

Derleme (compilation) ve yeniden derleme (recompilation) süreçleri, CPU zamanına mal olur. Bir procedure'ün her çalıştırmada yeniden derlenmesi, küçük sorgular için kabul edilebilir olsa da, büyük ve karmaşık işlemlerde toplam süreyi önemli ölçüde artırır. Ancak, zorunlu yeniden derlemeler bazen gereklidir; örneğin, temel alınan tablo yapısında değişiklik olduğunda veya istatistikler kritik şekilde güncellendiğinde. Yeniden derlemeyi tetikleyen en yaygın sebepler arasında, procedure içinde geçici tablo oluşturulup ardından boyutunda önemli değişiklik olması veya SET OPTION değişiklikleri yer alır. Bu süreçler anlaşılarak, gereksiz yeniden derlemeleri önleyen, ancak verimli planların kullanılmasını garanti eden bir kod yapısı oluşturulabilir.

WITH RECOMPILE seçeneğinin kullanımı dikkatle ele alınmalıdır. Bu seçenek, procedure'ün her çalıştırmasında yeni bir plan oluşturulmasını sağlar, bu da plan önbelleğini kirletmez. Bu, parametre değerlerinin aşırı değişkenlik gösterdiği ve tek bir "ortalama" planın tüm durumlar için kötü performans verdiği senaryolarda faydalı bir strateji olabilir. Ancak, derleme maliyetinin her seferinde ödendiği unutulmamalıdır. Daha akıllı bir çözüm, OPTIMIZE FOR bilinmeyen veya tipik bir değer ipucu kullanmak veya dinamik SQL ile sorguyu parametre değerlerine göre optimize etmektir.

Yapısal Faktör Potansiyel Performans Etkisi Önerilen İyileştirme
Fazla İç İçe Procedure Çağrısı Artık plan önbelleği, artan derleme sayısı Kritik sorguları ana procedure'e dahil etmek veya plan kılavuzları kullanmak
Kontrol Akışı İçinde Büyük Sorgular Yürütme planının kontrol akışı boyunca defalarca değerlendirilmesi Sorguları mümkünse akış dışına almak veya dinamik SQL ile yeniden yazmak
Gereksiz SET Seçenekleri Değişikliği Procedure'ün yeniden derlenmesine neden olur SET seçeneklerini procedure başında bir kez ve tutarlı şekilde ayarlamak
Monolitik Kod Blokları Okunabilirlik ve sorun gidermenin zorlaşması, lokal etki analizinin güçleşmesi Mantıksal olarak ayrılabilir işlemleri kendi SQL batch'lerine bölmek
  • Plan Önbelleği Analizi: sys.dm_exec_cached_plans ve sys.dm_exec_query_stats DMV'leri ile sık yeniden derlenen veya bellekte çok yer kaplayan planlar tespit edilmelidir.
  • İstatistik Güncelleme Stratejisi: Büyük tablolar için artımlı (incremental) istatistik güncellemesi veya uygun örnekleme oranı belirlenmelidir.
  • Parametre Veri Tipi Uyumu: Procedure parametre tipleri ile hedef tablo sütun tipleri birebir eşleşmeli, örtük dönüşüm önlenmelidir.
  • Schema Binding: WITH SCHEMABINDING seçeneği, temel tablolardaki beklenmeyen değişiklikleri ve bunların neden olduğu yeniden derlemeleri engelleyebilir.
  • Dinamik SQL Yönetimi: Sp_executesql kullanımı, parametreleştirilmiş sorgular sayesinde plan yeniden kullanımını artırır ve SQL Enjeksiyon riskini ortadan kaldırır.

Parametre Sniffing ve Önbellek Yönetimi

Parametre sniffing, SQL Server'ın bir stored procedure'ü ilk defa çalıştırırken (veya yeniden derlerken) gelen parametre değerlerini kullanarak optimize bir yürütme planı oluşturması sürecidir. Bu, genellikle faydalı bir davranış olsa da, ilk parametre için oluşturulan planın, sonraki farklı parametre değerleri için son derece verimsiz olmasına yol açabilir. Örneğin, ilk çalıştırmada yüksek seçiciliğe sahip bir değr kullanılmışsa, optimizatör buna uygun bir indeks arama planı seçebilir. Ancak, düşük seçiciliğe sahip bir değerle aynı plan kullanıldığında, maliyetli bir tablo taraması yerine yine arama yapılmaya çalışılır, bu da performansı düşürür.

Bu sorunu çözmek için çeşitli gelişmiş stratejiler mevcuttur. LOCAL_VARIABLE yaklaşımı, parametre değerini bir yerel değişkene atayarak sniffing'i etkin bir şekilde devre dışı bırakır. Bu, optimizatörün ortalama dağılım varsayımlarını kullanmasına neden olur ve her zaman optimal olmayabilir. Daha sofistike bir yöntem, OPTION (OPTIMIZE FOR UNKNOWN) veya OPTION (OPTIMIZE FOR (@param = specific_value)) sorgu ipuçlarını kullanmaktır. İlki, istatistiksel ortalamaya dayalı bir plan oluştururken, ikincisi belirli bir "tipik" değer için optimize edilmiş sabit bir planın kullanılmasını sağlar.

Plan önbelleği yönetimi, sistem geneli performans için kritiktir. Tek bir procedure'ün farklı parametrelerle farklı "optimal" planlara ihtiyaç duyduğu durumlarda, plan önbelleği aynı procedure için birden fazla planla dolabilir. Bu, önbellek parçalanmasına ve değerli bellek kaynaklarının israfına yol açar. Plan kılavuzları (Plan Guides) veya daha modern Query Store özelliği, bu tür sorunları yönetmek için tasarlanmıştır. Query Store, sorgu performansını otomatik olarak izler, farklı yürütme planlarını kaydeder ve hatta performans regresyonu durumunda önceki iyi plana zorla geri dönüş (plan forcing) yapılmasına olanak tanır.

Dinamik SQL kullanımı, parametre sniffing sorununu aşmanın bir başka yoludur. Procedure içinde sp_executesql ile oluşturulan dinamik sorgular, her farklı parametre kombinasyonu veya değer aralığı için ayrı ve optimize edilmiş bir sorgu metni üretebilir. Bu, her birinin kendi optimal planını önbelleğe almasına olanak tanır. Ancak, bu yaklaşım kod karmaşıklığını artırır ve güvenlik açısından parametreleştirmenin doğru uygulanmasını zorunlu kılar. Tercih edilen strateji, sorunun boyutuna, veri dağılımına ve bakım maliyetine bağlı olarak değişiklik gösterecektir.

Sniffing Sorunu Çözümü Mekanizma Avantajlar / Dezavantajlar
WITH RECOMPILE (Procedure Seviyesi) Her çalıştırmada yeni plan, önbelleğe alınmaz + Her seferinde en iyi plan. - Yüksek derleme maliyeti, ölçeklenmez.
OPTION (RECOMPILE) (Sorgu Seviyesi) Sadece ilgili sorgu için anlık yeniden derleme + Hedefli, daha düşük maliyet. - Yine de derleme zamanı alır.
Yerel Değişken Kullanımı Sniffing'i devre dışı bırakır, ortalama tahmin kullanılır + Sabit, öngörülebilir plan. - Nadiren tüm değerler için en iyisidir.
Query Store ile Plan Zorlama Performans regresyonunda daha önce kaydedilmiş iyi plana geri dönülür + Otomatik, güvenilir. - SQL Server 2016+, izleme ve yapılandırma gerektirir.
Koşullu Plan Seçimi (Dinamik SQL) Parametre değerine göre farklı sorgu metni ve planı + Yüksek esneklik ve optimizasyon. - Karmaşık kod, bakım zorluğu.

Stored procedure performans optimizasyonu, tek bir sihirli değnek uygulaması değil, çok katmanlı ve birbirine bağlı bir dizi disiplinli uygulamanın sonucudur. Temiz ve verimli SQL sorguları yazmak, doğru indeks stratejisini belirlemek ve bu indeksleri sağlıklı tutmak ilk temeldir. Bunun üzerine, set tabanlı işlemleri benimsemek ve geçici nesneleri bilinçli kullanmak ikinci katmanı oluşturur. Üçüncü ve en ince katman ise, procedure'ün derleme davranışını, parametre sniffing etkileşimlerni ve plan önbelleği yaşam döngüsünü yönetmektir. Bu katmanların her biri, sistemin ölçeklenebilirliğini, tutarlı yanıt sürelerini ve kaynak kullanım verimliliğini garanti etmek için akademik bir titizlikle ele alınmalıdır.

Performans tuning süreci asla statik değildir. Veri hacminin artması, kullanım örüntülerinin değişmesi ve SQL Server'ın yeni sürümlerine geçiş, sürekli izleme ve ayarlamayı gerekli kılar. Bu nedenle, geliştirilmiş procedure'lerin performansı, Query Store, Extended Events veya diğer izleme araçlarıyla düzenli olarak gözlemlenmeli ve yukarıda belirtilen optimizasyon ilkeleri ışığında periyodik olarak gözden geçirilip iyileştirilmelidir.