Execution Plan Okumak 2: Memory Grant, Spill, Parallelism ve Implicit Conversion Uyarıları

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, Enterprise Developer Edition, compatibility level 170) üzerinde, C# örnekleri .NET 10, Entity Framework Core (EF Core) 10.0.14, Dapper 2.1.89 ve Microsoft.Data.SqlClient 7.1.1 ile denenmiştir.

Önce Tahmin Et

Bir rapor script'i 1 numaralı müşterinin siparişlerini tutara göre sıralıyor. Müşteri numarası bir değişkende:

DECLARE @c int = 1;
SELECT OrderId, OrderNumber, CreatedAt, TotalAmount
FROM dbo.Orders WHERE CustomerId = @c
ORDER BY TotalAmount DESC
OPTION (MAXDOP 1);

Dördüncü yazıdan biliyorsun: değişken yüzünden optimizer 10,2 satır bekliyor, gelen ise 200.000. Sıralama için bellek gerekiyor ve bu bellek sorgu başlamadan ayrılıyor. Optimizer ne kadar bellek istedi, 200.000 satır o belleğe sığdı mı? Actual plan'ın XML'inden (Extensible Markup Language) okunan değerler:

<MemoryGrantInfo RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantedMemory="1024"
                 MaxUsedMemory="1024" IsMemoryGrantFeedbackAdjusted="No: First Execution">
<Warnings>
  <SpillToTempDb SpillLevel="2" SpilledThreadCount="1"></SpillToTempDb>
  <SortSpillDetails GrantedMemoryKb="1024" UsedMemoryKb="1024" WritesToTempDb="1597" ReadsFromTempDb="1597"></SortSpillDetails>
</Warnings>

1 MB istendi, 1 MB verildi, hepsi kullanıldı ve sıralama tempdb'ye taştı: 1.597 page yazıldı, aynı sayıda geri okundu. Bu yazıda planın "uyarı" katmanını okuyoruz: memory grant ve spill, memory grant feedback, paralel planlar ve exchange operator'leri, DOP (degree of parallelism) feedback, implicit conversion ve batch mode.

Memory Grant: Sorgu Başlamadan Ayrılan Bellek

Sort, Hash Match (join ve aggregate) gibi operator'ler çalışmak için bellek ister. SQL Server bu belleği sorgu başlamadan, tahmini satır sayısı ve satır genişliğinden hesaplar ve sorguya bir memory grant olarak ayırır. Grant çalışma sırasında büyümez. Tahmin küçükse ayrılan bellek yetmez ve operator verinin bir kısmını tempdb'ye yazıp geri okur (spill). Tahmin büyükse tersine bir israf olur: ayrılan bellek kullanılmasa da başka sorgular onu alamaz. Bunun da planda bir uyarısı var. Önceki yazıdaki EF Core Include sorgusunun planında şu satır duruyordu:

<MemoryGrantWarning GrantWarningKind="Excessive Grant" RequestedMemory="1024" GrantedMemory="1024" MaxUsedMemory="16" />

Tek bir sorgu için 1 MB önemsiz. Ama aynı anda yüzlerce sorgu fazla bellek isterse, grant'ler sunucunun query memory'sini tüketir ve yeni sorgular RESOURCE_SEMAPHORE wait'inde grant bekler. Teşhis yazısında bu wait'e döneceğiz. Grant'in boyutunu planın kökündeki MemoryGrantInfo'da, o an çalışan sorgularınkini sys.dm_exec_query_memory_grants'ta görürsün.

Spill Uyarısı ve Memory Grant Feedback

Açılıştaki sorguyu değiştirmeden üç kez çalıştırdım. Plan cache'ten aynı plan geldi, ama memory grant değişti:

run 1: RequestedMemory=1024   MaxUsedMemory=1024   IsMemoryGrantFeedbackAdjusted="No: First Execution"   + SortSpillDetails
run 2: RequestedMemory=21992  MaxUsedMemory=15192  IsMemoryGrantFeedbackAdjusted="Yes: Adjusting"
run 3: RequestedMemory=21992  MaxUsedMemory=15192  IsMemoryGrantFeedbackAdjusted="Yes: Stable"

