In my experience, caching works, but usually only for a few hours out of the work day, so never expect your plans to last beyond that. I've worked in various databases and I've had the luck of seeing my stored procedure's cached plans last no more than 24 hours (usually a lot less), so by the next day, everything needs to be recompiled. My takeaway from the link above was to search your jobs and stored procedures for anything containing: Here is a great link on #3 Above (how clearing the cache may be done manually): As seen from our examples, they can behave dynamically to different inputs and are really powerful tools in efficient modularized implementation. (because they don't know what the heck they're doing) SAP HANA stored procedures are reusable snippets of code which would be used in other development objects like Calculation views, other stored procedures, table functions and more. Someone set up a routine job to periodically clear out the cache.Maybe you have a ton of random ad-hoc / prepared scripts that run, and it leaves little room to hold on to plans created for your stored procedures Memory pressure on the cache to make room for newly generated plans.However, I believe the #1 recurring reason for recompilation is the cached plan no longer exists. What I mean by "invalidate" is, something caused the existing plan to require a recompilation. The other 2 answers here (by and list reasons for SQL to "invalidate" the cached plan. This is usually caused when temporary objects are created and referenced throughout the code. Stored procedures will recompile if the developer has place interleaving Data Definition Language operations with Data Manipulation Language operations.SQL Server will recompile the stored procedure to be sure that the execution plan has the up-to-date statistics for the table. Stored procedure will recompile if there is a sufficient number of rows in a table referenced by the stored procedure has changed.the stored procedures plan dropping from the cache.restoring the database containing the stored procedure or any object referenced by the stored procedure.running the sp_recompile system stored procedure against a table referenced by the stored procedure.changing the schema of any referenced objects. ![]() using the WITH RECOMPILE clause in the CREATE PROCEDURE or the EXECUTE statement.dropping and recreating the stored procedure.Optimizing SQL Server Stored Procedures to Avoid Recompiles
0 Comments
Leave a Reply. |