Recompilation process is same as compilation and degrades server performance. In SQL Server 2000 and earlier versions, this was a serious issue but in SQL server 2005, the severity of this issue has been significantly reduced by introducing a new feature called Statement-level recompilation. When SQL Server 2005 recompiles stored procedures, only the statement that causes recompilation is compiled, rather than the entire procedure. Recompilation occurs because of following reason:
- On schema change of objects.
- Adding or dropping column to/from a table or view
- Adding or dropping constraints, defaults, or rules to or from a table.
- Adding or dropping an index to a table or indexed view if index is used by the plan.
- Adding or dropping trigger from a table
- Dropping statistics from a table that is used in plan.
- On change of the SET options: When a compiled plan is created, the SQL server also stores the environmental setting of a connection (SET option) with it. If the same stored procedure is executed by another connection that has a different SET option, then the existing cached plan is not reused. To reduce recompilation caused by Environment change, we should not change the SET options in the connection setting and stored procedure.
- On statistics change of tables: Every time the SQL Server uses an already cached compiled plan, it checks the optimality before using it. SQL Server decides whether the plan is optimal for current amount of data in underlying tables. It could be that the data amount in underlying tables changed so much that the previously created plan is not optimized. For each table & index, the SQL server maintains a modification counter and if the counter values exceed the defined threshold, the previously created compiled plan is considered stale plan and a new plan is created.
Detecting recompilations: The below query retrieves the top 10 statements for which the recompilation count is maximum. Here, plan_generation_num returns a number that indicates the recompilation count of a statement.
SELECT TOP 10
qs.plan_generation_num,
qs.execution_count,
DB_NAME(st.dbid) AS DbName,
st.objectid,
st.TEXT
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
ORDER BY plan_generation_num DESC
Another tool is to get recompilation details is the SQL Profiler. Along with the statements text, it also tells the reason of recompilation in EventClass and EventSubClass columns.
Reducing recompilations: We have no option to avoid “schema change” and “SET options” based recompilation. But we have following query and stored procedure hints to avoid “statistics change” based recompilation:
- KEEP PLAN hint: The modification counter threshold for a temporary table is 6. This implies that when a stored procedure that creates a temporary table inserts 6 or more rows into this table, Stored Procedures will be recompiled as soon as this table is accessed. For permanent tables, this threshold is at least 500. We can increase the first threshold for the temporary table (6) to same as that of the permanent table (500) by using the KEEP PLAN query hint in the statement where the temporary table is used. For example,
SELECT TT.col4, SUM(PermTable.col1)
FROM dbo.PermTable
INNER JOIN #TempTable AS TT
ON PermTable.col1 = TT.col2
OPTION (KEEP PLAN);
- KEEPFIXED PLAN hint: This hint completely avoids “statistics change” based recompilation of a query.
SELECT col1, col2
FROM dbo.PermTable
WHERE col3 < 100
OPTION (KEEPFIXED PLAN);
- Use Table variable instead of temporary tables: Because a change in cardinality of a table variable does not cause recompilations, consider using a table variable instead of a temporary table when faced with a problem of excessive recompilation. However, the use of table variables can lead to poorer query plans. Distribution statistics are not stored for table variables, and cardinality is only available during recompilation and not during initial compilation. One has to check whether this is the case and make an appropriate trade-off.
Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
