Microsoft KB Archive/65181

= INFO: Interpretation of SET STATISTICS TIME ON Results =

Article ID: 65181

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q65181



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 &quot;Microsoft SQL Server is shipping now!&quot;

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 and 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 &quot;if a = b.&quot; 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 &quot;print3.&quot;

Keywords: kbinfo kbprogramming KB65181

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.