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

by

Troubleshooting Guide: Error 10536 in SQL Server 2025

Why This Error Happens

This error occurs because SQL Server’s sp_create_plan_guide_from_handle procedure has a hard-coded limitation of 1,000 eligible statements per batch or module when generating a plan guide directly from a plan handle. When the target stored procedure, trigger, or ad-hoc batch contains more than 1,000 statements eligible for guide creation, SQL Server cannot process the entire cached plan as a single unit. This is a design constraint rather than a permissions or corruption issue, and it typically surfaces in environments with very large, auto-generated, or heavily nested T-SQL modules.

Step-by-Step Resolution Guide

Step 1: Identify the Statement Count and Offsets

  • Run DBCC FREEPROCCACHE carefully (in a test environment only) or query sys.dm_exec_query_plan and sys.dm_exec_sql_text using the problematic @plan_handle to inspect the batch.
  • Use sys.dm_exec_plan_attributes or parse the XML showplan to count individual statements within the module.
  • Note that each statement has a statement_start_offset and statement_end_offset value embedded in the execution plan metadata.

Step 2: Extract Statement-Level Offsets

  • Query the XML plan output (via sys.dm_exec_text_query_plan) to pull out each StmtSimple or StmtCond node.
  • Record the StatementStartOffset and StatementEndOffset attributes for every individual statement in the batch.
  • Export this list into a working table or temporary script file (e.g., C:\SQLScripts\PlanGuideOffsets.sql) for iteration.

Step 3: Create Plan Guides Per Statement

  • Instead of calling sp_create_plan_guide_from_handle on the entire batch, loop through each recorded offset pair.
  • Execute sp_create_plan_guide_from_handle individually for each statement, passing the specific @statement_start_offset parameter:
  • EXEC sp_create_plan_guide_from_handle @name = N'PG_Statement1', @plan_handle = @handle, @statement_start_offset = 0;
  • Repeat this call for each offset value captured in Step 2, incrementing the plan guide name (e.g., PG_Statement2, PG_Statement3).

Step 4: Automate the Batch Process (Optional)

  • If the module exceeds 1,000 statements, write a PowerShell or T-SQL cursor script to automate the per-statement loop to avoid manual errors.
  • Save the script under C:\SQLScripts\AutoPlanGuideGenerator.ps1 and schedule it via SQL Server Agent or Windows Task Scheduler if this needs to run periodically after deployments.

Step 5: Validate Plan Guide Creation

  • Query sys.plan_guides to confirm the number of newly created guides matches the number of statements processed.
  • Check the is_disabled column to ensure none of the guides failed silently during creation.

Alternative or Verification Steps

  • Verify success by running SELECT * FROM sys.plan_guides WHERE name LIKE 'PG_Statement%'; and confirming the row count aligns with your original statement count from Step 1.
  • If per-statement creation is still impractical due to extreme complexity, consider refactoring the oversized stored procedure into smaller, modular sub-procedures, which naturally keeps each unit under the 1,000-statement threshold.