STATEMENT-LEVEL RECOMPILATION 5. Execute the procedure a couple

STATEMENT-LEVEL RECOMPILATION 5. Execute the procedure a couple of times from Query Analyzer and view the trace output. 6. The output from the trace will show a couple of entries in the Event Classcolumn with the value of SP:Recompile, as in Figure 7-3. This indicates that the procedure has been recompiled. As mentioned before, the cost of recompilation can be very high for large procedures, and in the SQL Server 2005 release, Microsoft has changed the model to statement-level re-compilation. At this stage you may worry that performance will suffer if each statement in a procedure is individually recompiled. Rest assured that the initial compilation is still on the module level, so only if a recompile is needed is it done per statement. Another performance benefit in SQL Server 2005 is the fact that when statement recompilation is done, the execution context will not be invalidated. The procedure in Listing 7-1 can be used in SQL Server 2005 to compare the differences between SQL Server 2000 and 2005. In SQL Server 2005, follow the steps listed earlier and notice in the trace how a recompile happens only the first time; for each subsequent execution, there is no recompile. This is due to the fact that an execution plan will be created after the initial recompile. Run the following code after you have executed the procedure a couple of times, and notice that the result you get consists of both a compiled plan and an executable plan. SELECT * FROM syscacheobjects WHERE dbid = db_id( pubs ) AND objid = object_id( test2 ) Figure 7-3: Trace Output

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

Bookmark the permalink.

Comments are closed.