ID Number: Q65181
1.00 1.10 4.20
OS/2
Summary:
This article addresses several questions that have been asked
concerning interpretation of the results returned when the SET
STATISTICS TIME ON option is executed under SQL Server.
More Information:
When using SET STATISTICS TIME ON, it is quite informative to also
use the SET SHOWPLAN ON option. These two commands work together to
provide user information concerning how the optimizer has broken up
your SQL query into a series of internal queries on a query processing
tree and how much time was taken to execute each portion of the
optimized query.
1. Q. Do all SQL Server commands generate EXECUTION statements?
A. Yes. In fact, you will even receive an execution statement when
you issue SET STATISTICS TIME OFF to terminate the reporting
process.
2. Q. What generates the PARSE & COMPILE statements that are returned?
A. These are not actually SQL statements, but are a timing report
providing information concerning how much time the SQL Server
program took to do the following:
a. Parse your SQL command by checking for syntax errors, breaking
the command up into component parts, and producing an internal
execution tree.
b. Compile an execution plan in cache memory from the execution
tree that has just been produced.
Note that this includes the amount of time that is required to
move the data from the associated tables into the memory cache;
therefore, if you execute the same query twice, and the table
data is still resident in the cache, your Parse and Compile
times will be considerably lower for the second execution of the
query.
3. Q. Why does the command sequence in the following query generate
three EXECUTION statements when there are only two commands?
if 1 = 1
print "Microsoft SQL Server is shipping now!"
A. The Execution statements do not have a one-to-one correspondence
with your SQL statements but relate to each node of the internal
execution tree. Using SET SHOWPLAN ON together with SET
STATISTICS TIME ON will, in many cases, help to illustrate what
is actually occurring internally in order for SQL Server to
execute your statements.
4. Q. Why does the same sequence of commands generate more PARSE &
COMPILE and EXECUTION statements if the commands are executed
from within a stored procedure?
A. Executing commands from within a stored procedure adds an
additional internal layer of overhead for the SQL Server engine.
There is an additional Parse and Compile for the stored
procedure itself, and when executed, the stored procedure must
be executed to initiate the same series of commands; hence, an
extra Execution statement will also be displayed. This
additional amount of overhead is more than offset by the fact
that once the stored procedure is produced, the internal
execution tree will be retained and the compiled execution plan
will be stored in the procedure cache. This will significantly
reduce the Parse and Compile overhead for subsequent executions
of the stored procedure.
5. Q. If the result of a conditional command is false, is an EXECUTION
statement generated? For example:
if a = b
begin
print1
print2
end
else
begin
print3
print4
end
If a != b, which command does the first EXECUTION statement
pertain to?
A. The Execution statements will pertain to the portions of the
internal execution tree that are actually executed. In the
example above, the first Execution statement will apply to the
conditional statement "if a = b." The next Execution statement
will apply to the node on the execution tree that is actually
executed as a result of the conditional statement, in this case,
to the portion that relates to "print3."