Let us quickly have a look at few causes of recompilation in action. These can be seen in the Profiler, under the “Event Subclass” column, whenever there is a recompile event. Here is the output from this query: subclass_name WHERE e.trace_event_id = sv.trace_event_id Note that both the above events have exactly same reasons: SELECT sv.subclass_name, There are two events available in profiler called “SP:Recompile” and “SQL:StmtRecompile”īelow is the query which can identify various possible reasons which is stored in profiler related catalog views. Easiest way is to capture profiler trace. There are various ways by which recompile can be captured. The query optimizer would detect such situations and would force a recompile. Plan optimality related: If the data in underlying tables is changed considerably.Then the plan referring the object would be invalidated. Imagine if we have altered the table and added a new column. Plan Stability related: If the plan is not recompiled, we might get unexpected results.Recompilation is generally caused by these events: Once the plan is cached, there are situations when the optimizer feels something has changed and it has to compile/generate the query plan again, this is called Recompilation. If SQL Server has already spent time in finding the optimal plan for a given query, why do it again? Compiled plans can be reused and precious CPU cycles saved if it doesn’t have to find the query plan again and again. The query plans are cached because compilation of a query is a time consuming task. The goal of compilation is to reuse the plan by caching it in memory. In this article, we will look into this process.īefore trying to understand recompiling, though, it’s important to understand compilation in SQL Server. There are times when SQL Server thinks the cache is no longer valid and goes into a mode of recompilation. SQL Server has its own way of optimizing repeated calls to the same stored procedure–it is called stored procedure caching. This holds true even with software systems. There is a morale to this story: if we keep doing something day in and day out, we will surely become very good at it as we have mastered the art through lots of practice. Why?” To this the elderly man replied, “Young man, I am just making sure the whales don’t turn up.” Laughing, the young man replied, “I have not seen a single whale here, sir.” The elderly man replied, “And I am glad to hear that! I must be doing my job really well, then.” He asked,“Sir, why do you come every day to the river and wave your hand so enthusiastically? You don’t seem to miss a single day. Being curious, this young man made sure he stopped and asked this elderly man about his actions. One fine day, a stranger saw this and was amused. The passer-by used to think the elderly man had gone nuts and, after awhile, ignored his actions. A retired naval officer used to get to the top of a bridge and keep waving his hand everyday around 8 PM. Recently, I heard a story that was quite compelling.
0 Comments
Leave a Reply. |