Stored Procedures caught me with too many recomplies - way out to avoid it

A common scenario in SQL Server environment that often application users complained about slow performance and same time you observe high CPU usage/spikes on the SQL Server instance, so better to follow the methods from HighCPU-whyitis blog and still you have not seen no sign of resolution to the problem, go down this blog.

Back to basics, by default SQL Server (until 2000 version days) does not create a new data access plan to retrieve the information from the database. So it will be optimized when they are compiled. So over the time as you make changes to the table structure or introduce new indexes which may optimize the data retrieval you should recompile your stored procedures as already compiled stored procedures may lose efficiency. Within SQL 2005 version such an optimization happens automatically when the first time a stored procedure is executed right after SQL Server services are restarted. Also it happens when underlying table has been changed that is used within a stored procedure. Say if you have added an Index to a table and the new plan will be updated not until the dependant stored procedure is recompiled. In this situation, it can be useful to force the stored procedure to recompile the next time it executes  

That is good upto some extent but think about the similar process is appearing whenever a Stored Procedure is executed many times within a minute time. So if you have detected excessive compilation/recompilation, consider the following options.

• Ensure SET options are not changed as they were previously, using PROFILER to determine which SET option is changed.
• Do not attempt to perform SET options within a stored procedure, always set them at connection level. (This will ensure the relevant SET options for a connection are not changed until its life time)
• Recompilation uses temporary tables, so keep a track of TEMPDB space when it is happening frequently.
• Hint about using HINTS! You can use KEEP PLAN query hint, that uses threshold of temporary tables to be aligned with permanent tables. To keep a track use PROFILER to view EventSubclass for 'Statistics Changed' value, this shows how frequently/best temporary table is used.
• Think about changes in table statistics due to the volatile data updates/inserts. Using hint of KEEPFIXED PLAN in query will affect the recompilations when underlying table structure has changed and current plan no longer applies, not due to the outdated-statistics.
• The above may not be consistent if you have explicitly mentioned SP_RECOMPILE statement for that stored procedure, as it will marked for recompilation on next execution.
• Do not attempt to turn-off automatic updates of statistics and indexes on the database.
• Always refer the object names with qualified sequence, such as dbo.MyTable to avoid ambiguity.
• Do not interleave DML and DDL operations when using conditional statements such as IF.
• Do not use WITH RECOMPILE statement within a stored procedure or using RECOMPILE hint. This will avoid un-necessary sequnce of recompilation of stored procedure every time it is executed.
• If simple set of queries and stored procedures are taking longer time better to take help of Data Tuning Advisor (DTA) - look at updated SQL Server 2005 Books Online for more information on its usage.
• If you have found excessive usage of temporary tables within the queries, better to switch to table variables wherever necessary - refer to  Temporary Tables vs. Table Variables and Their Effect on SQL Server link.
• Table variable cardinality do not ask or attempt a recompilation, but the issue here is optimizer cannot keep a track of table-variable's statistics or its maintenance. (piece of advice - test this method thorougly)
• Carefully adopt usage of JOIN statements, because when the query optimizer chooses a different join order the system performance may be slow.

Further down the road of this exercise refer the following articles that can help you to take better understanding of the issue and resolve it:

Optimizing SQL Server Stored Procedures to Avoid Recompiles

Beware: New Query Hints Added to SQL Server 2005

SSQA-StoredProcedures_Tags

FIX: Performance is very slow when the same stored procedure is executed at the same time in many connections on a multiple-processor computer that is running SQL Server 2005