Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011
    Posts
    18

    Unanswered: set showplan isn't working?

    Hi, after having executed
    Code:
    SET SHOWPLAN ON
    I am executing a simple select statement, where the <conditions> block is nothing but a bunch of AND statements using all the columns in the index.
    Code:
    SELECT  * 
    FROM    Trade
    WHERE   <conditions>
    However, I'm seeing the following. Two things:
    1) It doesn't appear the FROM TABLE descriptions have anything to do with the table I'm selecting from.
    2) Not sure why I'm seeing "QUERY PLAN FOR STATEMENT 29 (at line 137)." as there's only one statement and a few lines.
    3) Not sure why it's using a table scan instead of the index.

    Any ideas?


    STEP 1
    The type of query is GOTO.


    QUERY PLAN FOR STATEMENT 29 (at line 137).


    STEP 1
    The type of query is COND.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 30 (at line 139).


    STEP 1
    The type of query is SET OPTION ON.


    QUERY PLAN FOR STATEMENT 31 (at line 141).


    STEP 1
    The type of query is COND.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 32 (at line 143).


    STEP 1
    The type of query is SET OPTION ON.


    QUERY PLAN FOR STATEMENT 33 (at line 145).


    STEP 1
    The type of query is COND.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 34 (at line 147).


    STEP 1
    The type of query is SET OPTION ON.


    QUERY PLAN FOR STATEMENT 35 (at line 0).


    STEP 1
    The type of query is GOTO.


    QUERY PLAN FOR STATEMENT 1 (at line 0).


    STEP 1
    The type of query is DECLARE.


    QUERY PLAN FOR STATEMENT 2 (at line 21).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 3 (at line 22).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 4 (at line 23).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 5 (at line 26).


    STEP 1
    The type of query is COND.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 6 (at line 28).


    STEP 1
    The type of query is SET OPTION OFF.


    QUERY PLAN FOR STATEMENT 7 (at line 30).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 8 (at line 31).


    STEP 1
    The type of query is SET OPTION ON.


    QUERY PLAN FOR STATEMENT 9 (at line 37).


    STEP 1
    The type of query is SET ROW COUNT.


    QUERY PLAN FOR STATEMENT 10 (at line 41).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 11 (at line 49).


    STEP 1
    The type of query is COND.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 12 (at line 51).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 13 (at line 58).


    STEP 1
    The type of query is COND.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 14 (at line 60).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 15 (at line 71).


    STEP 1
    The type of query is COND.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 16 (at line 73).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 17 (at line 74).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 18 (at line 75).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 19 (at line 79).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 20 (at line 80).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 21 (at line 86).


    STEP 1
    The type of query is COND.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 22 (at line 88).


    STEP 1
    The type of query is SELECT.

    3 operator(s) under root

    |ROOT:EMIT Operator (VA = 3)
    |
    | |HASH UNION Operator (VA = 2) has 2 children.
    | | Using Worktable1 for internal storage.
    | | Key Count: 3
    | |
    | | |SCALAR Operator (VA = 0)
    | |
    | | |SCAN Operator (VA = 1)
    | | | FROM TABLE
    | | | master..spt_mda
    | | | Table Scan.
    | | | Forward Scan.
    | | | Positioning at start of table.
    | | | Using I/O Size 16 Kbytes for data pages.
    | | | With LRU Buffer Replacement Strategy for data pages.


    ...... to be continued ........

  2. #2
    Join Date
    Oct 2011
    Posts
    18
    The 2nd portion of the plan......

    QUERY PLAN FOR STATEMENT 23 (at line 98).


    STEP 1
    The type of query is COND.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 24 (at line 100).


    STEP 1
    The type of query is SELECT.

    4 operator(s) under root

    |ROOT:EMIT Operator (VA = 4)
    |
    | |HASH UNION Operator (VA = 3) has 2 children.
    | | Using Worktable1 for internal storage.
    | | Key Count: 3
    | |
    | | |SCALAR Operator (VA = 0)
    | |
    | | |RESTRICT Operator (VA = 2)(0)(0)(0)(7)(0)
    | | |
    | | | |SCAN Operator (VA = 1)
    | | | | FROM TABLE
    | | | | master..spt_mda
    | | | | Table Scan.
    | | | | Forward Scan.
    | | | | Positioning at start of table.
    | | | | Using I/O Size 16 Kbytes for data pages.
    | | | | With LRU Buffer Replacement Strategy for data pages.



    QUERY PLAN FOR STATEMENT 25 (at line 112).


    STEP 1
    The type of query is COND.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 26 (at line 114).


    STEP 1
    The type of query is SELECT.

    4 operator(s) under root

    |ROOT:EMIT Operator (VA = 4)
    |
    | |HASH UNION Operator (VA = 3) has 2 children.
    | | Using Worktable1 for internal storage.
    | | Key Count: 3
    | |
    | | |SCALAR Operator (VA = 0)
    | |
    | | |RESTRICT Operator (VA = 2)(0)(0)(0)(13)(0)
    | | |
    | | | |SCAN Operator (VA = 1)
    | | | | FROM TABLE
    | | | | master..spt_mda
    | | | | Table Scan.
    | | | | Forward Scan.
    | | | | Positioning at start of table.
    | | | | Using I/O Size 16 Kbytes for data pages.
    | | | | With LRU Buffer Replacement Strategy for data pages.



    QUERY PLAN FOR STATEMENT 27 (at line 0).


    STEP 1
    The type of query is GOTO.


    QUERY PLAN FOR STATEMENT 28 (at line 0).


    STEP 1
    The type of query is GOTO.


    QUERY PLAN FOR STATEMENT 29 (at line 137).


    STEP 1
    The type of query is COND.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 30 (at line 139).


    STEP 1
    The type of query is SET OPTION ON.


    QUERY PLAN FOR STATEMENT 31 (at line 141).


    STEP 1
    The type of query is COND.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 32 (at line 143).


    STEP 1
    The type of query is SET OPTION ON.


    QUERY PLAN FOR STATEMENT 33 (at line 145).


    STEP 1
    The type of query is COND.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 34 (at line 147).


    STEP 1
    The type of query is SET OPTION ON.


    QUERY PLAN FOR STATEMENT 35 (at line 0).


    STEP 1
    The type of query is GOTO.


    QUERY PLAN FOR STATEMENT 1 (at line 0).


    STEP 1
    The type of query is DECLARE.


    QUERY PLAN FOR STATEMENT 2 (at line 21).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 3 (at line 22).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 4 (at line 23).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 5 (at line 26).


    STEP 1
    The type of query is COND.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 6 (at line 28).


    STEP 1
    The type of query is SET OPTION OFF.


    QUERY PLAN FOR STATEMENT 7 (at line 30).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 8 (at line 31).


    STEP 1
    The type of query is SET OPTION ON.


    QUERY PLAN FOR STATEMENT 9 (at line 37).


    STEP 1
    The type of query is SET ROW COUNT.


    QUERY PLAN FOR STATEMENT 10 (at line 41).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 11 (at line 49).


    STEP 1
    The type of query is COND.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 12 (at line 51).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 13 (at line 58).


    STEP 1
    The type of query is COND.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 14 (at line 60).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 15 (at line 71).


    STEP 1
    The type of query is COND.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 16 (at line 73).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 17 (at line 74).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 18 (at line 75).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)

  3. #3
    Join Date
    Oct 2011
    Posts
    18
    And the remainder of the plan......

    QUERY PLAN FOR STATEMENT 19 (at line 79).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 20 (at line 80).


    STEP 1
    The type of query is SELECT.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 21 (at line 86).


    STEP 1
    The type of query is COND.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 22 (at line 88).


    STEP 1
    The type of query is SELECT.

    3 operator(s) under root

    |ROOT:EMIT Operator (VA = 3)
    |
    | |HASH UNION Operator (VA = 2) has 2 children.
    | | Using Worktable1 for internal storage.
    | | Key Count: 3
    | |
    | | |SCALAR Operator (VA = 0)
    | |
    | | |SCAN Operator (VA = 1)
    | | | FROM TABLE
    | | | master..spt_mda
    | | | Table Scan.
    | | | Forward Scan.
    | | | Positioning at start of table.
    | | | Using I/O Size 16 Kbytes for data pages.
    | | | With LRU Buffer Replacement Strategy for data pages.



    QUERY PLAN FOR STATEMENT 23 (at line 98).


    STEP 1
    The type of query is COND.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 24 (at line 100).


    STEP 1
    The type of query is SELECT.

    4 operator(s) under root

    |ROOT:EMIT Operator (VA = 4)
    |
    | |HASH UNION Operator (VA = 3) has 2 children.
    | | Using Worktable1 for internal storage.
    | | Key Count: 3
    | |
    | | |SCALAR Operator (VA = 0)
    | |
    | | |RESTRICT Operator (VA = 2)(0)(0)(0)(7)(0)
    | | |
    | | | |SCAN Operator (VA = 1)
    | | | | FROM TABLE
    | | | | master..spt_mda
    | | | | Table Scan.
    | | | | Forward Scan.
    | | | | Positioning at start of table.
    | | | | Using I/O Size 16 Kbytes for data pages.
    | | | | With LRU Buffer Replacement Strategy for data pages.



    QUERY PLAN FOR STATEMENT 25 (at line 112).


    STEP 1
    The type of query is COND.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 26 (at line 114).


    STEP 1
    The type of query is SELECT.

    4 operator(s) under root

    |ROOT:EMIT Operator (VA = 4)
    |
    | |HASH UNION Operator (VA = 3) has 2 children.
    | | Using Worktable1 for internal storage.
    | | Key Count: 3
    | |
    | | |SCALAR Operator (VA = 0)
    | |
    | | |RESTRICT Operator (VA = 2)(0)(0)(0)(13)(0)
    | | |
    | | | |SCAN Operator (VA = 1)
    | | | | FROM TABLE
    | | | | master..spt_mda
    | | | | Table Scan.
    | | | | Forward Scan.
    | | | | Positioning at start of table.
    | | | | Using I/O Size 16 Kbytes for data pages.
    | | | | With LRU Buffer Replacement Strategy for data pages.



    QUERY PLAN FOR STATEMENT 27 (at line 0).


    STEP 1
    The type of query is GOTO.


    QUERY PLAN FOR STATEMENT 28 (at line 0).


    STEP 1
    The type of query is GOTO.


    QUERY PLAN FOR STATEMENT 29 (at line 137).


    STEP 1
    The type of query is COND.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 30 (at line 139).


    STEP 1
    The type of query is SET OPTION ON.


    QUERY PLAN FOR STATEMENT 31 (at line 141).


    STEP 1
    The type of query is COND.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 32 (at line 143).


    STEP 1
    The type of query is SET OPTION ON.


    QUERY PLAN FOR STATEMENT 33 (at line 145).


    STEP 1
    The type of query is COND.

    1 operator(s) under root

    |ROOT:EMIT Operator (VA = 1)
    |
    | |SCALAR Operator (VA = 0)



    QUERY PLAN FOR STATEMENT 34 (at line 147).


    STEP 1
    The type of query is SET OPTION ON.


    QUERY PLAN FOR STATEMENT 35 (at line 0).


    STEP 1
    The type of query is GOTO.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •