Sybase FAQ: 10/19 - ASE Admin (7 of 7)

Sybase FAQ: 10/19 - ASE Admin (7 of 7)

Post by david.ow » Sat, 04 Aug 2001 11:43:39



From: do...@midsomer.org (David Owen)

Posted-By: auto-faq 3.3.1 beta (Perl 5.005)
Archive-name: databases/sybase-faq/part10
URL: http://www.isug.com/Sybase_FAQ
Version: 1.2
Maintainer: David Owen
Last-modified: 2000/06/07
Posting-Frequency: posted every 3rd month
   A how-to-find-the-FAQ article is posted on the intervening months.

1.5.9: You and showplan output

----------------------------------------------------------------------
--------

Microsoft SQL Server includes a very intelligent cost-based query
 optimizer
which, given an ad-hoc query, can quickly determine the best access
 method for
retrieving the data, including the order in which to join tables and
 whether or
not to use indexes that may be on those tables. By using a cost-based
 query
optimizer, the System Administrator or end user is released from
 having to
determine the most efficient way of structuring the query to get
 optimal
performance -- instead, the optimizer looks at all possible join
 orders, and
the cost of using each index, and picks the plan with the least cost
 in terms
of page I/O's.

Detailed information on the final access method that the optimizer
 chooses can
be displayed for the user by executing the Transact-SQL "SET SHOWPLAN
 ON"
command. This command will show each step that the optimizer uses in
 joining
tables and which, if any, indexes it chooses to be the least-cost
 method of
accessing the data. This can be extremely beneficial when analyzing
 certain
queries to determine if the indexes that have been defined on a table
 are
actually being considered by the optimizer as useful in getting to
 the data.
This document will define and explain each of the output messages
 from
SHOWPLAN, and give example queries and the output from SHOWPLAN to
 illustrate
the point. The format will be consistent throughout: a heading which
corresponds to the exact text of a SHOWPLAN statement, followed by a
description of what it means, a sample query which generates that
 particular
message, and the full output from executing the query with the
 SHOWPLAN option
on. Wherever possible, the queries will use the existing tables and
 indexes,
unaltered, from the SQL Server "Pubs" sample database.

STEP n

This statement will be included in the SHOWPLAN output for every
 query, where n
is an integer, beginning with "STEP 1". For some queries, SQL Server
 cannot
effectively retrieve the results in a single step, and must break the
 query
plan into several steps. For example, if a query includes a GROUP BY
 clause,
the query will need to be broken into at least two steps: one step to
 select
the qualifying rows from the table, and another step to group them.
 The
following query demonstrates a singlestep query.
        Query:          SELECT au_lname, au_fname
                        FROM Authors
                        WHERE city = "Oakland"

        SHOWPLAN:       STEP 1
                        The type of query is SELECT
                        FROM TABLE
                        authors
                        Nested iteration
                        Table Scan

The type of query is SELECT (into a worktable)

This SHOWPLAN statement indicates that SQL Server needs to insert
 some of the
query results into an intermediate worktable, and later in the query
 processing
will then select the values out of that table. This is most often
 seen with a
query which involves a GROUP BY clause, as the results are first put
 into a
work table, and then the qualifying rows in the work table are
 grouped based on
the given column in the GROUP BY clause. The following query returns
 a list of
all cities and indicates the number of authors that live in each
 city. The
query plan is composed of two steps: the first step selects the rows
 into a
worktable, and the second step retrieves the grouped rows from the
 worktable:
        Query:          SELECT city, total_authors = count(*)
                        FROM Authors
                        GROUP BY city

        SHOWPLAN:       STEP 1
                        The type of query is SELECT (into a
                                                     worktable)
                        GROUP BY
                        Vector Aggregate
                        FROM TABLE
                        authors
                        Nested iteration
                        Table Scan
                        TO TABLE
                        Worktable

                        STEP 2
                        The type of query is SELECT
                        FROM TABLE
                        Worktable
                        Nested iteration
                        Table Scan

The type of query is <query type>

This statement describes the type of query for each step. For most
 user
queries, the value for <query type> will be SELECT, INSERT, UPDATE,
 or DELETE.
If SHOWPLAN is turned on while other commands are issued, the <query
 type> will
reflect the command that was issued. The following examples show
 various
