Reading Execution Plans 2: Memory Grants, Spills, Parallelism and Implicit Conversion Warnings

Version note: The T-SQL (Transact-SQL, Microsoft's dialect of SQL (Structured Query Language)) examples in this post were run on SQL Server 2025 CU10 (17.0.5035.5, Enterprise Developer Edition, compatibility level 170), and the C# examples with .NET 10, Entity Framework Core (EF Core) 10.0.14, Dapper 2.1.89 and Microsoft.Data.SqlClient 7.1.1.

Guess First

A report script sorts customer 1's orders by amount. The customer number is in a variable:

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

You know from post four: because of the variable, the optimizer expects 10.2 rows, while 200,000 arrive. The sort needs memory, and that memory is reserved before the query starts. How much memory did the optimizer ask for, and did 200,000 rows fit in it? The values read from the actual plan's XML (Extensible Markup Language):

<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 was requested, 1 MB granted, all of it used, and the sort spilled to tempdb: 1,597 pages written and the same number read back. In this post we read the "warnings" layer of the plan: memory grants and spills, memory grant feedback, parallel plans and exchange operators, DOP (degree of parallelism) feedback, implicit conversion and batch mode.

Memory Grants: Memory Reserved Before the Query Starts

Operators such as Sort and Hash Match (join and aggregate) need memory to work. SQL Server calculates this memory before the query starts, from the estimated row count and row width, and reserves it for the query as a memory grant. The grant does not grow during execution. If the estimate is small, the reserved memory is not enough and the operator writes part of the data to tempdb and reads it back (a spill). If the estimate is large, the opposite waste happens: even if the reserved memory goes unused, other queries cannot have it. That has its own plan warning too. The plan of the EF Core Include query from the previous post contained this line:

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

1 MB for a single query is trivial. But if hundreds of queries ask for too much memory at the same time, grants exhaust the server's query memory and new queries wait for a grant on the RESOURCE_SEMAPHORE wait. We will come back to this wait in the diagnostics post. You can see the grant size in the MemoryGrantInfo at the root of the plan, and for currently running queries in sys.dm_exec_query_memory_grants.

Spill Warnings and Memory Grant Feedback

I ran the opening query three times without changing it. The same plan came from the plan cache, but the memory grant changed:

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"

This is memory grant feedback. Having seen the spill in the first run, SQL Server updated the grant information of the cached plan; the second run got 21,992 KB and did not spill, and by the third the adjustment was "stable". The estimate is still 10.2 rows; only the grant changed. The feature arrived in stages: for batch mode in SQL Server 2017 (compatibility level 140), for row mode in 2019 (level 150), and in 2022 percentile grants (adjusting to the distribution of history rather than to the last execution) and persistence in Query Store (so a plan evicted from the plan cache does not lose its adjustment). All of them are on in this server:

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

Two caveats. First, according to the edition table, row mode memory grant feedback, percentile and persistence are Enterprise only; on Standard this query spills every time. Second, feedback treats the symptom, not the cause. The real problem is the variable disabling the histogram; OPTION (RECOMPILE) or a parameterized stored procedure gives this query the right estimate. Besides, the plan does 600,000 logical reads with 200,000 lookups; a far bigger cost than the spill.

Parallel Plans: MAXDOP, Cost Threshold and Exchange Operators

Two settings decide whether a query runs in parallel. cost threshold for parallelism: if the estimated cost of the serial plan exceeds this value, the optimizer also considers a parallel plan. max degree of parallelism (MAXDOP): the maximum number of threads a parallel plan uses. Both are at their default values on this shared server:

cost threshold for parallelism 5
max degree of parallelism      0

MAXDOP 0 means "all schedulers"; here, 10. 5 dates back to very old versions of SQL Server and is a very low threshold on today's hardware: a simple grouping query with an estimated cost of 5.99 ran with 10 threads:

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

Both are server-level settings, and this series does not touch server settings. But MAXDOP can also be set at the database level, and that is often the right place: after ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2;, queries in the same database ran with DegreeOfParallelism="2". Microsoft's MAXDOP guidance recommends an upper bound based on the number of cores per NUMA (Non-Uniform Memory Access) node; for cost threshold it gives no exact value and asks you to measure your own workload. In community practice, starting with values such as 25–50 instead of 5 is common for OLTP (Online Transaction Processing) systems.

To read a parallel plan in row mode, I disabled batch mode and ran a join by country:

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 operators (exchanges) move rows between threads. Repartition Streams redistributes rows by the hash of the join key, so an order and a customer with the same CustomerId land on the same thread. Each thread does a partial aggregate for its share (10 threads × 5 countries = 50 rows), Gather Streams collects them onto a single thread, and the global aggregate produces the final totals. While threads wait for each other, the time is recorded in waits starting with CX. The session's waits after these queries:

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 is a consumer thread waiting for rows from a producer and is a natural part of a parallel plan; on its own it is not a problem. CXPACKET, since SQL Server 2017 CU3 (Cumulative Update 3), shows waits on the producer side and imbalance between threads; if it is high, the data may be unevenly distributed across threads or the estimates may be wrong.

DOP Feedback: On by Default in 2025

Parallelism is good for a reporting query, while for a small, frequently run OLTP query the cost of coordinating threads can exceed the gain. DOP feedback, introduced in SQL Server 2022, watches elapsed time and waits for repeating queries; if parallelism looks inefficient, it lowers the DOP on the next execution and verifies whether that helped. A value that works is persisted in Query Store, and if things get worse it goes back to the last good value. According to Microsoft's documentation, the lowest DOP is 2, serial execution is out of scope, the plan is not recompiled, and the feature does not work together with query hints. It requires compatibility level 160 and Query Store in READ_WRITE mode. In 2022 it was off by default; in SQL Server 2025 it is on by default, and you can see DOP_FEEDBACK 1 in the list above. Because this feature relies on elapsed time, I do not show a deterministic experiment on this emulated container; you can see its results in the sys.query_store_plan_feedback view.

Implicit Conversion: nvarchar Parameter, varchar Column

The sneakiest warning in plans is PlanAffectingConvert. Orders.OrderNumber is varchar(20). Because .NET strings are Unicode, EF Core and Dapper send the parameter as nvarchar unless you say otherwise. To compare the two sides, SQL Server converts the column to nvarchar due to data type precedence. I looked up a single order by its number in four different ways and read the logical reads and the warning from the plan cache:

string orderNumber = "BK-00424242";

/* EF Core: default mapping and IsUnicode(false) */
var order = await ctx.Orders.Where(o => o.OrderNumber == orderNumber).SingleAsync();

/* Dapper: plain string and 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

Looking up a single row on a column with a unique index cost 3,114–3,117 pages instead of 3–6: all of UX_Orders_OrderNumber was scanned and CONVERT_IMPLICIT ran on every row. The reason is the server's collation. This container is installed with SQL_Latin1_General_CP1_CI_AS, and because varchar and nvarchar have different sort rules in SQL collations, the optimizer cannot turn the conversion into a range search. I copied the same data into a table whose column uses Latin1_General_CI_AS (a Windows collation) and tried the same nvarchar(4000) parameter:

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 ...)

With the Windows collation, the optimizer turned the conversion into a range with GetRangeThroughConvert and could seek. Still, the plan is more complex and estimates can suffer; the right fix is the same for both collations: match the parameter type to the column type. In EF Core, IsUnicode(false) on the model (EF Core already sends a consistent length with Size = 20); in Dapper, DbString or a global type map. The details on the Dapper side and the 4000-character rule are in post 13 of the Dapper series. The opposite direction is harmless: if you send a varchar parameter to an nvarchar column, it is the parameter that gets converted, not the column.

Batch Mode on Rowstore and Adaptive Joins

In the grouping plan above, the operators said mode=Batch. Batch mode processes rows not one at a time but in vectors of hundreds of rows, and lowers CPU (Central Processing Unit) cost noticeably for large queries. It used to be possible only with columnstore indexes; since SQL Server 2019, at compatibility level 150 and above, it kicks in on ordinary rowstore tables too when the optimizer finds it suitable (large scans, joins and aggregates). You recognize it by each operator's EstimatedExecutionMode and ActualExecutionMode in the plan.

The Adaptive Join we saw in the hint-free join plan in the previous post belongs to batch mode too. The XML has these values:

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]

The operator reads the build side and compares its row count with the threshold (5,029.8): above the threshold it runs a hash join, below it the nested loops branch. Here 100,000 rows arrived, a hash join was chosen, and the nested loops branch never ran (execs=0). For queries whose estimates may be wrong, this limits the price of a wrong join choice.

How SQL Server 2019/2022 differ: memory grant feedback for row mode came in 2019 (level 150), percentile and persistence in 2022. DOP feedback exists in 2022 but is off by default; in 2025 it is on by default. Batch mode on rowstore started with 2019 and level 150. Implicit conversion behaves the same in all three versions. According to the edition table, the feedback and batch mode features in this post are Enterprise-specific; on Standard these plans run in row mode with a fixed grant.

In Production

Watch for spills. A one-off spill is no disaster, but in a frequently run query it means constant tempdb I/O (Input/Output). The SpillToTempDb warning in the actual plan and the sort_warning and hash_spill_details events in Extended Events catch them.

Set MAXDOP and cost threshold once, deliberately. The default MAXDOP 0 and cost threshold 5 can make even small queries use every core on a many-core server. If you cannot change them at the server level (a shared environment, a managed service), there is the database-level MAXDOP setting and the per-query OPTION (MAXDOP n).

Make the ORM (Object-Relational Mapping) model match the schema. Writing IsUnicode(false) and the right HasMaxLength for varchar columns prevents both implicit conversion and plan cache bloat caused by parameter lengths.

Common Pitfalls

Setting MAXDOP to 1 when you see CXPACKET. A parallelism wait is a symptom; the cause is often large scans due to missing indexes or wrong estimates. Turning off parallelism server-wide punishes reporting queries.

Relying on memory grant feedback and leaving the estimation problem alone. Feedback works only on Enterprise and only for repeating queries; the first execution still spills, and a plan evicted from the plan cache can lose its adjustment.

Thinking of implicit conversion only as "slowness". The same warning also breaks cardinality estimates (ConvertIssue="Cardinality Estimate") and affects other decisions such as join order and memory grants.

Shrugging it off with a Windows collation because "it still seeks". There is a seek, but the conversion runs on every row, and when the server or database collation changes, the same query can suddenly turn into a scan.

A Small Experiment

★☆☆ Run the opening query with OPTION (RECOMPILE, MAXDOP 1). What happened to the estimate and the memory grant; did the spill disappear?

★★☆ Repeat the join by country with ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4. How did the partial aggregate's output row count change? Why?

★★★ Compare the LIKE query EF Core generates for Contains on Customers.Email (varchar(200)) with the default mapping and with IsUnicode(false). What is the ConvertIssue value of the PlanAffectingConvert warning?

The real problem with the opening query was that a variable's value was unknown. In the next post we discuss the opposite, a value known too well: the plan cache, parameterization and parameter sniffing.