Keep Fixed plan query hint in SQL server

The keep fixed plan query hint is more aggressive than keep plan. It prevents SQL Server from recompiling a query under any circumstances even when statistics changes significantly or when these are schema changes to underlaying tables. It is useful for high transaction OLTP system were frequent statistics update cause excessive recompilation.

Syntex

Select * from tablename where column=’XXX’  option (keep fixed plan)

It prevents recompilation even if statistics update. SQL server will only recompile if schema change.

When to use keep fixed plan

Ø  For frequently executed queries on rapidly changes table.

Ø  When excessive recompilation cause CUP overhead.

Ø  For OLTP system where query plan stability is critical.

When not to use keep fixed plan

Ø  If query performance degrades over the time due to outdated plans.

Ø  For highly dynamic workload where updated plan improve performance.

Ø  If schema changes occur frequently.

Performance comparison

Query Type	CUP usage	Execution plan stability	Recompilation count
Default query	Higher	Changes frequently	High
With keep plan	Lower	More stable	Reduced
With keep fixed plan	Lowest	Fixed (no change)	Near zero