Plan Cache, Parameterization ve Parameter Sniffing: Belirtiler, Teşhis ve Çözümler

Sürüm notu: Bu yazıdaki T-SQL (Transact-SQL, Microsoft'un SQL (Structured Query Language) lehçesi) örnekleri SQL Server 2025 CU10 (17.0.5035.5, compatibility level 170) üzerinde, C# örnekleri .NET 10, Entity Framework Core (EF Core) 10.0.14, Microsoft.Data.SqlClient 7.1.1 ve Dapper 2.1.89 ile denenmiştir.

Önce Tahmin Et

Müşteri panelinin "siparişlerim" listesi basit bir stored procedure'dan geliyor:

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders @CustomerId int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderId, OrderNumber, Status, CreatedAt, TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    ORDER BY CreatedAt DESC;
END;

Müşteri 42'nin 473, müşteri 1'in (üniversite kütüphanesi) 200.000 siparişi var. Plan cache'i temizleyip procedure'ü önce 42, sonra 1 için çalıştırıyoruz; ardından aynı şeyi ters sırayla yapıyoruz. Bu deney için SQL Server 2022 ile gelen PSP (Parameter Sensitive Plan) optimization'ı kapattım, birazdan açacağız. Dört çalıştırmanın logical reads değerlerini tahmin et:

/* A: önce 42, sonra 1 */
Table 'Orders'. Scan count 1, logical reads 1460
Table 'Orders'. Scan count 1, logical reads 612850
/* B: önce 1, sonra 42 */
Table 'Orders'. Scan count 11, logical reads 7709
Table 'Orders'. Scan count 11, logical reads 7709

Aynı procedure, aynı müşteri 1: bir sırada 7.709, diğer sırada 612.850 logical read. Fark, procedure'ün ilk çalıştırıldığı değerden geliyor. "Sorgu bazen yavaş, bazen hızlı, sunucuyu restart edince düzeliyor" şikâyetinin klasik sebebi bu: parameter sniffing. Bu yazıda plan cache'in nasıl çalıştığını, parameterization türlerini, parameter sniffing'in teşhisini, çözümlerini ve her birinin bedelini, SQL Server 2022'nin PSP optimization'ını ve 2025'in OPPO'sunu (Optional Parameter Plan Optimization) inceliyoruz.

Plan Cache: Ad Hoc, Prepared ve Procedure

Birinci yazıda gördük: compile pahalı olduğu için SQL Server derlediği planları cache'ler ve aynı sorgu geldiğinde yeniden kullanır. "Aynı sorgu" ise metnin byte byte aynı olması demek. Aynı join'i üç farklı literal'le, sonra sp_executesql ile iki kez gönderdim ve veritabanının plan cache'ine baktım:

SELECT o.OrderNumber, c.Email FROM dbo.Orders AS o JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId WHERE o.OrderId = 42;
/* aynısı 43 ve 44 ile */
SELECT OrderNumber FROM dbo.Orders WHERE OrderId = 42;
SELECT OrderNumber FROM dbo.Orders WHERE OrderId = 43;
EXEC sp_executesql N'SELECT o.OrderNumber, c.Email FROM dbo.Orders AS o JOIN dbo.Customers AS c
    ON c.CustomerId = o.CustomerId WHERE o.OrderId = @OrderId', N'@OrderId int', 42;  /* ve 43 */

objtype  cacheobjtype  usecounts size_in_bytes text
-------- ------------- --------- ------------- ----------------------------------------------------------------------
Adhoc    Compiled Plan 1         73728         SELECT o.OrderNumber, c.Email FROM dbo.Orders AS o JOIN dbo.Customers
Adhoc    Compiled Plan 1         73728         SELECT o.OrderNumber, c.Email FROM dbo.Orders AS o JOIN dbo.Customers
Adhoc    Compiled Plan 1         73728         SELECT o.OrderNumber, c.Email FROM dbo.Orders AS o JOIN dbo.Customers
Adhoc    Compiled Plan 1         16384         SELECT OrderNumber FROM dbo.Orders WHERE OrderId = 42;
Adhoc    Compiled Plan 1         16384         SELECT OrderNumber FROM dbo.Orders WHERE OrderId = 43;
Prepared Compiled Plan 2         49152         (@1 tinyint)SELECT [OrderNumber] FROM [dbo].[Orders] WHERE [OrderId]=@
Prepared Compiled Plan 2         73728         (@OrderId int)SELECT o.OrderNumber, c.Email FROM dbo.Orders AS o JOIN

Join'li sorgu literal'le gönderildiğinde üç ayrı ad hoc plan oldu, her biri 72 KB ve her biri yalnızca bir kez kullanıldı. Tek tablolu basit sorguyu ise SQL Server kendisi parametreleştirdi (simple parameterization): (@1 tinyint) ile bir prepared plan oluştu ve iki çağrı onu paylaştı; ad hoc satırlar yalnızca 16 KB'lık "shell" girişleri. Simple parameterization yalnızca planı değerden bağımsız olarak güvenli olan çok basit sorgulara uygulanır; join, IN listesi ya da alt sorgu gördüğünde devreye girmez. sp_executesql ile açıkça parametre verilen sorgu ise doğal olarak tek plan. EF Core ve Dapper sorguları bu yüzden plan cache dostudur: değerleri hep parametre olarak gönderirler.

Forced Parameterization ve Optimize for Ad Hoc Workloads

Literal'lerle sorgu üreten eski bir uygulamayı değiştiremiyorsan iki veritabanı seviyesi ayar var. İlki forced parameterization:

ALTER DATABASE CURRENT SET PARAMETERIZATION FORCED;
/* aynı üç join sorgusu, literal 42, 43, 44 */

objtype  cacheobjtype  usecounts size_in_bytes text
-------- ------------- --------- ------------- ----------------------------------------------------------------------
Adhoc    Compiled Plan 1         16384         SELECT o.OrderNumber, c.Email FROM dbo.Orders AS o JOIN dbo.Customers
Adhoc    Compiled Plan 1         16384         SELECT o.OrderNumber, c.Email FROM dbo.Orders AS o JOIN dbo.Customers
Adhoc    Compiled Plan 1         16384         SELECT o.OrderNumber, c.Email FROM dbo.Orders AS o JOIN dbo.Customers
Prepared Compiled Plan 3         73728         (@0 int)select o . OrderNumber , c . Email from dbo . Orders as o join

Artık literal'ler parametreye çevrildi ve üç çağrı tek planı paylaştı. Bedeli: bütün literal'ler parametre olduğu için parameter sniffing'e açık sorgu sayısı da artar; ayrıca filtered index ve computed column eşleştirmesi gibi literal'e dayanan optimizasyonlar kaybolabilir. İkincisi, ad hoc planların ilk çalıştırmada yalnızca küçük bir "stub" olarak saklanması. Sunucu seviyesinde optimize for ad hoc workloads olarak bilinir; paylaşılan sunucuya dokunmamak için veritabanı seviyesindeki karşılığını kullandım:

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;
/* OrderId = 45 iki kez, OrderId = 46 bir kez */

objtype cacheobjtype       usecounts size_in_bytes
------- ------------------ --------- -------------
Adhoc   Compiled Plan Stub 1         456
Adhoc   Compiled Plan      1         73728

Bir kez çalışan sorgu (46) cache'te 72 KB yerine 456 byte tuttu; ikinci kez gelen sorgu (45) tam plana terfi etti. Tek seferlik sorgu üreten iş yüklerinde plan cache belleğini korumanın ucuz bir yolu.

Parameter Sniffing: Neden Olur?

Stored procedure'ün ya da sp_executesql'in ilk çalıştırmasında optimizer parametrenin o anki değerine bakar (sniff eder) ve histogramı o değerle kullanır. Müşteri 42 için 487 satır tahmin edip IX_Orders_CustomerId üzerinde seek + key lookup içeren bir plan kurdu: 473 satır için 1.460 logical read, mükemmel. Ama plan cache'e girdi ve müşteri 1 geldiğinde aynı plan çalıştı: 200.000 key lookup, 612.850 logical read. Ters sırada müşteri 1'e göre paralel clustered index scan kuruldu ve müşteri 42 de 473 satır için bütün tabloyu taradı.

Sniffing kötü bir şey değildir; çoğu sorguda doğru planın sebebidir. Değişkenlerle karşılaştır: dördüncü yazıda DECLARE edilen değişken optimizer'a yalnızca ortalamayı (10,2 satır) bırakıyordu. Sorun, veri çarpık olduğunda tek bir planın bütün değerlere uymaması. Teşhis için iki işaret yeterli: aynı sorgunun cache'teki planında derleme anındaki değer (ParameterCompiledValue) ile çalıştırma anındaki değer (ParameterRuntimeValue) farklı, ve sorgunun sys.dm_exec_query_stats'taki min_logical_reads ile max_logical_reads değerleri arasında büyük bir uçurum var.

Çözümler ve Her Birinin Bedeli

Procedure'ün sorgusuna farklı hint'ler ekleyip her seferinde cache'i temizledim ve 42, 1, 42 sırasıyla çalıştırdım (PSP kapalı):

SELECT OrderId, OrderNumber, Status, CreatedAt, TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId
ORDER BY CreatedAt DESC
OPTION (RECOMPILE);   /* ya da OPTIMIZE FOR (@CustomerId = 42), OPTIMIZE FOR UNKNOWN */

OPTION (RECOMPILE) her çağrıda o değere en uygun planı verdi; bedeli her çağrıda compile CPU'su (Central Processing Unit) ve planın cache'te görünmemesi. Saniyede yüzlerce kez çalışan bir sorgu için pahalı, dakikada birkaç kez çalışan bir arama ekranı için çoğu zaman en doğru çözüm. OPTIMIZE FOR belirli bir değer için planı sabitler: küçük müşteriler hep iyi, büyük müşteri hep kötü. OPTIMIZE FOR UNKNOWN değeri yok sayıp density vector'ün ortalamasını (10,2 satır) kullanır; bizim çarpık verimizde müşteri 1 için yine felaket. Bu iki hint "tutarlılık" satın alır, doğruluk değil. Uygulama tarafında bir başka seçenek de büyük müşterileri ayrı bir procedure'e ya da ayrı bir sorguya yönlendirmek; veriyi tanıyorsan en sağlam çözüm budur.

PSP Optimization: Tek Sorgu, Birden Çok Plan

SQL Server 2022 (compatibility level 160) ile gelen PSP optimization tam bu sorun için tasarlandı ve level 160 ve üstünde varsayılan açık. Tablonun son satırı onun sonucu: aynı procedure, hint'siz, üç çağrıda da doğru plan. Nasıl yaptığını Query Store'dan görebiliriz:

SELECT qv.query_variant_query_id, qv.parent_query_id, qv.dispatcher_plan_id, p.plan_id,
       SUBSTRING(qt.query_sql_text, CHARINDEX('option (PLAN PER VALUE', qt.query_sql_text), 200) AS variant_hint
FROM sys.query_store_query_variant AS qv
JOIN sys.query_store_query AS q ON q.query_id = qv.query_variant_query_id
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id;

61 57 32 33 option (PLAN PER VALUE(ObjectID = 1653580929, QueryVariantID = 2, predicate_range([sqls_07].[dbo].[Orders].[CustomerId] = @CustomerId, 100.0, 100000.0)))
62 57 32 34 option (PLAN PER VALUE(ObjectID = 1653580929, QueryVariantID = 3, predicate_range([sqls_07].[dbo].[Orders].[CustomerId] = @CustomerId, 100.0, 100000.0)))

<ParameterSensitivePredicate LowBoundary="100" HighBoundary="100000">
  <StatisticsInfo Statistics="[IX_Orders_CustomerId]" Table="[Orders]" ... />

PSP, histogramda çarpıklık gördüğü CustomerId = @CustomerId koşulu için bir dispatcher plan oluşturdu. Dispatcher, parametrenin histogramdaki tahminine göre değeri üç kovadan birine koyuyor: 100 satırın altı, 100 ile 100.000 arası ve 100.000'in üstü. Her kova kendi planını (query variant) ilk geldiği değerle derliyor. Müşteri 42 (473 satır) ikinci kovaya, müşteri 1 (200.000 satır) üçüncü kovaya düştü ve iki ayrı plan oluştu. Bunu üçüncü yazıdaki planlarda gördüğümüz PLAN PER VALUE eklerinden de tanıyorsun.

Sınırları da bilmek gerekiyor: PSP yalnızca eşitlik koşullarına ve bir sorguda en fazla üç koşula bakar; kova içindeki sniffing devam eder (aynı kovadaki 120 ve 90.000 satırlık iki müşteri yine tek planı paylaşır). Yine de çarpık dağılımlı OLTP (Online Transaction Processing) sorgularında kod değiştirmeden gelen ciddi bir iyileşme.

OPPO: @p IS NULL OR col = @p Kalıbı

Arama ekranlarında sık görülen "catch-all" sorgu, parameter sniffing'in özel bir türüdür:

CREATE OR ALTER PROCEDURE dbo.SearchOrders @OrderNumber varchar(20) = NULL, @CustomerId int = NULL
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderId, OrderNumber, CustomerId, Status, TotalAmount
    FROM dbo.Orders
    WHERE (@OrderNumber IS NULL OR OrderNumber = @OrderNumber)
      AND (@CustomerId IS NULL OR CustomerId = @CustomerId);
END;

Sorun sniffing'den derin: @OrderNumber NULL olabileceği için OrderNumber = @OrderNumber üzerinde seek yapan bir plan her değer için doğru olamaz, optimizer da tek planla güvenli tarafta kalıp index'i baştan sona tarar. SQL Server 2025'in OPPO'su (compatibility level 170'te varsayılan açık) PSP'nin altyapısını kullanarak parametrenin NULL olup olmamasına göre ayrı query variant'lar oluşturur ve her variant'ta @p IS NULL ifadesini sabite indirger. Aynı iki çağrıyı OPPO kapalı ve açıkken çalıştırdım:

EXEC dbo.SearchOrders @OrderNumber = 'BK-00424242';
EXEC dbo.SearchOrders @CustomerId = 42;

/* OPTIONAL_PARAMETER_OPTIMIZATION = OFF */
Table 'Orders'. Scan count 1, logical reads 3117
Table 'Orders'. Scan count 1, logical reads 3205
/* OPTIONAL_PARAMETER_OPTIMIZATION = ON */
Table 'Orders'. Scan count 0, logical reads 6
Table 'Orders'. Scan count 1, logical reads 3205

option (PLAN PER VALUE(ObjectID = 1685581043, QueryVariantID = 1, optional_predicate(@OrderNumber IS NULL),
        predicate_range([sqls_07].[dbo].[Orders].[CustomerId] = @CustomerId, 100.0, 100000.0)))

Sipariş numarasıyla arama 3.117'den 6 logical read'e indi: variant'ta OrderNumber üzerinde gerçek bir seek var. Müşteriyle arama ise değişmedi. Variant metni sebebini gösteriyor: OPPO yalnızca @OrderNumber'ı optional predicate olarak ele aldı, CustomerId koşulunu PSP'nin aralık kovasına bıraktı ve @CustomerId IS NULL OR ... ifadesi yerinde kaldı; plan IX_Orders_CustomerId'yi tarayıp her satırda koşulu değerlendirdi. Yani OPPO her optional parametreyi ayırmayabilir; birden çok optional parametresi olan sorgularda klasik çözümler (OPTION (RECOMPILE) ya da yalnızca dolu parametrelerle dinamik SQL üretmek) hâlâ gerekli olabilir. Güvenli dinamik SQL'i stored procedure yazısında ele alacağız.

ORM Parametre Uzunluğu ve Plan Cache Şişmesi

Parametre tanımı plan cache anahtarının bir parçasıdır. nvarchar(21) ile nvarchar(22) farklı tanımlardır ve farklı planlar demektir. ActiveX Data Objects for .NET'in (ADO.NET) AddWithValue'su uzunluğu değerin kendisinden alır:

string[] emails = ["customer7@example.com", "customer42@example.com", "customer4242@example.com"];

foreach (string email in emails)
{
    await using var cmd = new SqlCommand(
        "SELECT CustomerId FROM dbo.Customers WHERE Email = @email /* addwithvalue */", conn);
    cmd.Parameters.AddWithValue("@email", email);
    await cmd.ExecuteScalarAsync();
}

/* EF Core, modelde HasMaxLength(200).IsUnicode(false) */
foreach (string email in emails)
    await ctx.Customers.Where(c => c.Email == email).Select(c => c.CustomerId).SingleAsync();

1x (@email nvarchar(21)
1x (@email nvarchar(22)
1x (@email nvarchar(24)
3x (@email varchar(200)

Üç e-posta, AddWithValue ile üç ayrı plan; üstelik nvarchar oldukları için altıncı yazıdaki implicit conversion da cabası. EF Core modeldeki HasMaxLength(200) ve IsUnicode(false) bilgisini kullanıp her çağrıda varchar(200) gönderdi ve tek plan üç kez kullanıldı. Modelde uzunluk yoksa EF Core nvarchar(4000) ya da nvarchar(max) gönderir; Dapper'ın kuralı da benzerdir (Dapper serisinin 13. yazısı). Gerçek bir uygulamada binlerce farklı uzunluk binlerce plan, yani hem bellek hem compile CPU'su demek.

SQL Server 2019/2022'de farkı: plan cache, simple/forced parameterization ve sniffing davranışı üç sürümde aynı. Veritabanı seviyesindeki OPTIMIZE_FOR_AD_HOC_WORKLOADS 2019'dan beri var. PSP optimization 2022'de compatibility level 160 ile geldi; 2019'da yok. OPPO yalnızca 2025 ve level 170'te. Edition tablosuna göre PSP ve OPPO Standard ve Express dahil bütün edition'larda var.

Üretimde Dikkat

Teşhisi Query Store ile yap. Aynı sorgunun farklı planlarla farklı maliyette çalıştığını, hangi planın ne zaman geldiğini Query Store'un "Regressed Queries" raporu gösterir; sekizinci yazının konusu. Plan cache'teki min_logical_reads ve max_logical_reads farkı da hızlı bir işaret.

"Restart edince düzeldi" ya da "index rebuild yapınca düzeldi" cümlelerine dikkat et. Her ikisi de planı cache'ten atar ve bir sonraki çağrı yeni bir değerle compile olur. Sorun çözülmedi, yalnızca bu sefer şanslı bir değer önce geldi.

Plan cache'i production'da topluca temizleme. Tek bir kötü planı DBCC FREEPROCCACHE (plan_handle) ile ya da Query Store'da plan zorlayarak hedefli şekilde çöz.

Sık Düşülen Tuzaklar

Parameter sniffing'i sunucu yükü sanmak. "Sorgu bazen yavaş" şikâyetinde önce plan cache'teki planın derlendiği değere bak; CPU ya da disk yükü her zaman sebep değil, bazen sonuçtur.

Sorunu değişkene kopyalayarak "çözmek". Parametreyi procedure içinde bir değişkene atayıp sorguda onu kullanmak OPTIMIZE FOR UNKNOWN ile aynı etkiyi verir: tahmin ortalamaya düşer. Bizim veride bu, müşteri 1 için 600.350 logical read demekti.

Her sorguya OPTION (RECOMPILE) eklemek. Az çalışan sorgularda doğru, sık çalışan sorgularda compile CPU'su ve plan cache'te görünmeyen planlar yüzünden teşhisi zorlaştıran bir çözüm.

ORM'de (Object-Relational Mapping) string uzunluklarını modele yazmamak. Uzunluk ve Unicode bilgisi eksik model, hem plan cache şişmesine hem implicit conversion'a yol açar.

Küçük Bir Deneme

★☆☆ PSP açıkken procedure'ü müşteri 60 (389 sipariş) ve müşteri 300 (122 sipariş) için çalıştır. İkisi aynı variant'ı mı kullandı? Query Store'daki variant sayısı değişti mi?

★★☆ sys.dm_exec_query_stats'tan GetCustomerOrders sorgusunun min_logical_reads ve max_logical_reads değerlerini oku ve cache'teki planın XML'inde (Extensible Markup Language) ParameterCompiledValue'yu bul.

★★★ SearchOrders'ı yalnızca dolu parametreler için WHERE koşulu ekleyen, sp_executesql ile parametreli dinamik SQL'e çevir. Aynı iki çağrının logical reads'ini ve plan cache'te oluşan planları OPPO'lu hâliyle karşılaştır.

Bu yazıdaki teşhisin çoğu plan cache'e dayanıyordu ve plan cache uçucu: restart'ta, bellek baskısında, rebuild'de silinir. Bir sonraki yazıda kalıcı olanı açıyoruz: Query Store ile regresyon bulmak, plan zorlamak ve kod değiştirmeden hint vermek.