STATEMENT-LEVEL RECOMPILATION Compiled plan (a.k.a. query plan) A

STATEMENT-LEVEL RECOMPILATION Compiled plan (a.k.a. query plan) A read-only data structure used by any number of users. The plan is reentrant, which implies that all users share the plan and no user context information (such as data variable values) is stored in the compiled plan. There are never more than one or two copies of the query plan in memory one copy for all serial executions and another for all parallel executions. Executable plan A data structure for each user that concurrently executes the query. This data structure, which is called the executable plan or execution context, holds the data specific to each user s execution, such as parameter values. This architecture, paired with the fact that the execution context is reused, has very much improved the execution of not only stored procedures but functions, batches, dynamic queries, and so on. However, there is a common problem with executing stored procedures, and that is recompilation. Examples of things that cause recompilation to occur are as follows: Schema changes Threshold changes in rows Certain SEToptions A recompilation can incur a huge cost especially if the procedure, function, or batch is large, because SQL Server 2000 does module-level recompilation. In other words, the whole procedure is recompiled even if the cause of the recompilation affects only a small portion of the procedure. In addition, if the recompilation happens because a SET option changes, the executable plan will be invalidated and not cached. The code in Listing 7-1 is extremely simple, but it can be used to illustrate the problem. Listing 7-1 is a stored procedure which in the middle of the procedure changes the CONCAT_NULL_YIELDS_NULL option. When this runs against SQL Server 2000, a recompilation happens for each execution of the procedure. Listing 7-1: Procedure That Causes Recompilation CREATE PROCEDURE test2 AS SELECT before set option

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

Bookmark the permalink.

Comments are closed.