You can use SQL Server Profiler to watch automatic recompilation occur: Trace the event called SP:Recompile in the Stored Procedures category. If you also trace the event called SP:StmtStarting, you can see at what points the procedure is recompiling. Note that this event shows up in the Profiler's event list only if you enable the Profiler option to show all events. One way to avoid repeated recompilation is to include, at the beginning of the procedure, all data-definition statements dealing with temporary tables and inserting rows into the temporary tables. So if the procedure must be recompiled, the compilation won't happen more than once. Another way to prevent recompilation is to include the query hint KEEP PLAN in your statements that access the temporary tables. This hint is part of the OPTION clause and looks something like
SELECT <some columns>
FROM #newly_built_temp_table
OPTION (KEEP PLAN)
This hint tells SQL Server to use any plan that has been saved for this statement, even if you've made several changes to the temporary table.
Limiting the Number of Cached Plans
SQL Server limits the number of plans for each stored procedure. Because plans are reentrant in SQL Server 7.0, limiting plans is much easier than in previous releases. The online documentation states that any procedure can have, at most, two compiled plans (one for parallel plans—those that execute on multiple processors—and one for nonparallel plans), but there are exceptions. Certain situations cause SQL Server to store multiple plans for the same procedure. The most likely situation is a difference in certain SET options, database options, or configuration options. For example, a stored procedure that concatenates strings might compile the concatenation differently depending on whether the option CONCAT_NULL_YIELDS_NULL is on, or whether the corresponding database option is true. If a user executes the procedure with an option on, that execution uses a different plan from the one it would use if the option were off.
As with ad hoc and autoparameterized queries, the system table syscacheobjects can help you see when new plans are created and when existing ones are reused. You can get this information from the following query:
SELECT sql, cacheobjtype, usecounts
FROM master..syscacheobjects
WHERE objtype = 'Proc'
The value in the sql column is the name of the stored procedure. The value of the cacheobjtype column identifies whether the plan is a compiled plan (the part of a plan that is reentrant) or an executable plan (an instance of the compiled plan that contains information describing a process that is executing the query). In most cases, both compiled and executable plans remain in the memory cache, but you'll find exceptions if you run tests. In particular, if your stored procedure involves any sorting operations, you'll see the compiled plans in cache, but the executable plans aren't saved. Watching the usecounts result column from this query shows you how often a particular plan is reused. In addition, if running this query shows you multiple plans for the same procedure (i.e., the same procedure name shows up more than once with the same cacheobjtype value), you might want to return the value of the setopts column. This value is a bitmap that tells you which session and database options are enabled for the procedure. Two plans for the same procedure usually have different values for setopts.
Although many people assume that recompilation is something to avoid, this assumption is not always valid. If you know that updated statistics can improve the query plan, or if you know that you have wildly different possible parameter values, recompilation can be a good thing. Using stored procedures when developing your applications lets you determine when SQL Server reuses an existing query plan and when SQL Server creates a new one.
End of Article
Prev. page
1
[2]
next page -->