outputs for different queries/commands:
        Query 1:        CREATE TABLE Mytab (col1 int)
        SHOWPLAN 1:     STEP 1
                        The type of query is TABCREATE

        Query 2:        INSERT Publishers
                        VALUES ("9904", "NewPubs", "Seattle", "WA")

        SHOWPLAN 2:     STEP 1
                        The type of query is INSERT
                        The update mode is direct
                        Table Scan
                        TO TABLE
                        publishers

The update mode is deferred

There are two methods or "modes" that SQL Server can use to perform
 update
operations such as INSERT, DELETE, UPDATE, and SELECT INTO. These
 methods are
called deferred update and direct update. When the deferred method is
 used, the
changes are applied to all rows of the table by making log records in
 the
transaction log to reflect the old and new value of the column(s)
 being
modified (in the case of UPDATE operations), or the values which will
 be
inserted or deleted (in the case of INSERT and DELETE, respectively).
 When all
of the log records have been constructed, the changes are then
 applied to the
data pages. This method generates more log records than a direct
 update
(discussed later), but it has the advantage of allowing the execution
 of
commands which may cascade changes throughout a table. For example,
 consider a
table which has a column "col1" with a unique index on it, and data
 values
numbered consecutively from 1 to 100 in that column. Assume an UPDATE
 statement
is executed to increase the value in each row by 1:
        Query 1:        UPDATE Mytable
                        SET col1 = col1 + 1

        SHOWPLAN 1:     STEP 1
                        The type of query is UPDATE
                        The update mode is deferred
                        FROM TABLE
                        Mytable
                        Nested iteration
                        Table Scan
                        TO TABLE
                        Mytable

Consider the consequences of starting at the first row in the table,
 and
updating each row, through the end of the table. Updating the first
 row (which
has an initial value of 1) to 2 would cause an error, as the unique
 index would
be violated since there is already a value of 2 in the table;
 likewise,
updating the second row (which has an initial value of 2) to 3 would
 also cause
a unique key violation, as would all rows through the end of the
 table, except
for the last row. By using deferred updates, this problem is easily
 avoided.
The log records are first constructed to show what the new values for
 each row
will be, the existing rows are deleted, and the new values inserted.

Just as with UPDATE commands, INSERT commands may also be deferred
 for very
similar reasons. Consider the following query (there is no clustered
 index or
unique index on the "roysched" table):
        Query 2:        INSERT roysched SELECT * FROM roysched

        SHOWPLAN 2:     STEP 1
                        The type of query is INSERT
                        The update mode is deferred
                        FROM TABLE
                        roysched
                        Nested iteration
                        Table Scan
                        TO TABLE
                        roysched

Since there is no clustered index on the table, the new rows will be
 added to
the end of the table. The query processor needs to be able to
 differentiate
between the existing rows that are currently in the table (prior to
 the INSERT
command) and the rows which will be inserted, so as to not get into a
continuous loop of selecting a row, inserting it at the end of the
 table,
selecting that row that it just inserted, and re-inserting it again.
 By using
the deferred method of inserting, the log records can be first be
 constructed
to show all of the currently-existing values in the table, then SQL
 Server will
re-read those log records to insert them into the table.

The update mode is direct

Whenever possible, SQL Server will attempt to use the direct method
 of applying
updates to tables, since it is faster and requires fewer log records
 to be
generated than the deferred method. Depending on the type of command,
 one or
more criteria must be met in order for SQL Server to perform the
 update using
the direct method. Those criteria are:

*INSERT: For the direct update method to be used for INSERT
 operations,
    the table into which the rows are being inserted cannot be a
 table which is
    being read from in the same command. The second query example in
 the
    previous section demonstrates this, where the rows are being
 inserted into
    the same table in which they are being selected from. In
 addition, if rows
    are being inserted into the target table, and one or more of the
 target
    table's columns appear in the WHERE clause of the query then the
 deferred
    method, rather than the direct method, will be used.
*SELECT INTO: When a table is being populated with data by means of a
    SELECT INTO command, the direct method will always be used to
 insert the
    new rows.
*DELETE: For the direct update method to be used for DELETE
 operations,
    the query optimizer must be able to determine that either 0 or 1
 rows
    qualify for the delete. The only means for it to verify this is
 to check
    that there is a unique index on the table, which is qualified in
 the WHERE
    clause of the DELETE command, and the target table is not joined
 with any
    other table(s).
