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. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |