Notice that we have use count of 10 and our cache objtype is Compiled plan type Proc. SQL Server still Recompiles the block that has the hint but the rest remains untouched. Since we are not RECOMPILING the entire procedures from scratch we end up with a plan in the plan cache. There is an important distinction that happens at this time. This is achieved by adding the hint OPTION( RECOMPILE) against the statement we want recompiled as shown below. In these cases we can go for statement level recompile. If the procedure is sufficiently complex it doesn’t make sense to RECOMPILE the whole thing. Sometimes we arrive at a use case where the entire stored procedure seems to be fine except for a single block of code. Notice in the above screenshot we don’t have cacheobjtype = CompiledPLan ProcĪnother way to recompile procedures is something called statement level RECOMPILE If the hint was removed in the procedure we would end up with something like below:-Īnother way to achieve RECOMPILES without actually modifying the procedure would be to call the hint at run time as shown below Result of querying the plan cache can be seen below:. Since we need to recompile it every time the procedure executed there is no sense wasting memory on it. When used this way we see that the Execution plan is discarded as soon as the query finishes execution. This hint allows us to recompile the entire stored procedure (not just bits and pieces of it) each time it is executed. Here is the question that was being asked. To allow us to do this we have a few option. In such cases it is better to RECOMPILE the procedure so that it always takes into account the current state and comes up with a new plan each time. A simple example would be when the number of rows in the table have changed significantly. But like all things in life situations change and the plan that was working before might not be the best anymore. Because a stored procedure is something that gets executed frequently it makes sense to reuse the plan over and over again. Ideally the plan takes into account the current situations and arrives at what it thinks would get the job done in the quickest way with the least resources consumed. Stored procedures can be expensive to run so SQL Server database engine takes a moment when running the procedure the first time to come up with a plan. If you are not sure about how recompile works here is a quick primer. An interesting problem was posted on #SQLHelp the other day about using the QueryHint OPTION(RECOMPILE).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |