Kaynak kodu değiştirilemeyen uygulamada performans: indeks, Query Store, plan zorlama

Senaryo: sorgu metni senin değil

Satın alınmış bir uygulama yavaşladı. Üretici sorguyu değiştirmiyor ya da sürüm aylar sonra gelecek. Elinde kod yok, ama veritabanı senin. Peki neye dokunabilirsin? Bu dersin konusu seçenekleri ve özellikle riskini bilmek.

Seçenekler, ucuzdan pahalıya

Hangisini seçersen seç, üreticinin destek koşullarını oku: bazı sözleşmeler şemaya eklenen nesneleri sınırlar. Eklediğin nesneyi kayıt altına al, yeni sürüm yüklemesinde kaybolabilir ya da çakışabilir.

İndeks: en sık çözüm, kör uygulanırsa en sık hata

Sorgu metni sabitse indeks tasarımı o metne uyar. Planda sıralama kalkıyor mu, arama yapılıyor mu, ölç:

EXPLAIN QUERY PLAN
SELECT event_at, record_id FROM ops_access_log
WHERE login_name = 'svc_portal' AND event_at >= '2024-11-12';

CREATE INDEX ix_hesap_zaman ON ops_access_log (login_name, event_at);
EXPLAIN QUERY PLAN
SELECT event_at, record_id FROM ops_access_log
WHERE login_name = 'svc_portal' AND event_at >= '2024-11-12';

Eksik indeks önerileri

SQL Server planda ve sys.dm_db_missing_index_details görünümünde indeks önerir. Belgeye göre bunlar reçete değildir:

• Tek sorgunun çalışmadan önceki tahminine dayanır, sonradan sınanmaz.
• Anahtar sütunları sırasız listelenir. Sırayı sen verirsin: eşitlik sütunları önce, eşitsizlik sonra.
• Benzer öneriler çoğalır. Var olan indeksle karşılaştır, mümkünse birleştir.
• Sunucu yeniden başlayınca veriler silinir. Query Store açıksa saklanan planlarda kalır.

Koşul fonksiyon içeriyorsa

Sorguyu WHERE date(event_at) = ... diye yazıyorsa ve değiştiremiyorsan indeksi ifadeye kurarsın. SQLite ve PostgreSQL ifade indeksi, SQL Server hesaplanmış sütun üzerinde indeks, Oracle fonksiyon tabanlı indeks kullanır. Ayrıntısı Tür dönüşümü ve fonksiyon tuzakları dersinde.

Query Store: planın geçmişi

Query Store sorguları, planları ve çalışma istatistiklerini veritabanı başına saklar. Belgeye göre SQL Server 2016-2019'da varsayılan olarak kapalıdır, SQL Server 2022'de yeni veritabanlarında açıktır. Planı önbellekten silinse de geçmişi kalır: "Dün hızlıydı" sorusunu yanıtlamanın yolu budur.

-- Yalnız okumak için: bu komutlar burada çalışmaz
SELECT q.query_id, p.plan_id, t.query_sql_text,
       rs.count_executions, rs.avg_duration, rs.avg_logical_io_reads
FROM sys.query_store_plan p
JOIN sys.query_store_query q ON q.query_id = p.query_id
JOIN sys.query_store_query_text t ON t.query_text_id = q.query_text_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id;

Bu derste aynı mantığı kurgusal ops_plan_stats tablosunda çalıştırıyoruz. Dikkat: satırlar saat başınadır, bu yüzden ortalamaların ortalaması yanlış sonuç verir. Çalışma sayısıyla ağırlıklandır:

SELECT query_name, plan_id,
       SUM(exec_count) AS calisma,
       ROUND(1.0 * SUM(exec_count * avg_reads) / SUM(exec_count)) AS ort_okuma
FROM ops_plan_stats
GROUP BY query_name, plan_id
ORDER BY query_name, plan_id;

Üç farklı durum görüyorsun:

• rapor_ay_tur: plan değişince okuma büyümüş. Gerileme budur.
• rapor_sube_ozet: plan değişmiş, okuma düşmüş. Plan değişimi tek başına kötü değildir.
• rapor_sube_detay: ortalamalar yakın, ama soru değerden değere değişiyor. Şubeler çok farklı büyüklükte, bir plan küçük şubede iyi, büyük şubede kötü olabilir. Tek plana zorlamak diğer değeri bozar. Bu parametre koklama konusudur.

Plan zorlama ve riskleri

-- Yalnız okumak için
EXEC sp_query_store_force_plan @query_id = 8, @plan_id = 11;
EXEC sp_query_store_unforce_plan @query_id = 8, @plan_id = 11;

-- Zorlanan planları düzenli denetle
SELECT p.plan_id, p.query_id, p.force_failure_count, p.last_force_failure_reason_desc
FROM sys.query_store_plan p
WHERE p.is_forced_plan = 1;

Belgeden öğrendiklerimiz:

• Zorlama garanti değildir. Başarısız olursa motor sorguyu normal biçimde derler ve neden sys.query_store_plan içinde görünür.
• Sonuç plan zorlanan planla aynı değil, benzer olabilir. Nadiren performans belirgin biçimde kötüleşir, o zaman zorlama kaldırılır.
• Planın kullandığı nesne değişir ya da silinirse (indeks kaldırılması gibi) zorlama başarısız olmaya başlar.
• Zorlanan plan unutulursa, veri dağılımı değiştiğinde kötü plana kilitli kalırsın. Takvime bağla.

Yeni sürümlerde Query Store ipuçları (SQL Server 2022 ve sonrası) uygulama metnine dokunmadan ipucu ekler. Belge bunları plan kılavuzlarına göre daha kolay bulur, yine de istatistik ve indeks bakımından sonra, son çare olarak önerir.

-- Yalnız okumak için
EXEC sys.sp_query_store_set_hints @query_id = 8, @query_hints = N'OPTION (RECOMPILE)';
EXEC sys.sp_query_store_clear_hints @query_id = 8;

Araya ağ geçidi koymak (reverse proxy)

Uygulama ile veritabanı arasına bir ara katman koymak, koda dokunmadan sorguyu görme fırsatı verir. Bu bölüm kavramsaldır: SQL Server istemcileri TDS protokolüyle konuşur ve bu trafik tarayıcıda yakalanamaz, burada çalışan bir örneği yoktur.

Şifreli bağlantıda sorguyu görmek için geçidin şifrelemeyi çözmesi gerekir; bu da güven sınırını taşır. Bağlantı durumu taşıyan bir protokolde (oturum ayarları, geçici tablolar, açık işlemler) yönlendirme de sanıldığından zordur. Karar sorusu: Query Store ve denetim günlüğü bu ihtiyacı karşılıyor mu? Çoğu zaman yeni bir parça eklemeden başla.