*UPDATE: For the direct update method to be used for UPDATE
 operations,
    the same criteria apply as for DELETE: a unique index must exist
 such that
    the query optimizer can determine that no more than 1 row
 qualifies for the
    update, and the only table in the UPDATE command is the target
 table to
    update. In addition, all columns that are being updated must be
 datatypes
    that are fixed length, rather than variable-length. Note that any
 column
    that allows NULLs is internally stored by SQL Server as a
 variable-length
    datatype column.

        Query 1:        DELETE
                        FROM authors
                        WHERE au_id = "172-32-1176"

        SHOWPLAN 1:     STEP 1
                        The type of query is DELETE
                        The update mode is direct
                        FROM TABLE
                        authors
                        Nested iteration
                        Using Clustered Index
                        TO TABLE
                        authors

        Query 2:        UPDATE titles
                        SET type = "popular_comp"
                        WHERE title_id = "BU2075"

        SHOWPLAN 2:     STEP 1
                        The type of query is UPDATE
                        The update mode is direct
                        FROM TABLE
                        titles
                        Nested iteration
                        Using Clustered Index
                        TO TABLE
                        titles

        Query 3:        UPDATE titles
                        SET price = $5.99
                        WHERE title_id = "BU2075"

        SHOWPLAN 3:     STEP 1
                        The type of query is UPDATE
                        The update mode is deferred
                        FROM TABLE
                        titles
                        Nested iteration
                        Using Clustered Index
                        TO TABLE
                        titles

Note that the only difference between the second and third example
 queries is
the column of the table which is being updated. In the second query,
 the direct
update method is used, whereas in the third query, the deferred
 method is used.
This difference is due to the datatype of the column being updated:
 the
titles.type column is defined as "char(12) NOT NULL", while the
 titles.price
column is defined as "money NULL". Since the titles.price column is
 not a
fixed-length datatype, the direct method cannot be used.

GROUP BY

This statement appears in the SHOWPLAN output for any query that
 contains a
GROUP BY clause. Queries that contain a GROUP BY clause will always
 be at least
two-step queries: one step to select the qualifying rows into a
 worktable and
group them, and another step to return the rows from the worktable.
 The
following example illustrates this:
        Query:          SELECT type, AVG(advance),
                               SUM(ytd_sales)
                        FROM titles
                        GROUP BY type

        SHOWPLAN:       STEP 1
                        The type of query is SELECT (into a
                                                     worktable)
                        GROUP BY
                        Vector Aggregate
                        FROM TABLE
                        titles
                        Nested iteration
                        Table Scan
                        TO TABLE
                        Worktable

                        STEP 2
                        The type of query is SELECT
                        FROM TABLE
                        Worktable
                        Nested iteration
                        Table Scan

Scalar Aggregate

Transact-SQL includes the aggregate functions:

*AVG()
*COUNT()
*COUNT(*)
*MAX()
*MIN()
*SUM()

Whenever an aggregate function is used in a SELECT statement that
 does not
include a GROUP BY clause, it produces a single value, regardless of
 whether it
is operating on all of the rows in a table or on a subset of the rows
 defined
by a WHERE clause. When an aggregate function produces a single
 value, the
function is called a "scalar aggregate", and is listed as such by
 SHOWPLAN. The
following example shows the use of scalar aggregate functions:
        Query:          SELECT AVG(advance), SUM(ytd_sales)
                        FROM titles
                        WHERE type = "business"

        SHOWPLAN:       STEP 1
                        The type of query is SELECT
                        Scalar Aggregate
                        FROM TABLE
                        titles
                        Nested iteration
                        Table Scan

                        STEP 2
                        The type of query is SELECT
                        Table Scan

Notice that SHOWPLAN considers this a two-step query, which is very
 similar to
the SHOWPLAN from the GROUP BY query listed earlier. Since the query
 contains a
scalar aggregate, which will return a single value, SQL Server keeps
 internally
a "variable" to store the result of the aggregate function. It can be
 thought
of as a temporary storage space to keep a running total of the
 aggregate
function as the qualifying rows from the table are evaluated. After
 all rows
