Why do we need to recompile stored procedures?
Click to see full answer
Besides, what causes stored procedure to recompile?
Factors in Recompilation
- Inserting or deleting lots of data (data density in indexes & tables often controls query plans)
- Rebuilding indexes (a change to underlying objects)
- Creating/dropping temp tables (again, underlying DML changes).
- query plan ages out (think not used recently and sql want's to clean up memory use)
Additionally, what happens when a stored procedure is compiled? A stored procedure is compiled code that you can call from within T-SQL statements or from client applications. SQL Server runs the code in the procedure and then returns the results to the calling application. Using stored procedures is efficient for several reasons.
Hereof, what is the use of recompile in SQL Server?
RECOMPILE – specifies that after the query is executed, its query execution plan stored in cache is removed from cache. When the same query is executed again, there will be no existing plan in cache, so the query will have to be recompiled.
What is Sp_recompile?
sp_recompile looks for an object in the current database only. The queries used by stored procedures, or triggers, and user-defined functions are optimized only when they are compiled. SQL Server automatically recompiles stored procedures, triggers, and user-defined functions when it is advantageous to do this.