![]() ![]() But it takes far less time than a complex query with a bad execution plan would. There are a few ways to ensure recompilation of a stored procedure: using WITH RECOMPILE, making the stored procedure dynamic (think exec ()) marking the proc for recompile with sprecompile. (This can be a nuisance booting the server magically makes bad things stop happening and nobody's quite sure why.) ![]() You can EXEC procedure WITH RECOMPILE when you run it. In Asp.net some processes will time out before your stored proc will execute. WITH RECOMPILE AS into its definition so it will recompile every time you run it. You get the best performance if all your stored procs are in the procedure cache all the time WITH RECOMPILE will not allow that, so everytime your stored proc is accessed your user will wait for SQL Server to recompile the stored proc before executing it. That may be enough if the procedure was first defined long ago in the life of your database and your tables have grown a lot. This means: No execution plans in cache to review No execution stats recorded in sys.dmexecquerystats No execution stats recorded in sys.dmexecprocedurestats Wow, thatâs a bummer. You can rerun its definition to recompile it once. Using RECOMPILE in the stored procedure header is pretty drastic the procedure wonât cache an execution plan when it runs. Second, because itâs recompiled on every execution SQL Server does not place it in the plan cache so troubleshooting performance problems around these procedures is more difficult. First, you rarely want the entire procedure to be recompiled. Good news: recompile hints dont obscure past performance in Query Store (available in SQL Server 2016+) like they do in the plan cache. How to recompile your SP? (See the doc page linked above.) CREATE WITH RECOMPILE: do not use this.If you run the SP's queries yourself (in SSMS maybe) they get compiled and run. If parameter values on the procedure are frequently atypical, forcing a recompile of the procedure and a new plan based on different parameter values can improve performance. If these values represent the typical ones with which the procedure is subsequently called, then the procedure benefits from the query plan every time that it compiles and executes. When SQL Server executes procedures, any parameter values that are used by the procedure when it compiles are included as part of generating the query plan. This can improve the procedure's processing performance.Īnother reason to force a procedure to recompile is to counteract the "parameter sniffing" behavior of procedure compilation. My guess was a bad execution plan so I added WITH. This fixes the problem for a while, but it slowly creeps back. If a database undergoes significant changes to its data or structure, recompiling a procedure updates and optimizes the procedure's query plan for those changes. When the slow down occurs, we are able to restore functionality by running EXEC sprecompile 'CrucialTable' on a table that is touched by all of the stored procedures, and then immediately running the report for a long date range. When a procedure is compiled for the first time or recompiled, the procedure's query plan is optimized for the current state of the database and its objects. ![]()
0 Comments
Leave a Reply. |