Bu, memory grant feedback. İlk çalıştırmadaki spill'i gören SQL Server, cache'teki planın grant bilgisini güncelledi; ikinci çalıştırma 21.992 KB aldı ve spill olmadı, üçüncüde ayar "stable" oldu. Tahmin hâlâ 10,2 satır; değişen yalnızca grant. Özellik aşamalarla geldi: batch mode için SQL Server 2017 (compatibility level 140), row mode için 2019 (level 150), 2022'de ise percentile grant (son çalıştırmalara göre değil, geçmişin dağılımına göre ayar) ve Query Store'da kalıcılık (plan cache'ten düşen plan ayarını kaybetmesin diye). Bu sunucuda hepsi açık:

SELECT name, value FROM sys.database_scoped_configurations
WHERE name IN ('ROW_MODE_MEMORY_GRANT_FEEDBACK', 'BATCH_MODE_MEMORY_GRANT_FEEDBACK',
               'MEMORY_GRANT_FEEDBACK_PERSISTENCE', 'MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT',
               'DOP_FEEDBACK', 'BATCH_MODE_ON_ROWSTORE');

BATCH_MODE_MEMORY_GRANT_FEEDBACK 1
ROW_MODE_MEMORY_GRANT_FEEDBACK 1
BATCH_MODE_ON_ROWSTORE 1
MEMORY_GRANT_FEEDBACK_PERSISTENCE 1
MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT 1
DOP_FEEDBACK 1

İki uyarı var. Birincisi, edition tablosuna göre row mode memory grant feedback, percentile ve persistence yalnızca Enterprise'da; Standard'da bu sorgu her seferinde spill eder. İkincisi, feedback semptomu tedavi eder, sebebi değil. Asıl sorun değişkenin histogramı devre dışı bırakması; OPTION (RECOMPILE) ya da parametreli bir stored procedure bu sorguya doğru tahmini verir. Ayrıca plan 200.000 lookup ile 600.000 logical read yapıyor; spill'den çok daha büyük bir maliyet bu.

Paralel Plan: MAXDOP, Cost Threshold ve Exchange Operator'leri

Bir sorgunun paralel çalışıp çalışmayacağını iki ayar belirler. cost threshold for parallelism: seri planın tahmini maliyeti bu değeri aşarsa optimizer paralel plan da değerlendirir. max degree of parallelism (MAXDOP): paralel bir planın en fazla kaç thread kullanacağı. Bu paylaşılan sunucuda ikisi de varsayılan değerinde:

cost threshold for parallelism 5
max degree of parallelism      0

MAXDOP 0 "bütün scheduler'lar" demek; burada 10. 5 ise SQL Server'ın çok eski sürümlerinden kalma ve bugünün donanımında çok düşük bir eşik: tahmini maliyeti 5,99 olan basit bir gruplama sorgusu 10 thread'le çalıştı:

SELECT Status, COUNT(*) AS Orders, SUM(TotalAmount) AS Revenue
FROM dbo.Orders WHERE CreatedAt >= '2026-01-01'
GROUP BY Status;

StatementSubTreeCost="5.98775"  DegreeOfParallelism="10"
Parallelism                 actRows=5       mode=Row
  Compute Scalar            actRows=5       mode=Batch
    Hash Match              actRows=5       mode=Batch
      Clustered Index Scan  actRows=210880  reads=7705  mode=Batch

Her ikisi de server seviyesi ayar; bu seride server ayarına dokunmuyoruz. Ama MAXDOP veritabanı seviyesinde de verilebilir ve çoğu zaman doğru yer orasıdır: ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2; sonrasında aynı veritabanındaki sorgular DegreeOfParallelism="2" ile çalıştı. Microsoft'un MAXDOP rehberi NUMA (Non-Uniform Memory Access) düğümü başına çekirdek sayısına göre bir üst sınır önerir; cost threshold için kesin bir değer vermez, kendi iş yükünde ölçmeni ister. Topluluk pratiğinde OLTP (Online Transaction Processing) sistemlerinde 5 yerine 25–50 gibi değerlerle başlamak yaygındır.

Paralel planı row mode'da okumak için batch mode'u kapatıp ülke bazında bir join yaptım:

SELECT c.Country, COUNT(*) AS Orders, SUM(o.TotalAmount) AS Revenue
FROM dbo.Orders AS o JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId
WHERE o.CreatedAt >= '2026-01-01'
GROUP BY c.Country
OPTION (USE HINT('DISALLOW_BATCH_MODE'));

    Rows Executes
       5        1  |--Stream Aggregate(GROUP BY:([c].[Country]) DEFINE:([globalagg1005]=SUM([partialagg1004]), ...))
      50        1       |--Sort(ORDER BY:([c].[Country] ASC))
      50        1            |--Parallelism(Gather Streams)
      50       10                 |--Hash Match(Partial Aggregate, HASH:([c].[Country]) ...)
  210880       10                      |--Hash Match(Inner Join, HASH:([c].[CustomerId])=([o].[CustomerId]))
  100000       10                           |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([c].[CustomerId]))
  100000       10                           |    |--Clustered Index Scan(OBJECT:([Customers].[PK_Customers] AS [c]))
  210880       10                           |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([o].[CustomerId]))
  210880       10                                |--Clustered Index Scan(OBJECT:([Orders].[PK_Orders] AS [o]), WHERE:(...))

