An adhoc T-SQL statement doesn't have an ID to begin with since it is not a database object. So the search for existing execution plans is faster in case of SP's.Īdhoc T-SQL statements follow the same rules (statistics, etc) as an SP regarding plan reuse and some more. Please note that while searching for an existing execution plan, SQL server searches by the ID of the SP being currently submitted for execution. I have just mentioned the most relevant points here with respect to this article. There are other factors also that affect plan reuse but that theory is too detailed.
Use of a temporary table within an SP also causes recompilation of that statement. If the database level setting "auto-update statistics" is off, we have less recompilation of SP's to begin with.
This plan reuse system works as long as objects are qualified with it owner and DatabaseName (for e.g. If all four tables have changed by about 20% since last statistics update, then the entire SP is recompiled. If you are accessing four tables in that SP and roughly 20% of the data for one of these tables has been found to have changed since the last statistics update, then that statement is recompiled (statement level recompile). Recompilation happens when about 20% of the data in the tables being referenced within the SP is found to have changed since the last time statistics were updated for those tables and its indexes. So usage of different input parameters doesn't necessarily cause a recompilation. New input parameters in the current instance of execution replace the previous input parameters from a previous execution plan in the execution context handle which is part of the overall execution plan.
With recompile sql serial#
General Plan Reuse in briefĪssuming both of these options are not being used, an execution of an SP prompts a search for pre-existing plans (one serial plan and one parallel plan) in memory (plan cache). The new plan is discarded immediately after execution of the statement. Any pre- existing plan even if it is exactly the same as the new plan, is not used. When used with a T-SQL statement whether inside an SP or adhoc, Option 2 above creates a new execution plan for that particular statement. There is no caching of this particular execution plan for future reuse. Once the SP is executed, the plan is discarded immediately. Any existing plan is never reused even if the new plan is exactly the same as any pre-existing plan for that SP. It can not be used at an individual statement level but at a stored procedure level only.
With recompile sql code#
When used in the T-SQL code of a particular stored procedure (SP), Option 1 compiles that SP every time it is executed by any user. Option2: Option (Recompile) Their functionality Lastly, some relevant cautionary notes for their usage are mentioned. First, the features are explained and then they are differentiated. And EXEC WITH RECOMPILE has never been very commonly used, I think.This article attempts to differentiate between the uses of WITH RECOMPILE and OPTION (RECOMPILE) features in Microsoft SQL Server 2005/2008. So WITH RECOMPILE with CREATE PROCEDURE is in practice a retired feature that you rarely, if ever, have reason to use. You cannot get this effect with WITH RECOMPILE, since the compilation occurs when the procedure starts and the run-time value of is not known. But what is really powerful is that can be handled as a constant, and therefore you can get an Index seek when you have an and a table scan when you don't. One is if this is the only statement in the procedure that calls for a recompile every time, resources are saved by not recompiling the rest. This is more powerful than WITH RECOMPILE for two reasons. This tells SQL Server to recompile this statement every time it is executed. Starting with SQL 2005, there is a better alternative and that the is the statement-level hint OPTION(RECOMPILE): SELECT. I don't if the new plan is stored in cache, or if it is discarded.īoth are old features that have been in the product since at least SQL Server 4.x. This tells SQL Server to recompile the procedure for this invocation, bypassing what is in cache. The other is when you invoke a procedure: EXEC MySP WITH RECOMPILE This instructs SQL Server to recompile the entire procedure every time it is called, and never cache the plan. One is when you create a procedure, for instance: CREATE PROCEDURE MySP WITH RECOMPILE AS The WITH RECOMPILE clause can be used into places:
0 kommentar(er)
