What is recompilation in SQL Server?
Recompilation is the same process as a compilation, just executed again. If the database structure or data changes significantly, a recompilation is required to create a new query execution plan that will be optimal for the new database state and ensure better procedure performance.
There are multiple reasons why a recompilation can occur. A recompile of an execution plan could be due to database level configuration changes, schema changes, index changes, etc.
Why do we need to recompile stored procedures?
When a procedure is compiled for the first time or recompiled, the procedure’s query plan is optimized for the current state of the database and its objects. … Another reason to force a procedure to recompile is to counteract the “parameter sniffing” behavior of procedure compilation.
What does Option recompile do?
OPTION(RECOMPILE) tells the server not to cache the pan for a given query. This means that another execution of the same query will require elaborating a new(maybe different) plan. This is used in the queries with parameters to prevent parameter sniffing issues.
If you have collected trace using either SQL Server Profiler or an Extended Events session, you can capture SP: Recompile event in your trace. When you look at the event it displays the reason for recompiling the statement. However, when you run the following statement in SQL Server Management Studio, you can find all the possible reasons for statement recompilation.
SELECT dxmv.name,
dxmv.map_key,
dxmv.map_value
FROM sys.dm_xe_map_values AS dxmv
WHERE dxmv.name = N'statement_recompile_cause'
ORDER BY dxmv.map_key;
When I ran this query, I got the following 14 different reasons for statement recompilation. I personally was not aware of all the 14 and it was interesting learning for me.
map_key map_value 1 Schema changed 2 Statistics changed 3 Deferred compile 4 Set option change 5 Temp table changed 6 Remote rowset changed 7 For browse permissions changed 8 Query notification environment changed 9 PartitionView changed 10 Cursor options changed 11 Option (recompile) requested 12 Parameterized plan flushed 13 Test plan linearization 14 Plan affecting database version changed Happy Coding :)