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.
With option recompile software#
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 kommentar(er)
