![]() Occasionally, recompiles can occur that are neither triggered automatically by changes in the data structures or data, nor forced by use of hints. If the plan for only one statement within a batch or stored procedure has been invalidated by underlying changes to the data structures or data, or only one statement has the OPTION (RECOMPILE) hint, then only the plan for the affected statement is recompiled, not the whole batch or procedure. To save time and resources, SQL Server does statement-level recompilation, where possible. This is often done to deal with the erratic performance caused by parameter sniffing, the use of “catch-all” procedures, misuse of Execute(), and so on. The plan for that query may still be in the cache, but it will not be reused. We can also force the optimizer to continually recompile a plan by attaching to the query an OPTION (RECOMPILE) hint. The next time one of those queries runs, the optimizer will produce a new plan and the old one will be removed. It will happen automatically if the execution engine detects that a table has been altered or its statistics have changed substantially, at which point it will mark for recompilation any cached plans for queries that access that table. 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. ![]() 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. 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. ![]()
0 Comments
Leave a Reply. |