SQL Server Execution Plan Warnings: Implicit Conversions, Missing Indexes and Spills (2026)

Where Warnings Appear in Execution Plans

When SQL Server compiles a query, it embeds diagnostic markers directly into the execution plan whenever it detects a condition that may harm performance. In the graphical plan viewer inside SSMS (Actual Execution Plan or Estimated Execution Plan), these markers appear as yellow exclamation-mark triangles overlaid on the operator icon. Hovering over any operator that carries a warning opens a tooltip whose last section is labelled Warnings, listing every issue SQL Server found for that node.

For programmatic analysis you read the raw XML. Every plan warning lives inside the <Warnings> element, which is a child of the operator's <RelOp> node. Common child elements include <PlanAffectingConvert>, <SpillToTempDb>, <NoJoinPredicate>, <ColumnsWithNoStatistics>, and <UnmatchedIndexes>. Understanding which element maps to which symptom is the first step toward a systematic fix process.

You can also surface warnings from the plan cache without opening SSMS at all, by querying the XML stored in sys.dm_exec_query_plan. This lets you audit an entire workload in minutes rather than opening plans one at a time.

Warning Type 1 — Implicit Conversion

An implicit conversion warning fires when SQL Server must silently cast a column value or parameter value to a different data type in order to evaluate a predicate. This is the single most common plan warning in production databases and it is also one of the most damaging — it can silently turn an index seek into a full table scan, inflating query duration from milliseconds to minutes.

The conversion happens because SQL Server follows a data type precedence hierarchy. When two operands have different types, the lower-precedence type is promoted to match the higher-precedence one. For example, NVARCHAR outranks VARCHAR; INT outranks SMALLINT. If your table column is VARCHAR(50) and you pass an NVARCHAR parameter, SQL Server must convert every row in the index to NVARCHAR before it can compare — making the index non-seekable.

In the tooltip you will see: "Type conversion in expression (CONVERT_IMPLICIT) may affect 'CardinalityEstimate' in query plan choice". The word CardinalityEstimate is the critical clue: the optimizer can no longer use statistics to estimate how many rows pass the predicate accurately, so its row count guesses are often wildly wrong, leading to poor join order and memory-grant decisions on top of the scan penalty.

Implicit Conversion Demo: VARCHAR Column vs NVARCHAR Parameter

The script below creates a table with a VARCHAR email column and a clustered index, then queries it with an NVARCHAR literal — the default type for string literals prefixed with N'' and also what ADO.NET and Entity Framework pass by default for string parameters.

-- Setup
CREATE TABLE dbo.Customers (
    CustomerID   INT           NOT NULL IDENTITY PRIMARY KEY,
    Email        VARCHAR(100)  NOT NULL,
    Region       VARCHAR(20)   NOT NULL,
    SignupDate   DATE          NOT NULL
);

CREATE INDEX IX_Customers_Email ON dbo.Customers (Email);

-- Insert 500 000 rows (abbreviated)
INSERT INTO dbo.Customers (Email, Region, SignupDate)
SELECT
    'user' + CAST(n AS VARCHAR) + '@example.com',
    CASE n % 5 WHEN 0 THEN 'APAC' WHEN 1 THEN 'EMEA'
               WHEN 2 THEN 'AMER' WHEN 3 THEN 'LATAM' ELSE 'ANZ' END,
    DATEADD(DAY, -(n % 1825), '2021-01-01')
FROM (SELECT TOP 500000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) n
      FROM sys.all_objects a CROSS JOIN sys.all_objects b) t;

-- Query that TRIGGERS implicit conversion warning
-- N'' prefix forces NVARCHAR literal → column is VARCHAR → full index scan
SELECT CustomerID, Email, Region
FROM   dbo.Customers
WHERE  Email = N'user12345@example.com';   -- NVARCHAR vs VARCHAR column

Enable the actual execution plan (Ctrl+M) and run the query. You will see a yellow triangle on the Index Scan operator. The tooltip Warnings section reads: CONVERT_IMPLICIT(nvarchar,CONVERT_IMPLICIT(nvarchar,[dbo].[Customers].[Email],0),0). SQL Server had to scan all 500 000 rows because it could not seek into the VARCHAR index using an NVARCHAR key.

Fixing Implicit Conversions

There are three remediation paths. Choose based on whether you control the schema, the application code, or both.

Option A — Fix the application parameter type (best). If your ORM or ADO.NET code passes string parameters for a VARCHAR column, explicitly set SqlDbType.VarChar:

// ADO.NET — force VARCHAR, not NVARCHAR
var cmd = new SqlCommand("SELECT * FROM dbo.Customers WHERE Email = @Email", conn);
cmd.Parameters.Add("@Email", SqlDbType.VarChar, 100).Value = emailInput;