Parallelism operator'leri (exchange'ler) thread'ler arasında satır taşır. Repartition Streams satırları join key'inin hash'ine göre yeniden dağıtır, böylece aynı CustomerId'ye sahip sipariş ve müşteri aynı thread'e düşer. Her thread kendi payı için partial aggregate yapar (10 thread × 5 ülke = 50 satır), Gather Streams bunları tek thread'e toplar ve global aggregate son toplamı verir. Thread'ler birbirini beklerken zaman CX ile başlayan wait'lere yazılır. Bu sorgulardan sonra oturumun wait'leri:

SELECT wait_type, waiting_tasks_count
FROM sys.dm_exec_session_wait_stats
WHERE session_id = @@SPID AND wait_type LIKE 'CX%';

CXPACKET        13
CXSYNC_CONSUMER 9
CXSYNC_PORT     33
CXCONSUMER      387
CXROWSET_SYNC   7

CXCONSUMER tüketici thread'in üreticiden satır beklemesidir ve paralel planın doğal bir parçasıdır; tek başına bir sorun değildir. CXPACKET ise SQL Server 2017 CU3'ten (Cumulative Update 3) beri üretici tarafındaki beklemeyi ve thread'ler arası dengesizliği gösterir; yüksekse veri thread'lere dengesiz dağılmış ya da tahminler yanlış olabilir.

DOP Feedback: 2025'te Varsayılan Açık

Paralellik raporlama sorgusunda iyi, sık çalışan küçük bir OLTP sorgusunda ise thread koordinasyonunun maliyeti kazancı aşabilir. SQL Server 2022 ile gelen DOP feedback, tekrar eden sorgular için geçen süreyi ve wait'leri izler; paralellik verimsiz görünüyorsa bir sonraki çalıştırmada DOP'u düşürür ve işe yarayıp yaramadığını doğrular. İşe yarayan değer Query Store'a kalıcı olarak yazılır, kötüleşirse son iyi değere döner. Microsoft'un dokümanına göre en düşük DOP 2'dir, seri çalıştırma kapsam dışıdır, plan yeniden compile edilmez ve özellik query hint'leriyle birlikte çalışmaz. Compatibility level 160 ve READ_WRITE modunda Query Store ister. 2022'de varsayılan kapalıydı; SQL Server 2025'te varsayılan açık, yukarıdaki listede de DOP_FEEDBACK 1 görünüyor. Bu özellik süreye dayandığı için emülasyonla çalışan bu container'da deterministik bir deney göstermiyorum; sonuçlarını sys.query_store_plan_feedback view'ında görebilirsin.

Implicit Conversion: nvarchar Parameter, varchar Kolon

Planlardaki en sinsi uyarı PlanAffectingConvert. Orders.OrderNumber varchar(20). .NET string'leri Unicode olduğu için EF Core ve Dapper, aksini söylemezsen parametreyi nvarchar gönderir. SQL Server iki tarafı karşılaştırmak için data type precedence gereği kolonu nvarchar'a çevirir. Tek bir siparişi numarasıyla dört farklı şekilde aradım; logical reads ve uyarıyı plan cache'ten okudum:

string orderNumber = "BK-00424242";

