Recompilation is a process in SQL Server that can impact server performance. However, with the introduction of statement-level recompilation in SQL Server 2005, the severity of this issue has been significantly reduced.

Recompilation occurs in SQL Server for various reasons, including:

• Schema changes to objects, such as adding or dropping columns, constraints, or indexes
• Changes to SET options
• Changes to statistics of tables

Prior to SQL Server 2005, when a stored procedure was recompiled, the entire procedure had to be compiled again. This could lead to performance degradation, especially in complex procedures. However, with statement-level recompilation, only the statement that caused the recompilation is compiled, reducing the impact on server performance.

One common reason for recompilation is a change in the schema of objects. When a schema change occurs, SQL Server needs to recompile the affected statements to ensure they are executed correctly with the updated schema. Similarly, changes to SET options can also trigger recompilation. If a stored procedure is executed with different SET options, the existing cached plan is not reused, leading to recompilation.

Another factor that can trigger recompilation is changes to the statistics of tables. SQL Server maintains a modification counter for each table and index. If the counter exceeds a defined threshold, the previously compiled plan is considered stale, and a new plan is created.

To detect recompilations, you can use the sys.dm_exec_query_stats dynamic management view. The following query retrieves the top 10 statements with the highest recompilation count:

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

Additionally, the SQL Profiler tool can provide detailed information about recompilations, including the reason for recompilation.

To reduce recompilations caused by changes in statistics, you can use query and stored procedure hints. The KEEP PLAN hint can be used to increase the modification counter threshold for temporary tables, aligning it with the threshold for permanent tables. This can be helpful when a stored procedure that creates a temporary table inserts a significant number of rows into it.

SELECT TT.col4, SUM(PermTable.col1)
FROM dbo.PermTable
INNER JOIN #TempTable AS TT ON PermTable.col1 = TT.col2
OPTION (KEEP PLAN);

The KEEPFIXED PLAN hint can be used to completely avoid recompilation based on changes in statistics. This can be useful when you want to ensure that a query always uses the same plan, regardless of changes in statistics.

SELECT col1, col2
FROM dbo.PermTable
WHERE col3 < 100
OPTION (KEEPFIXED PLAN);

Another approach to reduce recompilations is to use table variables instead of temporary tables. Changes in the cardinality of a table variable do not trigger recompilations. However, it’s important to note that the use of table variables can lead to poorer query plans, as distribution statistics are not stored for table variables.

Understanding recompilation in SQL Server and implementing strategies to reduce its impact can help improve overall server performance and query execution efficiency.