Warnings, Spills, and Memory Grants

SQL Server annotates plans with warnings when it notices something worth telling you. They are drawn as a small yellow triangle on an operator and they are the highest-signal, lowest-effort thing in a plan. Check them before you analyze anything else.

Implicit conversion (ConvertIssue)

The warning reads roughly: "Type conversion in expression may affect CardinalityEstimate in query plan choice" — sometimes with SeekPlan in the message, which means it also prevented an index seek.

This is the most commonly overlooked cause of a slow query in a .NET application, and Module 4 covers it in full. In short: if the application sends an NVARCHAR parameter and the column is VARCHAR, SQL Server must convert the column to compare, which makes the predicate unusable for a seek and scans the table. The application code looks perfectly correct.

To find it across everything cached:

SELECT TOP (25)
       DB_NAME(p.dbid) AS database_name,
       SUBSTRING(t.text, 1, 400) AS query_snippet,
       p.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS p
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)   AS t
WHERE CAST(p.query_plan AS nvarchar(max)) LIKE '%PlanAffectingConvert%';

Spills to tempdb

Sorts, hash joins, hash aggregates and exchanges all need memory. SQL Server decides how much to grant at compile time, from row estimates. If the estimate was low and the operator needs more at runtime, the excess goes to tempdb — writing to disk in the middle of an in-memory operation.

The warning names the operator and the spill level. A "level 1" hash spill is a mild slowdown; deep recursive spills are catastrophic. A sort spill on a large report is often the difference between three seconds and three minutes.

Important: a spill is a symptom of a bad estimate, not a memory problem. Adding RAM to the server does not fix it, because the grant was sized from the estimate, not from what was available. Fix the estimate (Lesson 2) and the grant sizes correctly.

To catch spills happening in production, use Extended Events rather than watching plans:

CREATE EVENT SESSION [spill_watch] ON SERVER
ADD EVENT sqlserver.hash_warning (
    ACTION (sqlserver.sql_text, sqlserver.database_name, sqlserver.session_id)),
ADD EVENT sqlserver.sort_warning (
    ACTION (sqlserver.sql_text, sqlserver.database_name, sqlserver.session_id))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY = 4096 KB, STARTUP_STATE = OFF);

ALTER EVENT SESSION [spill_watch] ON SERVER STATE = START;
-- ... let the report run ...
-- Read results in SSMS: Management > Extended Events > Sessions > spill_watch > Watch Live Data
-- Then stop and clean up:
-- ALTER EVENT SESSION [spill_watch] ON SERVER STATE = STOP;
-- DROP EVENT SESSION [spill_watch] ON SERVER;

Excessive memory grant

The opposite problem. The optimizer over-estimated rows, asked for several gigabytes, and got it. The query may run fine — but that memory is reserved for its whole duration, so other queries queue behind it on RESOURCE_SEMAPHORE. One over-granting report can degrade an entire OLTP workload.

SQL Server 2019 and later flag this in the plan as an "excessive grant" warning when the grant far exceeds what was used. Find the offenders:

SELECT TOP (25)
       qs.execution_count,
       qs.max_grant_kb        / 1024 AS max_grant_mb,
       qs.max_used_grant_kb   / 1024 AS max_used_mb,
       CASE WHEN qs.max_used_grant_kb > 0
            THEN qs.max_grant_kb * 1.0 / qs.max_used_grant_kb END AS grant_overshoot_ratio,
       qs.total_elapsed_time / qs.execution_count / 1000 AS avg_ms,
       SUBSTRING(t.text, 1, 300) AS query_snippet
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE qs.max_grant_kb > 100000            -- grants over ~100 MB
ORDER BY qs.max_grant_kb DESC;

A ratio of 50 means the query reserved fifty times the memory it used. SQL Server 2017+ has memory grant feedback, which corrects this over repeated executions — and in 2022 that feedback persists across restarts via Query Store (Module 5).

Missing index

The plan shows a green suggestion: "Missing Index (Impact 96%): CREATE NONCLUSTERED INDEX...". Do not right-click and create it. Module 3 explains in detail why these recommendations mislead — they ignore indexes you already have, get the column order wrong, and over-include columns. Treat it as "the optimizer wanted to seek on these columns and could not," which is genuinely useful information, and then design the index yourself.

No Join Predicate

Means a Cartesian product — every row joined to every row, usually because a join condition was lost in a multi-table query. Almost always a bug in the SQL, and worth checking first when a report returns implausibly many rows.

Columns With No Statistics

The optimizer wanted statistics on a column and there were none, so it guessed. Common when AUTO_CREATE_STATISTICS has been turned off. Check that setting before doing anything else:

SELECT name,
       is_auto_create_stats_on,
       is_auto_update_stats_on,
       is_auto_update_stats_async_on,
       is_read_committed_snapshot_on,
       snapshot_isolation_state_desc,
       compatibility_level
FROM sys.databases
WHERE name = DB_NAME();

is_auto_create_stats_on = 0 on a reporting database is a strong candidate for the root cause of widespread bad estimates. compatibility_level on that same row determines which of the 2022 features in Module 5 are actually available to you.

Unmatched Indexes

Appears when a filtered index almost matched the query but its filter could not be proven to cover the predicate — often because the query uses a parameter, and the optimizer cannot verify at compile time that the parameter satisfies the filter. Recompiling with the value known (OPTION (RECOMPILE)) usually lets it match.

The order to check them in

1. Any warning triangle at all — read every one.
2. Estimated vs actual rows at the operator producing the most rows.
3. Key/RID lookups and their execution counts.
4. Join types against the actual data volumes.
5. Cost percentages — last, and only to orient yourself.

The short version

Check plan warnings first because they are free information: implicit conversion, spills to tempdb, excessive memory grants. A spill is not a memory problem, it is a bad estimate — the grant was sized at compile time from the estimate, so adding RAM does not help and fixing the estimate does. And do not create the missing index the plan suggests; use it as a signal about which columns the optimizer wanted to seek on, and design the index yourself.