/* EF Core: varsayılan eşleme ve IsUnicode(false) */
var order = await ctx.Orders.Where(o => o.OrderNumber == orderNumber).SingleAsync();

/* Dapper: düz string ve DbString */
var a = await conn.QuerySingleAsync<int>(
    "SELECT OrderId FROM dbo.Orders WHERE OrderNumber = @orderNumber",
    new { orderNumber });
var b = await conn.QuerySingleAsync<int>(
    "SELECT OrderId FROM dbo.Orders WHERE OrderNumber = @orderNumber",
    new { orderNumber = new DbString { Value = orderNumber, IsAnsi = true, Length = 20 } });

EF Core, IsUnicode(false)=False: 424242 -> (@orderNumber nvarchar(20) logical reads 3117, PlanAffectingConvert: yes
EF Core, IsUnicode(false)=True: 424242 -> (@orderNumber varchar(20) logical reads 6, PlanAffectingConvert: no
Dapper string: 424242 -> (@orderNumber nvarchar(4000) logical reads 3114, PlanAffectingConvert: yes
Dapper DbString: 424242 -> (@orderNumber varchar(20) logical reads 3, PlanAffectingConvert: no

Unique index'li bir kolonda tek satır aramak 3–6 page yerine 3.114–3.117 page tuttu: UX_Orders_OrderNumber'ın tamamı tarandı ve her satırda CONVERT_IMPLICIT çalıştı. Bunun sebebi sunucunun collation'ı. Bu container SQL_Latin1_General_CP1_CI_AS ile kurulu ve SQL collation'larında varchar ile nvarchar'ın sıralama kuralları farklı olduğu için optimizer dönüşümü bir aralık aramasına çeviremiyor. Aynı veriyi Latin1_General_CI_AS (Windows collation) kolonlu bir tabloya kopyalayıp aynı nvarchar(4000) parametresiyle denedim:

Table 'OrderNumbersWin'. Scan count 1, logical reads 3
  |--Compute Scalar(DEFINE:(([Expr1005],[Expr1006],[Expr1004])=GetRangeThroughConvert(CONVERT_IMPLICIT(nvarchar(4000),[@n],0), ...)))
  |--Index Seek(OBJECT:([OrderNumbersWin].[UX_OrderNumbersWin]), SEEK:([OrderNumbersWin].[OrderNumber] > [Expr1005] AND ...)

Windows collation'da optimizer GetRangeThroughConvert ile dönüşümü bir aralığa çevirip seek yapabildi. Yine de plan daha karmaşık ve tahminler bozulabilir; doğru çözüm iki collation'da da aynı: parametre tipini kolon tipine eşitlemek. EF Core'da modelde IsUnicode(false) (EF Core zaten Size = 20 ile tutarlı uzunluk gönderiyor), Dapper'da DbString ya da global type map. Dapper tarafının ayrıntıları ve 4000 karakter kuralı Dapper serisinin 13. yazısında var. Ters yön zararsızdır: nvarchar kolona varchar parametre gönderirsen dönüştürülen parametre olur, kolon değil.

Batch Mode on Rowstore ve Adaptive Join

Yukarıdaki gruplama planında operator'lerin yanında mode=Batch yazıyordu. Batch mode, satırları tek tek değil, yüzlerce satırlık vektörler hâlinde işler ve CPU (Central Processing Unit) maliyetini büyük sorgularda belirgin şekilde düşürür. Eskiden yalnızca columnstore index'lerle mümkündü; SQL Server 2019'dan beri compatibility level 150 ve üstünde, normal rowstore tablolarda da optimizer uygun gördüğünde (büyük tarama, join ve aggregate) devreye giriyor. Planda her operator'ün EstimatedExecutionMode ve ActualExecutionMode değerlerine bakarak tanırsın.

Önceki yazıda hint'siz join planında gördüğümüz Adaptive Join da batch mode'a ait. XML'de şu değerler var:

IsAdaptive="1"  AdaptiveThresholdRows="5029.8"  EstimatedJoinType="Hash Match"  ActualJoinType="Hash Match"
Adaptive Join                  actRows=100000  mode=Batch
  Hash Match (Aggregate)       actRows=100000  mode=Batch
    Clustered Index Scan       actRows=249535  reads=9902  mode=Batch   [PK_OrderItems]
  Clustered Index Seek         actRows=100000  reads=758   mode=Batch   [PK_Orders, range]
  Clustered Index Seek         actRows=0       execs=0     mode=Row     [PK_Orders, nested loops branch]

Operator build tarafını okur ve satır sayısını eşikle (5.029,8) karşılaştırır: eşiğin üstündeyse hash join, altındaysa nested loops dalını çalıştırır. Burada 100.000 satır geldi, hash join seçildi ve nested loops dalı hiç çalışmadı (execs=0). Tahminin yanlış olabileceği sorgularda bu, yanlış join seçiminin bedelini sınırlar.

SQL Server 2019/2022'de farkı: memory grant feedback row mode için 2019 (level 150), percentile ve persistence 2022 ile geldi. DOP feedback 2022'de var ama varsayılan kapalı; 2025'te varsayılan açık. Batch mode on rowstore 2019 ve level 150 ile başladı. Implicit conversion davranışı üç sürümde aynı. Bu yazıdaki feedback ve batch mode özellikleri edition tablosuna göre Enterprise'a özgü; Standard'da bu planlar row mode ve sabit grant'le çalışır.

Üretimde Dikkat

Spill'leri izle. Tek seferlik bir spill felaket değildir, ama sık çalışan bir sorguda sürekli tempdb I/O'su (Input/Output) demektir. Actual plan'daki SpillToTempDb uyarısı ve Extended Events'teki sort_warning, hash_spill_details event'leri bunları yakalar.

MAXDOP ve cost threshold'u bir kez, bilinçli ayarla. Varsayılan MAXDOP 0 ve cost threshold 5, çok çekirdekli bir sunucuda küçük sorguların bile bütün çekirdekleri kullanmasına yol açabilir. Sunucu seviyesinde değiştiremiyorsan (paylaşılan ortam, managed servis), veritabanı seviyesindeki MAXDOP ayarı ve sorgu bazında OPTION (MAXDOP n) var.

ORM (Object-Relational Mapping) modelini şemaya uydur. varchar kolonlar için IsUnicode(false) ve doğru HasMaxLength yazmak hem implicit conversion'ı hem de parametre uzunluğu yüzünden plan cache'in şişmesini önler.

Sık Düşülen Tuzaklar

CXPACKET'i görünce MAXDOP'u 1 yapmak. Paralellik wait'i bir belirtidir; çoğu zaman sebep eksik index yüzünden büyük taramalar ya da yanlış tahminlerdir. Sunucu genelinde paralelliği kapatmak raporlama sorgularını cezalandırır.

Memory grant feedback'e güvenip tahmin sorununu bırakmak. Feedback yalnızca Enterprise'da ve yalnızca tekrar eden sorgularda çalışır; ilk çalıştırma yine spill eder, plan cache'ten düşen plan ayarını kaybedebilir.

Implicit conversion'ı yalnızca "yavaşlık" olarak düşünmek. Aynı uyarı cardinality estimate'i de bozar (ConvertIssue="Cardinality Estimate"); join sırası ve memory grant gibi başka kararları da etkiler.

Windows collation'da "nasılsa seek yapıyor" deyip geçmek. Seek var ama dönüşüm her satırda çalışıyor ve sunucu ya da veritabanı collation'ı değiştiğinde aynı sorgu bir anda scan'e dönebilir.

Küçük Bir Deneme

★☆☆ Açılıştaki sorguyu OPTION (RECOMPILE, MAXDOP 1) ile çalıştır. Tahmin ve memory grant ne oldu, spill kayboldu mu?

★★☆ ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4 ile ülke bazındaki join sorgusunu tekrarla. Partial aggregate'in çıktı satır sayısı nasıl değişti? Neden?

★★★ Customers.Email (varchar(200)) üzerinde EF Core'un Contains ile ürettiği LIKE sorgusunu varsayılan eşleme ve IsUnicode(false) ile karşılaştır. PlanAffectingConvert uyarısının ConvertIssue değeri ne?

Açılıştaki sorgunun asıl sorunu bir değişkenin değerinin bilinmemesiydi. Bir sonraki yazıda bunun tersini, değerin fazla iyi bilinmesini konuşacağız: plan cache, parameterization ve parameter sniffing.