Using set showplan with noexec

You can use set noexec with set showplan on to view a query plan without executing the query. For example, this query prints the query plan but also executes the queries, which might be time consuming:

set showplan on
go
select * from really_big_table
select * from really_really_big_table
go

However, if you include set noexec, you can view the query plan without running the query.

Stored procedures are compiled when they are first used, or if the resultant compiled plan is already in use by another session, so set noexec can have unexpected results, and Sybase recommends that you use set fmtonly on instead. If you include a stored procedures inside another stored procedure, the second stored procedure is not run when you enable set noexec. For example, if you create two stored procedures:

create procedure sp_B
   as
   begin
       select * from authors
   end

and

create procedure sp_A
   as
   begin
       select * from titles
       execute sp_B
   end

Individually, their query plans look like this:

set showplan on
sp_B

QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1
            The type of query is EXECUTE.
QUERY PLAN FOR STATEMENT 1 (at line 4).

     STEP 1

       The type of query is SELECT.
       1 operator(s) under root
       |ROOT:EMIT Operator (VA = 1)
       |
       |   |SCAN Operator (VA = 0)
       |   |  FROM TABLE
       |   |  titles
       |   |  Table Scan.
       |   |  Forward Scan.
       |   |  Positioning at start of table.
       |   |  Using I/O Size 2 Kbytes for data pages.
       |   |  With LRU Buffer Replacement Strategy for data pages.

If you enable set noexec:

set noexec on
go
set showplan on
go
exec proc A
go

Adaptive Server produces no showplan output for procedure B because noexec is enabled, so Adaptive Server is not actually executing or compiling procedure B, and does not print any showplan output. If noexec was not enabled, Adaptive Server would have compiled and printed plans for both A and B stored procedures.

But if you use set fmtonly on:

use pubs2
go
create procedure sp_B
as
begin
     select * from authors
end
go
create procedure sp_A
as
begin
     select * from titles
     execute sp_B
end
go
set showplan on
go
set fmtonly on
go

QUERY PLAN FOR STATEMENT 1 (at line 1).


   STEP 1
     The type of query is SET OPTION ON.

sp_B
go

QUERY PLAN FOR STATEMENT 1 (at line 1).

   STEP 1
     The type of query is EXECUTE.

QUERY PLAN FOR STATEMENT 1 (at line 4).

     STEP 1

       The type of query is SELECT.
       1 operator(s) under root
       |ROOT:EMIT Operator (VA = 1)
       |
       |   |SCAN Operator (VA = 0)
       |   |  FROM TABLE
       |   |  authors
       |   |  Table Scan.
       |   |  Forward Scan.
       |   |  Positioning at start of table.
       |   |  Using I/O Size 2 Kbytes for data pages.
       |   |  With LRU Buffer Replacement Strategy for data pages. 


au_id         au_lname                                 au_fname
         phone         address
         city                 state   country     postalcode
----------    --------------------------------------   --------------------
         ------------  ----------------------------------------
         ------------------   -----   ----------  ----------

(0 rows affected)
(return status = 0)
sp_A
go

QUERY PLAN FOR STATEMENT 1 (at line 1).


   STEP 1
     The type of query is EXECUTE.


QUERY PLAN FOR STATEMENT 1 (at line 4).


     STEP 1

       The type of query is SELECT.
       1 operator(s) under root
       |ROOT:EMIT Operator (VA = 1)
       |
       |   |SCAN Operator (VA = 0)
       |   |  FROM TABLE
       |   |  titles
       |   |  Table Scan.
       |   |  Forward Scan.
       |   |  Positioning at start of table.
       |   |  Using I/O Size 2 Kbytes for data pages.
       |   |  With LRU Buffer Replacement Strategy for data pages. 


QUERY PLAN FOR STATEMENT 2 (at line 5).

   STEP 1
     The type of query is EXECUTE.

title_id
     title

     type           pub_id     price                 advance
     total_sales
     notes

     pubdate                 contract
--------
     --------------------------------------------------------------------
     -----------    --------   -------------------   -----------------------
     -----------
     ---------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
     --------------------    --------

(0 rows affected)

QUERY PLAN FOR STATEMENT 1 (at line 4).
     STEP 1

       The type of query is SELECT.
       1 operator(s) under root
       |ROOT:EMIT Operator (VA = 1)
       |
       |   |SCAN Operator (VA = 0)
       |   |  FROM TABLE
       |   |  authors
       |   |  Table Scan.
       |   |  Forward Scan.
       |   |  Positioning at start of table.
       |   |  Using I/O Size 2 Kbytes for data pages.
       |   |  With LRU Buffer Replacement Strategy for data pages. 

au_id         au_lname                                 au_fname
         phone         address
         city                 state   country     postalcode
----------    --------------------------------------   --------------------
         ------------  ----------------------------------------
         ------------------   -----   ----------   ----------

Both stored procedures are run and you see the resulting showplan output.