have been evaluated from the table (Step 1), the final value from the
"variable" is then selected (Step 2) to return the scalar aggregate
 result.

Vector Aggregate

When a GROUP BY clause is used in a query which also includes an
 aggregate
function, the aggregate function produces a value for each group.
 These values
are called "vector aggregates". The "Vector Aggregate" statement from
 SHOWPLAN
indicates that the query includes a vector aggregate. Below is an
 example query
and SHOWPLAN which includes a vector aggregate:
        Query:          SELECT title_id, AVG(qty)
                        FROM sales
                        GROUP BY title_id

        SHOWPLAN:       STEP 1
                        The type of query is SELECT (into a
                                                     worktable)
                        GROUP BY
                        Vector Aggregate
                        FROM TABLE
                        sales
                        Nested iteration
                        Table Scan
                        TO TABLE
                        Worktable

                        STEP 2
                        The type of query is SELECT
                        FROM TABLE
                        Worktable
                        Nested iteration
                        Table Scan

FROM TABLE

This SHOWPLAN step indicates the table that the query is reading
 from. In most
queries, the "FROM TABLE" will be followed on the next line by the
 name of the
table which is being selected from. In other cases, it may indicate
 that it is
selecting from a worktable (discussed later). The main importance of
 examining
the table names after the "FROM TABLE" output is to determine the
 order in
which the query optimizer is joining the tables. The order of the
 tables listed
after the "FROM TABLE" statements in the SHOWPLAN output indicate the
 same
order that the tables were joined; this order may be (and often times
 is)
different than the order that they are listed in the FROM clause of
 the query,
or the order that they appear in the WHERE clause of the query. This
 is because
the query optimizer examines all different join orders for the tables
 involved,
and picks the join order that will require the least amount of I/O's.
        Query:          SELECT authors.au_id, au_fname, au_lname
                        FROM authors, titleauthor, titles
                        WHERE authors.au_id = titleauthor.au_id
                        AND titleauthor.title_id = titles.title_id
                        AND titles.type = "psychology"

        SHOWPLAN:       STEP 1
                        The type of query is SELECT
                        FROM TABLE
                        titles
                        Nested iteration
                        Table Scan
                        FROM TABLE
                        titleauthor
                        Nested iteration
                        Table Scan
                        FROM TABLE
                        authors
                        Nested iteration
                        Table Scan

This query illustrates the order in which the SQL Server query
 optimizer
chooses to join the tables, which is not the order that they were
 listed in the
FROM clause or the WHERE clause. By examining the order of the "FROM
 TABLE"
statements, it can be seen that the qualifying rows from the titles
 table are
first located (using the search clause <titles.type = "psychology">).
 Those
rows are then joined with the titleauthor table (using the join
 clause <
titleauthor.title_id = titles.title_id>), and finally the titleauthor
 table is
joined with the authors table to retrieve the desired columns (using
 the join
clause <authors.au_id = titleauthor.au_id>).

TO TABLE

When a command is issued which makes or attempts to make a
 modification to one
or more rows of a table, such as INSERT, DELETE, UPDATE, or SELECT
 INTO, the
"TO TABLE" statement will show the target table which is being
 modified. For
some operations which require an intermediate step which inserts rows
 into a
worktable (discussed later), the "TO TABLE" will indicate that the
 results are
going to the "Worktable" table, rather than a user table. The
 following
examples illustrate the use of the "TO TABLE" statement:
        Query 1:        INSERT sales
                        VALUES ("8042", "QA973", "7/15/92", 7,
                                "Net 30", "PC1035")

        SHOWPLAN 1:     STEP 1
                        The type of query is INSERT
                        The update mode is direct
                        Table Scan
                        TO TABLE
                        sales

        Query 2:        UPDATE publishers
                        SET city = "Los Angeles"
                        WHERE pub_id = "1389"

        SHOWPLAN 2:     STEP 1
                        The type of query is UPDATE
                        The update mode is deferred
                        FROM TABLE
                        publishers
                        Nested iteration
                        Using Clustered Index
                        TO TABLE
                        publishers

Notice that the SHOWPLAN for the second query indicates that the
 publishers
table is used both as the "FROM TABLE" as well as the "TO TABLE". In
 the case
of UPDATE operations, the optimizer needs to read the table which
 contains the
row(s) to be updated, resulting in the "FROM TABLE" statement, and
 then needs
to modify the row(s), resulting in the "TO TABLE" statement.

Worktable

For some types of queries, such as those that require the results to
 be ordered
or displayed in groups, the SQL Server query optimizer may determine
 that it is
necessary to create its own temporary worktable. The worktable is
 used to hold
the intermediate results of the query, at which time the result rows
 can be
ordered or grouped, and then the final results selected from that
 worktable.
When all results have been returned, the worktable is automatically
 dropped.
The worktables are always created in the tempdb database, so it is
 possible
that the system administrator may have to increase the size of tempdb
 to
accommodate that queries which require very large worktables. Since
 the query
optimizer creates these worktables for its own internal use, the
 names of the
worktables will not be listed in the tempdb..sysobjects table.

Worktables will always need to be used when a query contains a GROUP
 BY clause.
For queries involving ORDER BY, it is possible that the ordering can
 be done
without the use of the worktable. If there is a clustered index on
 the column
(s) in the ORDER BY clause, the optimizer knows that the rows are
 already
stored in sorted order, so a sort in a worktable is not necessary
 (although
there are exceptions to this, depending on the sort order which is
 installed on
the server). Since the data is not stored in sorted order for
 nonclustered
indexes, the worktable will not be necessary if the cheapest access
 plan is by
using the nonclustered index. However, if the optimizer determines
 that
scanning the entire table will require fewer I/Os than using the
 nonclustered
index, then a worktable will need to be created for the ordering of
 the
results. The following examples illustrate the use of worktables:
        Query 1:        SELECT type, AVG(advance), SUM(ytd_sales)
                        FROM titles
                        GROUP BY type

        SHOWPLAN 1:     STEP 1
                        The type of query is SELECT (into a
                                                     worktable)
                        GROUP BY
                        Vector Aggregate
                        FROM TABLE
                        titles
                        Nested iteration
                        Table Scan
                        TO TABLE
                        Worktable

                        STEP 2
                        The type of query is SELECT
                        FROM TABLE
                        Worktable
                        Nested iteration
                        Table Scan

        Query 2:        SELECT *
                        FROM authors
                        ORDER BY au_lname, au_fname

        SHOWPLAN 2:     STEP 1
                        The type of query is INSERT
                        The update mode is direct
                        Worktable created for ORDER BY
                        FROM TABLE
                        authors
                        Nested iteration
                        Table Scan
                        TO TABLE
                        Worktable

                        STEP 2
                        The type of query is SELECT
                        This step involves sorting
                        FROM TABLE
                        Worktable
                        Using GETSORTED
                        Table Scan

        Query 3:        SELECT *
                        FROM authors
                        ORDER BY au_id

        SHOWPLAN 3:     STEP 1
                        The type of query is SELECT
                        FROM TABLE
                        authors
                        Nested iteration
                        Table Scan

In the third example above, notice that no worktable was created for
 the ORDER
BY clause. This is because there is a unique clustered index on the
authors.au_id column, so the data is already stored in sorted order
 based on
the au_id value, and an additional sort for the ORDER BY is not
 necessary. In
the second example, there is a composite nonclustered index on the
 columns
au_lname and au_fname. However, since the optimizer chose not to use
 the index,
and due to the sort order on the SQL Server, a worktable needed to be
 created
to accommodate the sort.

Worktable created for SELECT_INTO

SQL Server's SELECT INTO operation performs two functions: it first
 creates a
table with the exact same structure as the table being selected from,
 and then
it insert all rows which meet the WHERE conditions (if a WHERE clause
 is used)
of the table being selected from. The "Worktable created for
 SELECT_INTO"
statement is slightly misleading, in that the "worktable" that it
 refers to is
actually the new physical table that is created. Unlike other
 worktables, it is
not dropped when the query finishes executing. In addition, the
 worktable is
not created in tempdb, unless the user specifies tempdb as the target
 database
for the new table.
        Query:          SELECT *
                        INTO seattle_stores
                        FROM stores
                        WHERE city = "seattle"

        SHOWPLAN:       STEP 1
                        The type of query is TABCREATE

                        STEP 2
                        The type of query is INSERT
                        The update mode is direct
                        Worktable created for SELECT_INTO
                        FROM TABLE
                        stores
                        Nested iteration
                        Table Scan
                        TO TABLE
                        Worktable

