showplan and statistics io

Once you have identified problem areas within your query, you can focus on those, using the tools that were described on Day 15. To use these tools, however, it’s important to understand how they relate to stored procedures, and to do that you need to learn about procedure compilation and execution.

Review of Batch Processing

A standard SQL batch goes through four general steps:

•  Parse—Verify the syntax and check for valid object names.

•  Optimize—Determine the most efficient execution path.

•  Compile—Generate executable code.

•  Execute—Run the batch.

Every time you execute a batch, it runs through these steps.

Stored Procedure Processing

Stored procedures are also subject to the same steps, but they don’t happen all at once. Here’s a summary of when they happen:

•  Parse—At creation time, procedure syntax and object names are checked.

•  Optimize—Whenever a new procedure plan is required by the server, it optimizes the procedure with respect to the current set of parameters. (Much more on this topic in a moment.)

•  Compile—Occurs right after optimize, but only when a new plan is required.

•  Execute—Occurs every time a procedure is run.

The key to understanding how stored procedures are compiled is to understand how the server uses procedure cache.

Procedure cache is a reserved area within the memory cache where compiled code is retained for reuse. The compiled code is retained for all coded objects, including procedures, triggers, rules, defaults, and constraints.

When are stored procedures optimized? When you execute a procedure, the server generates an optimization plan for the batch, which does not include a plan for the procedure. Optimization of the procedure is postponed until the procedure itself is executed.

Let’s assemble a test to demonstrate this last point. First, turn on showplan. Next, write a small procedure in pubs called test_execution. Finally, write a batch to execute the procedure within a condition that is never true:

set   showplan   on
go
create   proc   test_execution   (@title char(12))
a s
select    count(*)
     from   titles
    where   title   =   @title
return   0
go
if  1  =  2
begin
         print    “executing   the   procedure”
          exec    test_execution   “business”
         select   count(*)   from   publishers
end
go

STEP 1
The type of query is COND
STEP 1
The type of query is PRINT
STEP 1
The type of query is EXECUTE
STEP 1
The type of query is SELECT
Scalar Aggregate
FROM TABLE
publishers
Nested iteration
Table Scan
STEP 2
The type of query is SELECT
STEP 1
The type of query is GOTO

Let’s take a moment to read this closely. Each STEP 1 corresponds to a statement in the batch. The first is the conditional (COND) IF statement. The second is a PRINT, followed by an EXECUTE, a SELECT, and finally, a GOTO statement that is implicit from the condition. (Notice that the second SELECT statement is used to retrieve the result of the aggregate to the user. All aggregates are processed in two steps.)

You can learn several important points from this output. First, the creation of the procedure itself gives no showplan output; procedures are not optimized when they are created.

---

Note:  Key Point
The showplan option provides output at optimization time. If there is no optimization taking place, there will be no showplan output.

---

The server also didn’t optimize the procedure when it optimized the batch in which it was executed. Procedures are not optimized as a part of an execution batch.

Notice that the optimizer did generate a plan for the SELECT statement inside the condition, even though the condition would never be true. The optimizer generates a plan for each statement in a batch or procedure and does not evaluate any conditions.

Let’s change the condition from false to true and observe the stored procedure optimization:

if 1 = 1
begin
   print   “executing   the   procedure”
    exec   test_execution   “business”
   select   count(*)   from   publishers
end

---

Note:  I’ve inserted some comments (in italics) in the showplan output to help you sort out what’s what.

---

-- Start batch optimization here

STEP 1
The type of query is COND
STEP 1
The type of query is PRINT
STEP 1
The type of query is EXECUTE
STEP 1
The type of query is SELECT
Scalar Aggregate
FROM TABLE
publishers
Nested iteration
Table Scan
STEP 2
The type of query is SELECT
STEP 1
The type of query is GOTO

-- End batch optimization here

-- Start batch execution here

    executing the procedure

-- Start procedure optimization here

STEP 1
The type of query is DECLARE
STEP 1
The type of query is SELECT
Scalar Aggregate
FROM TABLE
titles
Nested iteration
Table Scan
STEP 2
The type of query is SELECT
STEP 1
The type of query is SELECT

-- End procedure optimization here

-- Start procedure execution here

    …

The most important point is that procedure optimization is deferred until the procedure is executed. The clearest indication of this is that batch output (the print message) is displayed before procedure optimization output.

Stored Procedure Optimization

Let’s look at what the server does each time a procedure is executed. Consider the execution of our performance tracking procedure, show_tracking. Here’s a complete execution batch:

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