A yellow warning icon is easy to spot and easy to overreact to. Reading the warnings in an execution plan means finding the operator, cause, and actual cost before changing anything.
Find the Operator Behind Execution Plan Warnings
Open the operator properties or plan XML and read the warning text. A graphical icon can sit near an operator whose input caused the problem. Note the statement, database, parameter values, and plan capture time. Estimated plans show compile-time warnings. Actual plans can add runtime warnings after execution.
I start at the operator, not at the overall query cost percentage. A warning with no measurable effect on a rare query can wait. A frequent query with a small-looking warning can matter. What work did that operator perform, and how frequently does the statement run? The answer sets priority. The icon is a clue, not an emergency pager.
Check Implicit Conversion
An implicit conversion occurs when SQL Server reconciles different data types. If the indexed column must be converted for a predicate, the engine can lose an efficient seek or misestimate rows. A conversion on the parameter side can be harmless. Read the plan expression to see which side changed. Match application parameter types and lengths to the column.
I have seen a Unicode parameter compared with a non-Unicode indexed column and the plan changed. The fix was in the data-access type, not a new index. Do not add a cast around the column without testing. That can preserve the problem. Inspect the query and driver binding. The query below lists column types for a table you name, so the application can use matching parameters.
SELECT c.name AS column_name,
TYPE_NAME(c.user_type_id) AS data_type,
c.max_length,
c.precision,
c.scale
FROM sys.columns AS c
WHERE c.object_id = OBJECT_ID(N'dbo.YourTable')
ORDER BY c.column_id;Read Missing Statistics Carefully
A missing-statistics warning says the optimizer lacked a statistic it wanted during compilation. Check whether AUTO_CREATE_STATISTICS is enabled, whether the user had permission, and whether a filtered or computed expression complicates the request. Create or update the specific statistic in a tested change when the estimate gap supports it.
I compare estimated and actual rows before adding objects. A warning can exist on a plan that still performs well. Conversely, a bad estimate can exist without a warning. Avoid turning every missing-statistics message into a blanket maintenance job. The right statistic is tied to a predicate and workload.
SELECT name,
is_auto_create_stats_on,
is_auto_update_stats_on
FROM sys.databases
WHERE database_id = DB_ID();Understand Sort and Hash Spills
A spill means an operator needed more working memory than its grant and used tempdb. Actual plans and runtime events can show spill details. A spill can come from poor estimates, a plan shape, or concurrent memory pressure. Raising server memory is not the automatic answer. Find why the grant was insufficient and whether the query is important enough to tune.
I check the operator’s estimated and actual rows, granted memory, and frequency. A single small spill during a monthly report differs from repeated spills on a high-volume endpoint. Query Store can show trends. Fixing statistics or reducing rows before the sort can help more than an instance-wide setting. The yellow icon is not a shopping list for RAM.
Look for No Join Predicate
A warning about a missing join predicate can signal an accidental Cartesian product. Sometimes a cross join is intentional, but it should be explicit and sized for the workload. Inspect the query text and the plan operator. A small test set can hide the scale of the problem until production data grows.
I ask the developer to explain the intended relationship between tables. If there is one, add the predicate and test the result rows, not only the duration. A faster query returning the wrong data is still wrong. If a cross join is deliberate, document why it is bounded. The warning deserves a decision, not automatic suppression.
Do Not Confuse Missing Index Advice
Plan suggestions for missing indexes are estimates from one compilation. They do not account fully for existing indexes, write cost, storage, or overlap with other suggestions. Before creating an index, inspect current keys and included columns. Consolidate where possible and test representative writes. A suggested index can improve one query and burden every insert.
I treat missing-index text as a hypothesis. It points to a useful access path, not a finished CREATE INDEX design. Compare the actual query frequency and read savings with maintenance cost. If the table changes rapidly, that trade-off matters. A green percentage next to a suggestion is not a purchase order.
Compare Execution Plan Warnings to User Impact
Prioritize warnings in an execution plan by total workload effect: query frequency, CPU, reads, elapsed time, and blocked work. One dramatic plan can matter less than a small repeated query. Query Store helps connect plans to usage over time. Capture a baseline before any fix so you can tell whether the warning removal improved the service.
I have watched a warning disappear while the query got slower. The icon was fixed, but the plan took a worse route. That is why the result must include runtime measurements and application behavior. Removing a warning is not the goal. Improving the right workload is.
Check the Context of Execution Plan Warnings
An execution plan generated under different SET options, compatibility level, statistics, or parameters can show different warnings. Compare like with like. An estimated plan cannot show a runtime spill that occurs only when actual rows arrive. An actual plan captured after a statistics update can differ from an earlier estimate for reasons unrelated to the display type.
I save the plan with query text and capture context. The XML retains details a screenshot can miss, including warnings and parameter information. Protect plan files if literals contain sensitive data. The warning needs context to remain useful after the incident ends.
Make One Testable Change
Choose one warning with a plausible effect. Fix a parameter type, add a targeted statistic, rewrite a join, or test a query-level memory path. Capture the new actual plan and compare results, reads, CPU, and duration. Verify that the query returns the same correct rows. Revisit other warnings after the first change.
Which warning can you link to a specific bad estimate or resource cost? Start there. The best plan review turns a colored icon into a narrow experiment. Everything else can remain a note until the workload gives it priority.
Related reading on this blog: Execution Plans and Indexing Strategies: Quick Guide and Performance and TempDB Spills: SQL in Sixty Seconds 208.
A plan warning is not a repair instruction, it is a clue that needs measured context.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
