Each time the server executes the procedure, it looks first in the procedure cache to see whether there is already a compiled plan for the procedure. Why would a plan already exist? Another user may have executed the procedure during the time SQL Server has been running. If that plan hasn’t been paged out because another procedure needed the space, the server will use it and skip the optimization step entirely.

---

Note:  Can you actually observe this happening? Not with showplan. If the showplan option is on, SQL Server will generate a new optimization plan for your procedure, even if another plan is already in procedure cache.

If you have sa privileges (or if you know someone who does!), you can use dbcc memusage to observe the contents of the procedure cache. Choose a quiet time when there aren’t a lot of users; then turn on showplan and execute the same procedure three or four times. Now run this batch to look at the contents of the cache:

dbcc traceon(3604)
dbcc memusage
dbcc traceoff(3604)

Here’s what a part of the output looked like for me after several executions of the test we ran earlier:

Procedure Name: test_execution
Database Id: 5
Object Id: 1600008731
Version: 1
Uid: 1
Type: stored procedure
Number of trees: 0
Size of trees: 0.000000 Mb, 0. bytes, 0 pages
Number of plans: 5
Size of plans: 0.013199 Mb, 13840.000000 bytes, 10 pages

The crucial line is the one indicating the number of plans. The stored procedure cache is now storing five separate versions of this procedure. Because the showplan option was on, the server continued to generate additional plans each time I ran the test. After turning off showplan and executing the test several more times, I was able to run dbcc memusage again and found the same number of plans in memory.

---

Figure 17.1 shows SQL Server’s basic algorithm for determining stored procedure processing. First, the server checks procedure cache for an available plan. (If multiple people are running a procedure at once, the server will need to generate a plan for each concurrent user; that’s why there may be multiple plans.) If one is found, the server passes the parameter values to the procedure and executes it. If a plan is not found, the server substitutes the parameter values into each statement in the procedure and prepares an optimization plan. It then compiles that plan, passes the parameters, and executes it.

Figure 17.1.  Stored procedures are optimized only when a new plan is needed.

Understanding Stored Procedure Compilation

In general, it’s good that stored procedures can reuse optimization plans from procedure cache. Optimization takes time and costs cycles. It’s unnecessary to recompile procedures whose optimization plans will never change from one execution to the next.

On the other hand, there are procedures that must be re-optimized each time they are run. Consider this query:

select   sum(qty)
from   sales
where    title_id   like   @title_id

Depending on the value of @title_id, this query could find all rows in the table (%) or very few (CH2140). If the number of rows found gets low enough, it makes sense for the query to use a nonclustered index. Until then, a table scan method is more efficient.

To test this out, I tried running the SELECT query from the procedure using several different values. Table 17.1 shows the plan the optimizer chose given various values in the LIKE clause, as well as the cost of using a table scan versus a nonclustered index for each approach.

As you can see, there is a lot at stake here. Running a very precise query (CH21%) using a table scan can cause a lot of unnecessary work, but nothing like the time wasted when you run a very general query (C%) using a nonclustered index. In this case, the improper use of a plan calling for a nonclustered index resulted in ten times as much work. In addition, SQL Server was unable to use read-ahead reads for most of the work because of the random distribution of data with respect to a nonclustered index.

Here’s the problem: The first time you run a procedure containing a query with a WHERE clause returning a variable number of rows, the value of the parameter is substituted into the SELECT statement and the statement is optimized for that parameter. If you run the procedure later and the plan is found in cache, you get the earlier optimization plan, even if that plan is completely inappropriate for your current query.

Controlling Procedure Compilation

One way to avoid these problems is to execute a procedure WITH RECOMPILE. This option allows you to request a new optimization plan prior to execution. Here’s how you would do it:

declare @ret int
exec @ret = show_tracking with recompile
if @ret != 0
    print “no results: error in procedure”
else
    select * from perf_tracker

It’s typical that users request a new plan whenever they are certain that any plans in memory are inappropriate for them. Remember, the server does not attempt to find a plan that is well-suited to your parameters; it simply chooses the first plan it finds for the procedure you are executing.

In our earlier query using the title_id like … example, if a user knew that the procedure typically ran with a very broad condition and that this execution would have a more narrow search condition, he might request a new plan to guarantee best performance.

The downside of EXECUTE … WITH RECOMPILE is that it does nothing to solve the overall problem. Plans generated by WITH RECOMPILE are left behind in cache. Other unsuspecting users will be executing with wildly inappropriate execution plans.