Keep plan Query hint in SQL Server

 Keep plan query hint instruct SQL server to keep execution plan stable for a query, even if there are frequent statics update. Normally, SQL Server recompiles a query when statistics changes significantly. By default, SQL Server may recompile a query when a temporary table is involved. Keep plan hints tells SQL server to use the same execution plan regardless of temporary table’s row count preventing these recompilations.

Syntex

Select column1, coulmn2 from table where column1=’XXX’ option (Keep Plan)

It prevents unnecessary recompilation, improve the performance. Ensure query plan remain stable even if the table gets frequently insert, update or delete.

When to use keep plan

Ø  For frequently updated table.

Ø  When queries recompile too often due to statistic changes.

Ø  For OLTP system when stability is needed for repeated queries.

When not to use keep plan

Ø  If Statistics update are rare.

Ø  For highly dynamic queries where different plan may need each time.