Mahdi MolaviBir API yoğun saatlerde yavaşladığında suçlu genellikle "tüm veritabanı" değil, birkaç belirli sorgudur. Bu rehber, bir yavaşlık bildiriminden çözüme giden pratik yolu izliyor: Query Store ile pahalı sorguları bulmak, yürütme planını okumak, yazma maliyetini göz önünde tutarak indekslemek, parameter sniffing'i ve kilitleri kontrol altına almak, maliyetli EF Core kalıplarını düzeltmek ve SqlBulkCopy ile toplu yazmak.
"Yavaş"tan belirli bir sorguya
"Zirvede yavaş" demek işe başlamak için yeterli değil; hangi endpoint'in, hangi yük altında ve hangi sorguyla yavaşladığını bilmeniz gerekir. Gozine2'de sınav zirvelerinden önce darboğazları erkenden bulmak için en yoğun API'lere Locust ile yük testi uyguladım, ardından SQL Server sorgularını, önbellekleme yollarını ve aynı API'leri iyileştirdim. Bu çalışma sınav zirvesi vaka çalışmasında anlatılıyor; yük senaryosunun tasarımını da Locust ile yük testi rehberinde ele alıyorum. Yük testi belirtiyi yeniden üretir; zamanın nereye gittiğini SQL Server'ın kendi araçları gösterir.
İlk araç Query Store. SQL Server 2016'dan beri var ve SQL Server 2022'den beri yeni veritabanlarında varsayılan olarak açık; daha önce oluşturulmuş bir veritabanında hâlâ kapalı olabilir. Sorguların, planların ve çalışma zamanı istatistiklerinin geçmişini veritabanının kendi içinde tuttuğu için yeniden başlatmalar bu geçmişi silmez. SSMS'teki Top Resource Consuming Queries raporunda Total istatistiğini seçin: birkaç milisaniye süren ama saniyede yüzlerce kez çalışan bir sorgu, toplamda, saatte bir kez çalışan ve birkaç saniye süren bir sorgudan daha pahalıdır.
Query Store kapalıysa sys.dm_exec_query_stats, plan önbelleğindeki planlar için kümülatif istatistikler verir; bir plan önbellekten çıktığında istatistikleri de onunla birlikte gider. SQL Server 2022'den beri bu görünümü okumak için VIEW SERVER PERFORMANCE STATE izni gerekir (önceden VIEW SERVER STATE).
SELECT TOP (20)
qs.execution_count,
qs.total_worker_time / 1000 AS total_cpu_ms,
qs.total_worker_time / qs.execution_count / 1000.0 AS avg_cpu_ms,
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
SUBSTRING(st.text, qs.statement_start_offset / 2 + 1,
(CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset) / 2 + 1) AS statement_text,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC;
Bu DMV'deki süreler mikrosaniye cinsindendir; 1000'e bölünmelerinin nedeni budur. Bir düzeltmenin öncesini ve sonrasını karşılaştırmak için avg_logical_reads sütunu süreden daha iyidir, çünkü sunucunun o anki yoğunluğuna daha az bağlıdır. Bu sorgunun döndürdüğü plan, önbelleğe alınmış plandır ve gerçek satır sayılarını içermez.
Yürütme planını okumak
Gerçek planı SSMS'te Include Actual Execution Plan ile ya da SET STATISTICS XML ON ile alın; SET STATISTICS IO ON her tablo için mantıksal okumaları da gösterir. EF Core'un ürettiği sorguyu logdan ya da ToQueryString() ile alın ve aynı parametrelerle sp_executesql üzerinden çalıştırın; yerel bir değişken (ToQueryString() çıktısındaki DECLARE ifadeleri gibi) ya da sabit bir değer farklı bir plan üretebilir. ARITHABORT ayarını da eşleyin: SSMS'te varsayılan olarak açık, uygulama bağlantısında kapalıdır ve plan önbelleği anahtarının bir parçası olduğu için aynı sorgu SSMS'te hızlı, uygulamada yavaş olabilir. Planda şu işaretlere bakarım:
- Scan ve Seek. Seek doğrudan ihtiyaç duyduğu aralığa gider; Scan indeksin tamamını ya da büyük bir kısmını okur. Hot path'teki büyük bir tabloda Scan bir uyarı işaretidir. Bir indeks mevcut olsa bile iki yaygın neden, sütun üzerindeki bir fonksiyon (
YEAR(CreatedAt) = 2025) ve örtük tür dönüşümüdür. Örneğin birvarcharsütunu birnvarcharparametresiyle karşılaştırmak, SQL collation'larında (SQL_Latin1_General_CP1_CI_ASgibi) Seek'i engeller, Windows collation'larında ise genellikle engellemez. EF Core parametre tipini sütun eşlemesinden aldığı içinvarcharbir sütun için modeldeIsUnicode(false)ayarlayın. - Key Lookup. Nonclustered indeks satırı buldu, ama kalan sütunlar her satır için ayrı ayrı clustered indeksten okunuyor; heap üzerinde aynı operatörün adı RID Lookup'tır. Birkaç satır için ucuzdur, binlerce satır için değil.
- Tahmini ve gerçek satır sayısı. Büyük bir fark, planın yanlış bir varsayımla seçildiği anlamına gelir: güncelliğini yitirmiş istatistikler, parameter sniffing ya da tahmin edilmesi zor bir koşul (predicate). Nested Loops join'in iç tarafında tahmini sayı çalıştırma başınadır, gerçek sayı ise tüm çalıştırmaların toplamıdır; karşılaştırmadan önce tahmini çalıştırma sayısıyla çarpın (SSMS 18.5 ve sonrası bu değeri Estimated Number of Rows for All Executions olarak gösterir).
- Uyarılar. Sarı üçgen genellikle tempdb'ye taşan (spill) bir Sort ya da Hash veya örtük bir dönüşüm anlamına gelir. Yeşil Missing Index önerisi yalnızca bir ipucudur: tek bir sorgunun tahmininden gelir, anahtar sütunların sırasını belirlemez, asla filtrelenmiş indeks önermez ve mevcut indekslerle çakışabilir.
İndeksler: kapsayan, filtrelenmiş ve yazmalardaki maliyet
Kapsayan (covering) indeks, sorgunun ihtiyaç duyduğu her sütunu içerir: arama, join ve sıralama sütunları anahtarda, geri kalanı INCLUDE içinde. INCLUDE sütunları yalnızca yaprak (leaf) düzeyinde saklanır, 32 sütunluk ve 1.700 baytlık anahtar sınırlarına dahil edilmez ve sıralarının performansa etkisi yoktur. Sonuç, Key Lookup olmadan bir Seek'tir.
-- Hot query (example):
-- SELECT QuestionId, ChoiceId, UpdatedAt FROM dbo.Answers WHERE AttemptId = @attemptId;
CREATE NONCLUSTERED INDEX IX_Answers_AttemptId
ON dbo.Answers (AttemptId)
INCLUDE (QuestionId, ChoiceId, UpdatedAt);
-- Only open attempts are searched often (Status 0 = InProgress)
CREATE NONCLUSTERED INDEX IX_Attempts_Open
ON dbo.Attempts (ExamId, Deadline)
WHERE Status = 0;
İlk indeks yukarıdaki örnek sorguyu tamamen kapsar. İkincisi filtrelenmiştir ve yalnızca açık sınav oturumlarını tutar; daha küçüktür, istatistikleri daha doğrudur ve yalnızca o alt küme değiştiğinde güncellenir. Sınırlamaları: LIKE değil, yalnızca basit karşılaştırma operatörlerini kabul eder; sorgunun koşulu indeks filtresinin bir alt kümesi olmalıdır; filtre değeri bir parametreden geliyorsa (Status = @status) optimizer genellikle bu indeksi kullanmaz, çünkü önbelleğe alınmış bir plan her değer için geçerli olmak zorundadır (OPTION (RECOMPILE) bunu çözer). EF Core'da LINQ ifadesinin içine yazılmış bir sabit, örneğin bir enum üyesi, literal'e dönüşür; bir değişken ise parametreye. QUOTED_IDENTIFIER veya ANSI_NULLS kapalı olan bir bağlantı, böyle bir indeksin kapsadığı veriyi değiştirdiğinde hata alır.
Yine de indekslerin bir maliyeti var. Her nonclustered indeks, her INSERT ve DELETE'te ve kendi sütunlarına dokunan her UPDATE'te, ek loglama ve kilitlemeyle birlikte güncellenir. SQL Server indeks tasarım kılavuzuna göre yalnızca optimizer'a seçenek sunmak için çok sayıda indeks oluşturmak veri değişikliklerini yavaşlatır ve eşzamanlılık sorunlarına yol açabilir. Zirvede yoğun yazma alan bir tabloda, örneğin sınav sırasında cevaplar tablosunda, bu maliyet tam da en kötü anda ödenir. sys.dm_db_index_usage_stats içinde user_seeks, user_scans ve user_lookups toplamını user_updates ile karşılaştırın. Bu sayaçlar yeniden başlatmada sıfırlanır; bu yüzden en az bir zirveyi kapsayan bir döneme bakın. Mevcut bir indekse INCLUDE sütunu eklemek, genellikle neredeyse aynısı olan yeni bir indeks oluşturmaktan daha iyidir.
Parameter sniffing: tek plan her değere uymadığında
SQL Server parametreli bir sorguyu ilk seferde o çalıştırmanın değeriyle derler ve planı sonraki değerler için yeniden kullanır. Çarpık dağılımlı veride bu sorun çıkarır. Örneğin ülke çapındaki bir sınavın on binlerce adayı, her sınıf sınavının ise birkaç düzine adayı varsa, küçük bir sınav için oluşturulmuş bir plan (Nested Loops ve Key Lookup) büyük sınav için çok yavaştır. Belirtisi, bazen hızlı bazen yavaş olan ve davranışı bir yeniden başlatmadan ya da istatistik güncellemesinden sonra aniden değişen bir sorgudur. Query Store böyle bir sorguyu, Queries With High Variation veya Regressed Queries raporunda birbirinden çok farklı birkaç planla gösterir.
Microsoft'un dokümantasyonu hint'leri son çare olarak görür; bu yüzden önce istatistikleri ve indeksleri kontrol edin. Ondan sonra şu seçenekleriniz var:
OPTION (RECOMPILE): her çalıştırma için yeni bir plan oluşturur ve sonra atar. Doğrudur, ama derleme maliyetini her seferinde ödersiniz; ağır ve seyrek çalışan sorgulara uygundur.OPTIMIZE FOR: planı temsilî bir değer için (OPTIMIZE FOR (@examId = 42)) ya da ortalama seçiciliğe göre (OPTIMIZE FOR UNKNOWN) oluşturur. Kararlıdır, ama ortalamadan uzak değerler için en iyi plan değildir.- Plan zorlama:
sp_query_store_force_plan, Query Store'un o sorgu için kaydettiği planlardan birini sabitler. Veri hacmi ve dağılımı değiştikçe yeniden gözden geçirilmesi gerekir. - Query Store hint'leri (SQL Server 2022 ve sonrası):
sys.sp_query_store_set_hints,OPTION(RECOMPILE)gibi bir hint'i sorgu metnini değiştirmeden uygular; bu da onu EF Core'un ürettiği SQL için temiz bir seçenek yapar. - PSP optimizasyonu (SQL Server 2022 ve sonrası): Parameter Sensitive Plan optimizasyonu,
160uyumluluk düzeyinde varsayılan olarak açıktır. Çarpık veri üzerindeki en fazla üç parametreli eşitlik koşulunu seçer ve parametre değerine göre sorgunun, her biri kendi planına sahip birkaç varyantını tutar. SQL Server 2025 bunu170düzeyinde DML ifadelerine genişletir. Geri yüklenmiş (restore) ya da attach edilmiş bir veritabanı önceki uyumluluk düzeyini korur; bu yüzden yalnızca sunucuyu yükseltmek yeterli değildir.
Kilitler ve beklemeler: blocking ve READ_COMMITTED_SNAPSHOT
Bazen CPU düşüktür ve istekler yine de yavaştır; bu, sorguların bir şeyi beklediği anlamına gelir. sys.dm_exec_requests içindeki blocking_session_id ve wait_type sütunları hangi oturumun hangisini beklediğini gösterir; LCK_M_* beklemeleri kilit demektir. SQL Server 2017'den beri Query Store bekleme istatistiklerini de sorgu başına tutar.
SQL Server'da READ_COMMITTED_SNAPSHOT varsayılan olarak kapalıdır ve READ COMMITTED paylaşımlı (shared) kilitlerle çalışır; bu yüzden okuyan, yazanın arkasında bekler. Seçenek açıkken her ifade, ifade başlamadan önce commit edilmiş en son veriyi satır sürümlerinden okur; okuyanlar ve yazanlar birbirini bloklamaz, ancak yazanlar yine yazanları bloklar. Azure SQL Database'de bu seçenek varsayılan olarak açıktır. ALTER DATABASE … SET READ_COMMITTED_SNAPSHOT ON çalıştırdığınızda veritabanında başka aktif bağlantı olmamalıdır, ancak tek kullanıcı (single-user) modu gerekmez.
Maliyeti: satır sürümleri tempdb'de, Accelerated Database Recovery (SQL Server 2019'dan beri mevcut) açıksa veritabanının kendisinde tutulur ve uzun süren bir transaction onları hayatta tutar. Okumaların semantiği de değişir. Kalan kontenjan gibi bir değeri okuyup ona göre yazan kod artık devam eden bir değişikliğin arkasında beklemez; önceki sürümü görür. Bu kalıp RCSI olmadan da güvenli değildi. Onu atomik yapın (UPDATE … SET Remaining = Remaining - 1 WHERE Id = @id AND Remaining > 0 ve etkilenen satır sayısını kontrol edin) ya da okumaya UPDLOCK ekleyin. Her durumda transaction'ları kısa tutun, bir transaction'ın ortasında dış servisleri çağırmayın ve UPDATE ile DELETE koşullarında kullanılan sütunları indeksleyin.
Hot path'lerde EF Core
EF Core'un kendi ek yükü çoğu uygulamada küçüktür; asıl maliyet, yazması kolay kalıplardan gelir. Bir test ortamında SQL loglamasını açın ve istek başına sorguları sayın. Ardından şunları kontrol edin:
- N+1: lazy loading ya da her öğe için bir sorgu çalıştıran bir döngü, tek bir isteği onlarca gidiş-dönüşe çevirir. İlişkili veriyi aynı sorguda
Includeya da bir projeksiyonla getirin. Selectile projeksiyon: yalnızca ihtiyacınız olan sütunları okuyun; böylece daha az veri taşınır ve kapsayan bir indeks mümkün hâle gelir. Hiç entity içermeyen bir sonuç hiç izlenmez (tracking).AsNoTracking: salt okunur, tam entity'ler için change tracker maliyetini kaldırır; ama identity resolution olmadığı için sonuçta birkaç kez görünen bir satır birkaç ayrı instance'a dönüşür.AsSplitQuery(): iki kardeş koleksiyonuIncludeettiğinizde Kartezyen patlamayı önler, ama daha fazla gidiş-dönüşe mal olur ve snapshot ya da serializable bir transaction olmadan sorgular arasındaki tutarlılık garanti edilmez.- Compiled query'ler ve pooling:
EF.CompileAsyncQueryveAddDbContextPoolEF'in ek yükünü azaltır, ama kazanç ağ ve veritabanı süresinin yanında küçüktür; onlara ancak ölçtükten sonra başvurun. - Sabitler yerine parametreler:
Expression.Constantile dinamik olarak ifade oluşturmak her değer için yeni bir SQL ve yeni bir plan üretir. EF Core 10'da bir liste üzerindekiContainsde plan sayısını sınırlı tutmak için varsayılan olarak padding'li birden çok ayrı parametreye çevrilir.
// Read path: only the needed columns; no entity in the result, so nothing is tracked
var answers = await db.Answers
.TagWith("GET /attempts/{id}/answers")
.Where(a => a.AttemptId == attemptId)
.Select(a => new AnswerDto(a.QuestionId, a.ChoiceId, a.UpdatedAt))
.ToListAsync(ct);
// One UPDATE statement (EF Core 7+), no entities loaded
var now = DateTime.UtcNow;
int expired = await db.Attempts
.Where(a => a.ExamId == examId
&& a.Status == AttemptStatus.InProgress
&& a.Deadline < now)
.ExecuteUpdateAsync(s => s
.SetProperty(a => a.Status, AttemptStatus.Expired)
.SetProperty(a => a.ClosedAt, now), ct);
İlk sorgu SQL'in başına bir yorum eklemek için TagWith kullanır; bu yorum loglarda ve sys.dm_exec_sql_text içindeki tam batch metninde görünür. Sonuç bir DTO olduğu için hiçbir şey izlenmez ve IX_Answers_AttemptId indeksi üç sütununu da kapsar. İkinci ifade, hiç entity yüklemeden tek bir UPDATE göndermek için ExecuteUpdateAsync (EF Core 7 ve sonrası) kullanır. Durum koşulu bir sabittir ve literal'e dönüşür; dolayısıyla InProgress değeri 0 ise optimizer yukarıdaki filtrelenmiş indeksi kullanabilir. Bu metot change tracker'ı atlar: aynı context'te daha önce yüklenmiş entity'ler güncellenmez ve kendi transaction'ını açmaz; bu yüzden aynı satırlar üzerinde onu SaveChanges ile karıştırmayın.
Küme tabanlı yazmalar ve SqlBulkCopy
Bir döngüde satır satır INSERT, satır başına bir gidiş-dönüş ve bir autocommit transaction demektir; varsayılan olan tam kalıcılıkta (full durability) her commit log yazmasını bekler. SaveChanges ifadeleri batch'ler hâlinde gönderir, ama binlerce satır için SqlBulkCopy daha uygundur: veriyi bir DataTable ya da IDataReader kaynağından bulk copy protokolü üzerinden tabloya yükler. Benim kalıbım, batch'i geçici bir tabloya yüklemek ve küme tabanlı tek bir ifadeyle ana tabloya birleştirmektir.
Büyük, Farsça bir çevrim içi eğitim platformunun izlenme süresi pipeline'ında, ingest servisinin (.NET 10 üzerinde bir minimal API) istek yolu olayı yalnızca sınırlı (bounded), bellek içi bir channel'a koyar ve 202 döndürür; channel doluysa 503. Bir arka plan servisi batch'leri geçici tablolara yükler ve tek bir transaction içinde küme tabanlı bir upsert çalıştırır; analiz sonuçları da MARS kapalı bir bağlantıda SqlBulkCopy ile yazılır. 12 çekirdekli bir iş istasyonunda, 10.000 olay ve 50 eşzamanlı kullanıcıyla yapılan bir Locust benchmark'ında servis saniyede yaklaşık 2.450 istek kabul etti; medyan 13 ms, 95. yüzdelik 21 ms idi ve bellek kullanımı en fazla yaklaşık 108 MB'a çıktı. Bu sayılar ingest yolunu ölçer ve tasarımın amacı da budur: istek SQL Server'ı beklemez. Ayrıntılar gerçek izlenme süresi üzerine notta. Kalıbın genel bir sürümü:
const string UpsertSql = """
UPDATE t SET t.Seconds = t.Seconds + b.Seconds
FROM dbo.WatchTotals AS t WITH (UPDLOCK, HOLDLOCK)
JOIN #Batch AS b ON b.UserId = t.UserId AND b.LessonId = t.LessonId;
INSERT dbo.WatchTotals (UserId, LessonId, Seconds)
SELECT b.UserId, b.LessonId, b.Seconds FROM #Batch AS b
WHERE NOT EXISTS (SELECT 1 FROM dbo.WatchTotals AS t WITH (UPDLOCK, HOLDLOCK)
WHERE t.UserId = b.UserId AND t.LessonId = b.LessonId);
""";
await using var conn = new SqlConnection(connectionString); // MARS is off by default
await conn.OpenAsync(ct);
await using var tx = (SqlTransaction)await conn.BeginTransactionAsync(ct);
await using (var create = new SqlCommand(
"CREATE TABLE #Batch (UserId int NOT NULL, LessonId int NOT NULL, Seconds int NOT NULL);",
conn, tx))
await create.ExecuteNonQueryAsync(ct);
using (var bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tx))
{
bulk.DestinationTableName = "#Batch";
await bulk.WriteToServerAsync(batch, ct); // DataTable or IDataReader, one row per key
}
await using (var upsert = new SqlCommand(UpsertSql, conn, tx))
await upsert.ExecuteNonQueryAsync(ct);
await tx.CommitAsync(ct);
Her şey tek bir bağlantıda çalışır, çünkü #Batch yalnızca o oturumda görünür; ayrıca SqlBulkCopy'ye de verilen tek bir transaction içinde çalışır (verilmezse hata alırsınız); böylece batch ya tamamen uygulanır ya da hiç uygulanmaz. Varsayım, batch'te her anahtar için tek satır olduğu ve (UserId, LessonId) çiftinin benzersiz bir anahtar olduğudur. UPDLOCK, HOLDLOCK eşzamanlı yazıcılar için oradadır: henüz var olmayan anahtarlarda bile aralık (range) kilitleri alır; böylece iki transaction aynı yeni anahtarı aynı anda ekleyip artışlardan birini kaybedemez. Bu modda deadlock mümkündür ve kurban (victim) transaction'ın yeniden denenmesi gerekir. Tek instance'lı bir yazıcıda çekişme olmaz. MARS, SqlClient'ta varsayılan olarak kapalıdır ve bu kodun ona ihtiyacı yoktur.
Tüm sıra tek cümlede: yük testiyle belirtiyi yeniden üretin, pahalı sorguyu bulun, gerçek planı okuyun, indeksi ya da sorguyu düzeltin, parameter sniffing'i ve beklemeleri kontrol edin ve son olarak aynı yük senaryosunu yeniden çalıştırın. API'niz zirvede yavaşlıyorsa ve bir de başka bir gözün bakmasını isterseniz Benimle çalışın sayfası nasıl yardımcı olabileceğimi anlatıyor.
Sık sorulan sorular
SQL Server'da yavaş bir sorguyu bulmanın ilk adımı nedir?
Query Store'u açın ve sorguları toplam CPU'ya ya da toplam süreye göre sıralayın; SQL Server 2022'den beri yeni veritabanlarında varsayılan olarak açıktır. Kullanılamıyorsa sys.dm_exec_query_stats, plan önbelleğindeki planlar için benzer istatistikler verir.
Parameter sniffing nedir?
SQL Server, parametreli bir sorgunun planını ilk çalıştırmadaki parametre değerine göre oluşturur ve sonraki değerler için yeniden kullanır. Veri dağılımı çarpıksa aynı plan bazı değerler için çok yavaş olabilir. Çözümler OPTION (RECOMPILE) ve OPTIMIZE FOR'dan Query Store'da plan zorlamaya ve SQL Server 2022 ve sonrasındaki PSP optimizasyonuna kadar uzanır.
READ_COMMITTED_SNAPSHOT'ı açmak güvenli mi?
Çoğu OLTP uygulaması için yaygın bir tercihtir ve Azure SQL Database'de varsayılandır; ancak okumaların semantiğini değiştirir ve satır sürümleri için alan gerektirir. Açmadan önce, okumaların kilitlerin arkasında beklemesine dayanan kodları gözden geçirin.
AsNoTracking'e her zaman ihtiyacım var mı?
Yalnızca okuma amacıyla tam entity'ler döndürdüğünüzde. Select ile bir DTO'ya projeksiyon yapıyorsanız ve sonuçta hiç entity yoksa EF Core hiçbir şeyi izlemez.
SaveChanges yerine SqlBulkCopy'yi ne zaman kullanmalıyım?
Olay batch'leri gibi binlerce satırı tek seferde yazdığınızda. Yaygın bir kalıp, batch'i SqlBulkCopy ile geçici bir tabloya yüklemek ve ardından küme tabanlı bir upsert ile ana tabloya birleştirmektir.