How to fix Error 8696 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 8696 – Improperly Formed Spool Element in USE PLAN Hint

Why This Error Happens

This error occurs when the XML execution plan supplied in a USE PLAN hint contains a Spool element whose parent RelOp node has a NodeId that is either missing, duplicated, or does not uniquely correspond to a single operator in the plan tree. SQL Server’s query optimizer requires every RelOp associated with a Spool operator to have a distinct NodeId so it can correctly map the forced plan shape back to the logical query tree. This typically results from manually edited XML plans, plans copied between different query contexts, or plans generated by a mismatched SQL Server version/build that structures Spool metadata differently.

Step-by-Step Resolution Guide

Step 1: Validate the XML Plan Structure

  • Open the XML plan file or extracted plan text used in the USE PLAN hint.
  • Search for all instances of <RelOp NodeId= and cross-reference them against every <Spool> parent element.
  • Confirm no two RelOp elements share the same NodeId value anywhere in the document.
  • Use a tool like Notepad++ or VS Code with XML formatting enabled to visually inspect nested Spool structures.

Step 2: Regenerate the Execution Plan from a Clean Source

  • Re-run the original query using SET SHOWPLAN_XML ON or extract the plan via SQL Server Management Studio (SSMS) “Include Actual Execution Plan” rather than reusing an old or hand-edited XML file.
  • Save the newly generated plan as a .sqlplan file to avoid encoding or structural corruption from copy-pasting raw XML.
  • Avoid merging plan fragments from two different queries or SQL Server builds, as NodeId numbering is context-specific per compiled plan.

Step 3: Apply the USE PLAN Hint with the Verified XML

  • Reference the clean, regenerated XML plan in your query hint syntax:
OPTION (USE PLAN N'<ShowPlanXML>...</ShowPlanXML>')
  • Ensure the XML namespace declaration matches the SQL Server 2025 schema version (xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan").
  • Test the query in a non-production environment first to confirm the Spool and RelOp hierarchy compiles without error 8696.

Step 4: Check for Compatibility Level Mismatches

  • Run SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the database compatibility level matches the SQL Server 2025 engine generating the plan.
  • If the XML plan was captured on a different compatibility level or older SQL Server version, regenerate it after aligning compatibility levels using:
ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

Alternative or Verification Steps

  • After applying the corrected plan, execute the query with OPTION (RECOMPILE) once to confirm no residual cached plan is causing conflicting NodeId references.
  • If the error persists, bypass the USE PLAN hint temporarily and use OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')) or a plan guide instead, then re-capture a fresh XML plan from that execution for future reuse.