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

by

Troubleshooting Guide: SQL Server Error 10636 — “Ignore in Optimization” Setting Conflict

Why This Error Happens

This error occurs because the IGNORE_IN_OPTIMIZATION property is being applied to an index type that does not support it, such as a clustered index, XML index, spatial index, or full-text index. SQL Server 2025 restricts this specific optimization hint to non-clustered B-tree indexes and columnstore indexes only, as these are the only structures where the query optimizer can safely disregard the index during cost-based plan selection. The conflict typically arises from outdated T-SQL scripts, migration tooling, or ORM-generated DDL that attempts to apply this property universally across all index types without checking compatibility.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Index and Its Type

  • Run the following query to pull index metadata for the affected table:
  SELECT name, type_desc, is_hypothetical 
  FROM sys.indexes 
  WHERE object_id = OBJECT_ID('YourSchema.YourTable');
  • Confirm whether the type_desc returns CLUSTERED, XML, SPATIAL, or FULLTEXT — these are the types that will trigger Error 10636.
  • Cross-reference the index name from the error message with the output to pinpoint the exact object.

Step 2: Correct the T-SQL Script or ORM Mapping

  • Remove or comment out the IGNORE_IN_OPTIMIZATION = ON clause from the CREATE INDEX or ALTER INDEX statement targeting the incompatible index.
  • If using Entity Framework, Dapper, or a similar ORM, check the migration file (e.g., Migrations/202505_AddIndex.cs) for auto-generated index attributes and manually override the fluent API configuration.
  • For manual scripts, locate the statement in SSMS (SQL Server Management Studio 21) under Object Explorer > Databases > YourDB > Tables > Indexes, right-click the index, and select Script Index as > ALTER To > New Query Editor Window to inspect the current definition.

Step 3: Recreate the Index with a Supported Type (If Optimization Hint Is Required)

  • If you need the IGNORE_IN_OPTIMIZATION behavior, drop the unsupported index and recreate it as a non-clustered B-tree index instead:
  DROP INDEX IX_YourIndex ON YourSchema.YourTable;
  CREATE NONCLUSTERED INDEX IX_YourIndex 
  ON YourSchema.YourTable (ColumnName)
  WITH (IGNORE_IN_OPTIMIZATION = ON);
  • Ensure this change doesn’t break existing clustering keys or foreign key dependencies by checking sys.foreign_keys first.

Step 4: Apply the Corrected Script and Monitor Execution

  • Execute the revised script in SSMS or via sqlcmd from PowerShell:
  sqlcmd -S localhost -d YourDB -i "C:\Scripts\FixIndex.sql"
  • Watch the Messages tab in SSMS for confirmation of successful execution with no Error 10636 recurrence.

Alternative or Verification Steps

  • Verify the fix by querying sys.indexes again and confirming the ignore_in_optimization column (new in SQL Server 2025 catalog views) returns 1 only for non-clustered B-tree or columnstore index types.
  • If the error persists after recreating the index, check Windows Event Viewer (Event Viewer > Applications and Services Logs > MSSQLSERVER) for deeper engine-level conflicts, and consider running DBCC CHECKDB to rule out metadata corruption as a contributing factor.