Worktable created for DISTINCT

When a query is issued which includes the DISTINCT keyword, all
 duplicate rows
are excluded from the results so that only unique rows are returned.
 To
accomplish this, SQL Server first creates a worktable to store all of
 the
results of the query, including duplicates, just as though the
 DISTINCT keyword
was not included. It then sorts the rows in the worktable, and is
 able to
easily discard the duplicate rows. Finally, the rows from the
 worktable are
returned, which insures that no duplicate rows will appear in the
 output.
        Query:          SELECT DISTINCT city
                        FROM authors

        SHOWPLAN:       STEP 1
                        The type of query is INSERT
                        The update mode is direct
                        Worktable created for DISTINCT
                        FROM TABLE
                        authors
                        FROM TABLE
                        authors
                        Nested iteration
                        Table Scan
                        TO TABLE
                        Worktable

                        STEP 2
                        The type of query is SELECT
                        This step involves sorting
                        FROM TABLE
                        Worktable
                        Using GETSORTED
                        Table Scan

Worktable created for ORDER BY

As discussed previously, queries which include an ORDER BY clause
 will often
require the use of a temporary worktable. When the optimizer cannot
 use an
available index for the ordering, it creates a worktable for use in
 sorting the
result rows prior to returning them. Below is an example which shows
 the
worktable being created for the ORDER BY clause:
        Query:          SELECT *
                        FROM authors
                        ORDER BY city

        SHOWPLAN:       STEP 1
                        The type of query is INSERT
                        The update mode is direct
                        Worktable created for ORDER BY
                        FROM TABLE
                        authors
                        FROM TABLE
                        authors
                        Nested iteration
                        Table Scan
                        TO TABLE
                        Worktable

                        STEP 2
                        The type of query is SELECT
                        This step involves sorting
                        FROM TABLE
                        Worktable
                        Using GETSORTED
                        Table Scan

Worktable created for REFORMATTING

When joining tables, SQL Server may in some cases choose to use a
 "reformatting
strategy" to join the tables and return the qualifying rows. This
 strategy is
only considered as a last resort, when the tables are large and
 neither table
in the join has a useful index to use. The reformatting strategy
 inserts the
rows from the smaller of the two tables into a worktable. Then, a
 clustered
index is created on the worktable, and the clustered index is then
 used in the
join to retrieve the qualifying rows from each table. The main cost
 in using
the reformatting strategy is the time and I/Os necessary to build the
 clustered
index on the worktable; however, that cost is still cheaper than
 joining the
tables with no index. If user queries are using the reformatting
 strategy, it
is generally a good idea to examine the tables involved and create
 indexes on
the columns of the tables which are being joined. The following
 example
illustrates the reformatting strategy. Since none of the tables in
 the Pubs
database are large enough for the optimizer to consider using this
 strategy,
two new tables are used. Each table has 5 columns defined as
 "char(200)". Tab1
has 500 rows and Tab2 has 250 rows.
        Query:          SELECT Tab1.col1
                        FROM Tab1, Tab2
                        WHERE Tab1.col1 = Tab2.col1

        SHOWPLAN:       STEP 1
                        The type of query is INSERT
                        The update mode is direct
                        Worktable created for REFORMATTING
                        FROM TABLE
                        Tab2
                        Nested iteration
                        Table Scan
                        TO TABLE
                        Worktable

                        STEP 2
                        The type of query is SELECT
                        FROM TABLE
                        Tab1
                        Nested iteration
                        Table Scan
                        FROM TABLE
                        Worktable
                        Nested iteration
                        Using Clustered Index

This step involves sorting

This SHOWPLAN statement indicates that the query must sort the
 intermediate
results before returning them to the user. Queries that specify
 DISTINCT will
require an intermediate sort, as well as queries that have an ORDER
 BY clause
which cannot use an available index. As stated earlier, the results
 are put
into a worktable, and the worktable is then sorted. The example on
 the
following page demonstrates a query which requires a sort:
        Query:          SELECT DISTINCT state
                        FROM stores

        SHOWPLAN:       STEP 1
                        The type of query is INSERT
                        The update mode is direct
                        Worktable created for DISTINCT
                        FROM TABLE
                        store