INF: Interpretation of SET STATISTICS TIME ON Results

  - Microsoft SQL Server version 4.2 for 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."