Before the first time a query is run , SQL Server Query Optimizer compiles a cost effective execution plan. Compiling the plan is CPU cycle expensive. Maximising Execution Plan Reuse helps query response time.
Recompilations are normal in SQL Server. For example these conditions will make an Excecution Plan invalid and cause a recompile. From BOL
1) Changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).
2)Changes to any indexes used by the execution plan.
3)Updates on statistics used by the execution plan, generated either explicitly from a statement, such as UPDATE STATISTICS, or generated automatically.
4)Dropping an index used by the execution plan.
How to identify excessive recompilations and troubleshoot
1) Find statements with Recompilations – This query returns the top 5 statements based on plan generation number
SELECT TOP 5 qst.plan_generation_num, qst.execution_count, stm.TEXT, DB_NAME(stm.dbid) AS DbName, stm.objectid FROM sys.dm_exec_query_stats qst CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS stm ORDER BY qst.plan_generation_num DESC
Another useful tool is Profiler .Check the SQL:StmtRecompile event class
2) Identify the lists of queries with high levels of recompilations. Are they Adhoc or Dynamic Queries?
3) If Adhoc or Dynamic Queries exist, rewrite the query . Try and write as Prepared Statements, Stored Procedures or sp_executesql
4) If the list of queries with recompilations are not Adhoc or Dynamic queries but the levels of recompilations remain high, review the query for a rewite. Try and write as Prepared Statements, Stored Procedures or sp_executesql. If that doesn’t improve the recompilation levels – consider using the PARAMETERIZATION between forced and simple