At this stage you may worry that performance Procedures, and in the SQL Server 2005 release, Microsoft has changed the model This indicates that the procedure has been recompiled.ħ-4: Trace Properties Dialog in SQL Server 2005Īs mentioned before, the cost of recompilation can be very high for large The output from the trace will show a couple of entries in the Event ClassĬolumn with the value of SP:Recompile, as in Figureħ-3. In the Stored Procedures event group, choose the SP:Recompile event, clickĮxecute the procedure a couple of times from Query Analyzer and view the When the Trace Properties dialog comes up, choose the Events Open the SQL Server Profiler and from the File menu, select New | To verify that recompilation happens on SQL Server 2000, do the Listing 7-1: Procedure That Causes Recompilation CREATE PROCEDURE test2 When this runs against SQL ServerĢ000, a recompilation happens for each execution of the procedure. Listing 7-1 is a stored procedure which in the middle of the procedureĬhanges the CONCAT_NULL_YIELDS_NULL option. The code in Listing 7-1 is extremely simple, but it Recompilation happens because a SET option changes, the executable plan will be Recompilation affects only a small portion of the procedure. Other words, the whole procedure is recompiled even if the cause of the Or batch is large, because SQL Server 2000 does module-level recompilation. Things that cause recompilation to occur are as follows:ħ-2: Trace Properties Dialog for SQL ProfilerĪ recompilation can incur a huge cost especially if the procedure, function, Problem with executing stored procedures, and that is recompilation. Has very much improved the execution of not only stored procedures butįunctions, batches, dynamic queries, and so on. This architecture, paired with the fact that the execution context is reused, This data structure, which is called the executable plan orĮxecution context, holds the data specific to each user's execution, such Query plan in memoryone copy for all serial executions and another for allĮxecutable plan≺ data structure for each user that concurrentlyĮxecutes the query. There are never more than one or two copies of the The plan and no user context information (such as data variable values) is The plan is reentrant, which implies that all users share query plan)≺ read-only data structure used byĪny number of users. Is divided into two structures: a compiled plan and an executable plan.Ĭompiled plan (a.k.a. Server 2000, the query plan architecture differs from previous versions, and it Of the infrastructural improvements in T-SQL: statement recompilation. The next thing we'll look at is a performance enhancement that is part Learn More Buy Statement-Level Recompilation If not, performance may suffer.First Look at SQL Server 2005 for Developers, A If these values represent the typical ones with which the procedure is called subsequently, then the stored procedure benefits from the query plan each time it compiles and executes. When SQL Server executes stored procedures, any parameter values used by the procedure when it compiles are included as part of generating the query plan. In this situation, it can be useful to force the stored procedure to recompile the next time it executesĪnother reason to force a stored procedure to recompile is to counteract, when necessary, the "parameter sniffing" behavior of stored procedure compilation. But if a new index is added from which the stored procedure might benefit, optimization does not happen until the next time the stored procedure is run after Microsoft SQL Server is restarted. It also occurs if an underlying table used by the stored procedure changes. This optimization happens automatically the first time a stored procedure is run after Microsoft SQL Server is restarted. "As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |