A simple example on SQL Server’s RECOMPILE query option.
the RECOMPILE Query Option directs SQL Server to generate a new Query Execution Plan every time the query is run, the syntax for the command is:
Being a Query Option, the RECOMPILE statement needs to be placed at the end of the query, as such:
dbo.Transactions T WITH (INDEX(IDX_Customer_2))
CustomerID = 1
Usually forcing SQL Server to regenerate a plan is needed in the following situations:
- SQL Server Query Processor engine is not generating the expected efficient plans due to plan generalization.
- Catch-all queries such as using ISNULL in the WHERE clause