SQL Server Interview Question #214

What is query recompilation?

Advanced Query Optimization Senior Advanced

Detailed Explanation

Recompilation means SQL Server generates a new execution plan instead of reusing the existing cached plan. It can happen automatically after relevant changes or can be requested explicitly.

OPTION (RECOMPILE) compiles the statement using values available for that execution and does not reuse that statement's compiled plan in the normal way. This can help highly parameter-sensitive queries, but repeated compilation consumes CPU.

Recompilation is therefore a targeted technique rather than a default performance setting.

Code Example

SELECT Id, OrderDate, TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId
OPTION (RECOMPILE);