Option B — Change the column to NVARCHAR. If the application universally sends NVARCHAR and the column is VARCHAR, migrate the column. The data doubles in storage but all index seeks are restored:

-- Migrate column type (run in maintenance window)
ALTER TABLE dbo.Customers
    ALTER COLUMN Email NVARCHAR(100) NOT NULL;

-- Rebuild index to pick up new key type
ALTER INDEX IX_Customers_Email ON dbo.Customers REBUILD;

Option C — Explicit CAST in the query (avoid if possible). Casting the literal matches types but still prevents a seek if you cast the column. Always cast the parameter side:

-- Correct: cast the parameter/literal, not the column
SELECT CustomerID, Email, Region
FROM   dbo.Customers
WHERE  Email = CAST(N'user12345@example.com' AS VARCHAR(100));  -- seek restored

-- WRONG: casting the column still prevents a seek
-- WHERE CAST(Email AS NVARCHAR(100)) = N'user12345@example.com'  -- full scan

Finding All Implicit Conversions in the Plan Cache

Hunting implicit conversions one query at a time is impractical on a busy server. The query below shreds the plan XML stored in the buffer pool and returns every statement that carries a PlanAffectingConvert warning, ranked by execution count so you tackle the highest-frequency offenders first:

-- Find all implicit conversion warnings in plan cache
;WITH PlanXML AS (
    SELECT
        qs.execution_count,
        qs.total_logical_reads,
        qs.total_elapsed_time / 1000          AS total_ms,
        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 query_text,
        TRY_CAST(qp.query_plan AS XML)        AS plan_xml
    FROM   sys.dm_exec_query_stats         qs
    CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle)       st
    CROSS  APPLY sys.dm_exec_query_plan(qs.plan_handle)    qp
    WHERE  qp.query_plan LIKE '%PlanAffectingConvert%'   -- fast pre-filter
)
SELECT TOP 50
    execution_count,
    total_logical_reads,
    total_ms,
    query_text,
    warning_node.value('@ConvertIssue', 'NVARCHAR(128)')  AS convert_issue,
    warning_node.value('@Expression',  'NVARCHAR(256)')   AS expression
FROM PlanXML
CROSS APPLY plan_xml.nodes(
    '//*[local-name()="PlanAffectingConvert"]'
) x(warning_node)
ORDER BY execution_count DESC;

The ConvertIssue attribute tells you whether the conversion affects a CardinalityEstimate, SeekPlan, or neither. Only the first two are performance-critical; conversions flagged as affecting only Type (pure widening in arithmetic) are cosmetic.

Warning Type 2 — Missing Index

A missing-index warning appears as a green hint text directly in the graphical plan — "Missing Index (Impact X%): CREATE NONCLUSTERED INDEX…". The Impact percentage is the optimizer's estimate of how much CPU and I/O cost this one index would eliminate for the specific query. A 90%+ impact number means the query is spending almost all its time on scans that an index would eliminate.

Missing-index hints are generated at compile time by the optimizer when it identifies a range scan or key lookup that a covering index would replace. They are recorded both in the plan XML (<MissingIndexes> element) and in the DMVs sys.dm_db_missing_index_details and sys.dm_db_missing_index_group_stats.

Missing Index DMVs

The following query joins the three missing-index DMVs to produce a ranked list of index candidates with their full CREATE statement. Sort by improvement_measure — a compound metric that weights both impact and usage frequency:

-- Ranked missing index candidates across all databases
SELECT TOP 25
    DB_NAME(mid.database_id)                          AS db_name,
    OBJECT_NAME(mid.object_id, mid.database_id)       AS table_name,
    migs.avg_total_user_cost * migs.avg_user_impact
        * (migs.user_seeks + migs.user_scans)         AS improvement_measure,
    migs.user_seeks,
    migs.user_scans,
    migs.last_user_seek,
    ROUND(migs.avg_user_impact, 0)                    AS avg_impact_pct,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    'CREATE NONCLUSTERED INDEX IX_'
        + OBJECT_NAME(mid.object_id, mid.database_id)
        + '_' + REPLACE(REPLACE(ISNULL(mid.equality_columns, '')
                + ISNULL('_' + mid.inequality_columns, ''), '[', ''), ']', '')
        + ' ON ' + mid.statement
        + ' (' + ISNULL(mid.equality_columns, '')
        + CASE WHEN mid.inequality_columns IS NOT NULL
               THEN (CASE WHEN mid.equality_columns IS NOT NULL THEN ',' ELSE '' END)
                    + mid.inequality_columns
               ELSE '' END + ')'
        + ISNULL(' INCLUDE (' + mid.included_columns + ')', '')
        + ';'                                         AS create_statement
FROM   sys.dm_db_missing_index_groups        mig
JOIN   sys.dm_db_missing_index_group_stats   migs ON mig.index_group_handle = migs.group_handle
JOIN   sys.dm_db_missing_index_details       mid  ON mig.index_handle        = mid.index_handle
WHERE  mid.database_id = DB_ID()             -- current database only
ORDER  BY improvement_measure DESC;

