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

by

Troubleshooting Guide: Error 10781 – Conflicting Query Optimizer Hints in SQL Server 2025

Why This Error Happens

This error occurs when a query simultaneously uses a QUERYTRACEON hint and a corresponding OPTIMIZER hint (such as USE HINT or a legacy trace flag equivalent) that govern the same internal optimizer behavior. SQL Server’s query optimizer cannot reconcile two explicit directives attempting to control the same execution path or cardinality estimation model at once. This typically surfaces after migrating queries from older SQL Server versions where trace flags were applied inline, while newer code also introduces OPTION (USE HINT(...)) syntax, creating a duplicate or contradictory instruction set.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Query Text

  • Capture the full query text referenced in the error message, including the specific %d trace flag number and the %.*ls hint string.
  • Use SQL Server Management Studio (SSMS) or Azure Data Studio connected to your Windows Server 2025 instance to re-run the query in isolation.
  • Check the Messages tab for the exact trace flag number (e.g., 4199, 9481) and the named hint (e.g., DISABLE_OPTIMIZER_ROWGOAL).

Step 2: Cross-Reference Trace Flag and Hint Mappings

  • Open the official SQL Server 2025 documentation or query sys.dm_exec_valid_use_hints to view the current list of supported USE HINT values.
  • Match the numeric trace flag from the error against its corresponding named hint to confirm they are indeed duplicates.
  • Example conflict: OPTION (QUERYTRACEON 9481, USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')) — both target legacy CE behavior.

Step 3: Edit the Query to Remove the Duplicate Hint

  • Open the stored procedure, view, or ad-hoc script in your IDE (SSMS, VS Code with mssql extension).
  • Remove either the QUERYTRACEON %d clause or the USE HINT('%.*ls') clause from the OPTION() list — keep only one.
  • Save the script and deploy the change via your standard release pipeline or directly via ALTER PROCEDURE.

Step 4: Clear the Plan Cache for the Affected Object

  • Run DBCC FREEPROCCACHE (scoped with sys.dm_exec_cached_plans lookup if production-safe) or use ALTER PROCEDURE ... WITH RECOMPILE for a one-time fix.
  • This ensures no stale execution plan referencing the old conflicting hint combination remains cached.

Step 5: Validate Server-Level Trace Flags

  • Check DBCC TRACESTATUS(-1) to see if the conflicting trace flag is also enabled globally via startup parameters in SQL Server Configuration Manager.
  • If found, navigate to SQL Server Configuration Manager > SQL Server Services > [Your Instance] > Properties > Startup Parameters on Windows Server 2025 and remove the -T%d entry if it duplicates the query-level hint.

Alternative or Verification Steps

  • Re-execute the corrected query and confirm it completes without Error 10781, then check the actual execution plan (Ctrl+M in SSMS) to verify the intended optimizer behavior is still applied.
  • As an alternative, use OPTION (USE HINT(...)) exclusively going forward instead of QUERYTRACEON, since Microsoft recommends named hints for better forward compatibility in SQL Server 2025 and beyond.