#Sql server recompile stored procedure how to
#Sql server recompile stored procedure code

This is a recompilation and it happens for various reasons. Sometimes, however, we re-execute a stored procedure, or resubmit a batch or query the optimizer has seen before, and for which it has an optimized plan in cache, but for some reason it canвЂ™t reuse that plan, and compiles a new one. When we execute the same batch or object again, it will simply reuse its cached plan, whenever possible. Fortunately, we tend to execute the same queries or procedures repeatedly, maybe with different parameters, so SQL Server stores most of the plans it generates in the plan cache, and will ensure that all plans are safe for reuse, regardless of what parameter values we supply.

#Sql server recompile stored procedure code

It takes time and resources for SQL ServerвЂ™s optimiser to devise this plan, but it must be done before the code can passed onto the execution engine. When SQL Server executes an ad-hoc batch or query, or an object such as a stored procedure or trigger, SQL Server compiles an execution plan for each batch or object, and for each query within that batch or object, optimized for the current state of the database, its objects and their data.

#Sql server recompile stored procedure how to

However, if recompilations become excessive, especially for frequent or costly queries, then it can become a problem, and itвЂ™s worth investigating the cause, which IвЂ™ll show how to do with Extended Events. There is nothing particularly wrong with recompilations, and in fact itвЂ™s quite common to force certain queries to recompile on every execution, precisely to avoid bad performance problems related to parameter sniffing, misuse of Execute(), or catch-all queries. Could it be something as simple as the fact that you have issued a SET statement in the batch, in order to change an execution setting? If you do, there is a chance that the issue is caused by SQL Server needing to recompile the procedure or trigger repeatedly. YouвЂ™ve checked the indexes, ruled out problems like parameter sniffing, but the intermittent performance problem persists. Sometimes you will have a stored procedure or trigger that intermittently takes longer to run, for no apparent reason. He is a regular contributor to Simple Talk and SQLServerCentral. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.Дespite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.