Cross-reference the last_user_seek date before creating anything. A high improvement-measure index that was last sought six months ago may belong to a decommissioned report.

Using Missing Index Hints Wisely

The optimizer's index suggestion is a starting point, not a prescription. Blindly executing every generated CREATE statement leads to three classic problems:

• Duplicate / near-duplicate indexes. SQL Server may suggest (A, B) INCLUDE (C) when you already have (A, B, C) as a covering index. Always check sys.indexes for existing candidates before creating.
• Over-indexing write-heavy tables. Every index adds overhead to INSERT, UPDATE, and DELETE. A table with 20 indexes often performs worse overall than one with 8 well-chosen indexes.
• Ignored INCLUDE columns. The optimizer lists included columns because the query SELECTs them. If you omit INCLUDEs to keep index size down, the optimizer will use the index but add a Key Lookup — which reintroduces a warning of its own.

Best practice: consolidate suggestions for the same table across all DMV rows into one or two composite indexes, then test with your actual workload using a load-replay tool or Query Store before deploying.

Warning Type 3 — Memory Spill (Sort / Hash)

Sort and Hash Match operators both require memory grants decided at query compile time. When the actual number of rows processed at runtime exceeds the optimizer's estimate, the allocated memory is insufficient and SQL Server spills intermediate data to TempDB. This is recorded in the actual plan as a warning on the Sort or Hash Match node, and in the operator tooltip you see Spill Level (1 = one spill pass, 2 = two passes, etc.) and Actual Pages Spilled.

In the XML, spills appear as <SpillToTempDb SpillLevel="1" SpilledThreads="0" /> inside the <Warnings> element of the relevant <RelOp>. The NodeId attribute on the RelOp lets you pinpoint the exact operator number in large plans.

Spills are expensive because TempDB I/O is orders of magnitude slower than in-memory sorting. A sort that takes 2 ms in memory can take 800 ms after a Level-2 spill on a moderately busy server.

Fixing Sort Spills

A Sort spill means the row estimate feeding into the Sort operator was too low. The root fix is always to correct the cardinality estimate upstream, but an immediate structural fix is to provide an index that already delivers rows in the required order, eliminating the Sort operator entirely:

-- Query with sort spill: ORDER BY on unindexed column
SELECT CustomerID, Email, SignupDate
FROM   dbo.Customers
WHERE  Region = 'APAC'
ORDER  BY SignupDate;   -- Sort operator → spills when row count underestimated

-- Fix: add an index that covers the WHERE and returns rows in ORDER BY order
CREATE NONCLUSTERED INDEX IX_Customers_Region_SignupDate
    ON dbo.Customers (Region, SignupDate)
    INCLUDE (Email);
-- SQL Server now uses an Index Seek + ordered scan — no Sort operator at all

When an eliminating index is impractical (e.g., dynamic ORDER BY columns), grant more memory to specific queries using Resource Governor or query-level hints:

-- Increase minimum memory grant for this query
-- SQL Server 2016+ supports MIN_GRANT_PERCENT / MAX_GRANT_PERCENT
SELECT CustomerID, Email, SignupDate
FROM   dbo.Customers
WHERE  Region = 'APAC'
ORDER  BY SignupDate
OPTION (MIN_GRANT_PERCENT = 10);   -- guarantee at least 10% of max server memory for this grant

Fixing Hash Spills

Hash Match spills arise when the build-side row count is underestimated, causing the hash table to overflow into TempDB. The root cause is almost always stale or absent statistics. Update statistics first:

-- Update all statistics on the affected table with a full scan
UPDATE STATISTICS dbo.Customers WITH FULLSCAN;

-- Verify stats date
SELECT
    s.name              AS stat_name,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM   sys.stats                                s
CROSS  APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE  s.object_id = OBJECT_ID('dbo.Customers')
ORDER  BY sp.last_updated;

If fresh statistics still leave a spill — which can happen with highly skewed data — apply OPTION(HASH JOIN, MIN_GRANT_PERCENT = 5) or use a table hint to switch the operator type. For SQL Server 2019+ with Intelligent Query Processing enabled, Adaptive Memory Grant Feedback will automatically increase the grant on the second execution of the same query and should eliminate the spill without manual intervention.

-- Force larger memory grant for a hash-heavy query (SQL Server 2016+)
SELECT c.Region, COUNT(*) AS cnt, SUM(o.TotalAmount) AS revenue
FROM   dbo.Customers c
JOIN   dbo.Orders    o ON o.CustomerID = c.CustomerID
GROUP  BY c.Region
OPTION (MIN_GRANT_PERCENT = 8, MAXDOP 4);

