Skip Stored Procedure Recompilation
Skip Stored Procedure Recompilation
By : Kasim Wirama, MCDBA
Stored procedure (and also function) is preferable to be implemented in SQL Server because its compilation code is cached, so next execution will take the compilation form in cache. Imagine that you have a complex stored procedure and you found that it recompile for each time it is called through SP:Recompile and SQL:StmtRecompile. In this case SQL Server have some options that you might to consider to avoid the problematic stored procedure from being recompiled.
If you want to make your query got cached in cache store, there is some ways to achieve it :
- make your tables which are involved in query to read only.
- turn off automatic update statistics, it is applicable when there is little database update activity that impacted on index update. But it is recommended that you turn on auto update statistics.
- if your column is unique, make it unique constraint or unique index and your select query use the column in WHERE clause.
- if your column is not unique, you can consider use query hints : Option (Keepfixed Plan), it is available on SQL Server 2005.
For overall recompilation monitoring for SQL Server, performance monitor give 2 relevant counters under performance object : MSSQLServer : SQL Statisctics, i.e. SQL Re-compilation/sec and SQL:Batch Requests/sec. If ratio between them is high, your SQL Server has recompilation issue that might degrade database performance.