Warning Type 4 — No Join Predicate

This warning appears on a Nested Loops or Hash Match operator when SQL Server cannot find a join predicate connecting the two input tables. The result is a Cartesian product — every row on the left is joined to every row on the right — which is almost never intentional and can generate billions of result rows.

The tooltip message is direct: "No Join Predicate." Common causes include a forgotten ON clause, a mistyped column alias that falls out of scope, or an accidental comma-join in older SQL-89 syntax:

-- Accidental Cartesian product (missing ON clause)
SELECT c.Email, o.TotalAmount
FROM   dbo.Customers c, dbo.Orders o;    -- SQL-89 syntax, no WHERE join condition

-- Correct: explicit INNER JOIN with predicate
SELECT c.Email, o.TotalAmount
FROM   dbo.Customers c
JOIN   dbo.Orders    o ON o.CustomerID = c.CustomerID;

Legitimate uses of Cartesian products (CROSS JOINs for calendar generation, test data multiplication) should be written as explicit CROSS JOIN so the intent is documented and the warning is expected.

Warning Type 5 — Unmatched Indexes (Filtered Index Predicate Mismatch)

A filtered index has a WHERE clause baked into its definition, restricting it to a subset of rows. SQL Server can only use a filtered index when the query's own WHERE clause implies the index filter. When you define a filtered index but query without the matching predicate, the optimizer notes an UnmatchedIndexes warning — it considered the index but could not use it.

-- Filtered index: covers only ACTIVE customers
CREATE NONCLUSTERED INDEX IX_Customers_Active_Email
    ON dbo.Customers (Email)
    WHERE Region = 'APAC';   -- filter

-- This query CANNOT use the filtered index — UnmatchedIndexes warning
SELECT Email FROM dbo.Customers WHERE Email LIKE 'user1%';
-- Missing: WHERE Region = 'APAC' predicate

-- This query CAN use the filtered index — no warning
SELECT Email FROM dbo.Customers
WHERE  Email LIKE 'user1%'
AND    Region = 'APAC';       -- filter predicate present

The warning is informational rather than critical — it simply tells you the optimizer noticed the filtered index but could not apply it. Act on it by either adding the filter predicate to your query or reconsidering whether the filtered index is covering the right workload.

Warning Type 6 — Statistics Warning

SQL Server emits a statistics warning on a plan operator when the statistics object used to estimate cardinality has not been updated recently enough to be trusted. In SSMS tooltips this appears as "Statistics [statistic_name] are out of date on table [table_name]." In the XML it is a <ColumnsWithNoStatistics> or <StatisticsInfo> node.

Out-of-date statistics cause the optimizer to use stale histogram data. On a fast-growing table, the histogram may show 10 000 rows when 2 million now exist, causing the optimizer to choose a Nested Loops join that performs 2 million seeks instead of a Hash Match that would cost a fraction of the I/O.

Fix strategy:

1. Enable Auto-Update Statistics and Auto-Update Statistics Asynchronously (SQL Server 2014+) on every OLTP database so stale stats are refreshed in the background without blocking queries.
2. Schedule nightly UPDATE STATISTICS WITH FULLSCAN on the largest, most frequently modified tables where the auto-update threshold (20% row change) is too coarse.
3. For partitioned tables on SQL Server 2014+, enable incremental statistics so only changed partitions are updated, not the entire table.

-- Enable auto-update statistics (asynchronous) on a database
ALTER DATABASE YourDatabase SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE YourDatabase SET AUTO_UPDATE_STATISTICS_ASYNC ON;

-- Manual full-scan update for a critical table
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

-- Check modification counter — trigger manual update when > 10% of row count
SELECT
    OBJECT_NAME(s.object_id)           AS table_name,
    s.name                             AS stat_name,
    sp.last_updated,
    sp.rows,
    sp.modification_counter,
    ROUND(100.0 * sp.modification_counter / NULLIF(sp.rows, 0), 2) AS pct_changed
FROM   sys.stats                                             s
CROSS  APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE  sp.modification_counter > 0
AND    sp.rows > 0
ORDER  BY pct_changed DESC;

Bulk Warning Hunt: Extended Events Session

For a fleet-wide warning audit, deploy an Extended Events session that captures the query_post_execution_showplan and sql_statement_completed events and filters for plans containing any warning element. This avoids the plan-cache limitations (eviction, restart resets) and captures short-lived queries that never make it into DMVs:

-- Create Extended Events session to capture plans with warnings
CREATE EVENT SESSION [PlanWarnings] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan (
    ACTION (
        sqlserver.sql_text,
        sqlserver.database_name,
        sqlserver.username,
        sqlserver.client_hostname,
        sqlserver.query_hash
    )
    WHERE (
        -- Filter to plans that contain any warning element in the XML
        sqlserver.query_plan_filtered LIKE N'%