This section explains how to use and interpret the
showplan command to better understand and utilize the SQL Server query optimizer.
When you send a SQL statement to the Sybase SQL Server, the request first goes to a cost-based query optimizer whose job it is to find the most efficient data access path to fulfill the request. To do this, the optimizer examines such information as:
- The structure of any indices defined on the table(s) involved
- The distribution of data within the indices
- The number of rows in the table(s) involved
- The number of data pages used by the table(s) involved
- The amount of data cache SQL Server is currently using
- The access path that would require the least amount of I/O and, therefore, would be the fastest
Once an optimal access path is calculated, it is stored in a query plan within the procedure cache.
The
showplan allows you to view the plan the optimizer has chosen and to follow each step that the optimizer took when joining tables, reading from an index or using one of several other methods to determine cost efficiency. To invoke the showplan command, enter:
1> set showplan on
2> go
This command causes SQL Server to display query plan information for every SQL statement executed within the scope of the SQL Server session.
Since the determination of a query plan is performed independently from the actual data retrieval or modification, it is possible to examine and tune query plans without actually executing the SQL statement. This can be accomplished by instructing SQL Server not to execute any SQL statements via the following command:
1> set noexec on
2> go
Note
Issue
noexec after
showplan or the
set showplan command will not execute.
For more information about executing the
showplan command, refer to the SQL Server Performance and Tuning Guide.
Note
The
showplan command does not function within stored procedures or triggers. However, if you set it to on and then execute a stored procedure or a command that fires a trigger, you can see the procedure or trigger output.
Use the following examples to analyze a query plan. In all cases, examples use the
pubs database provided with each SQL Server release.
Interpreting
showplan Output
The output of the
showplan command consists of many different types of statements, depending on the details of the access path that is being used. The following sections describe some of the more common statements.
STEP
n
This statement is added to the
showplan output for every query, where
n is an integer, beginning with 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 needs to be broken into at least two steps: one to select the qualifying rows from the table and another to group them.
The following query demonstrates a single-step query and its
showplan output:
1> select au_lname, au_fname from authors
2> where city = “Oakland”
3> go
STEP 1
The type of query is SELECT
FROM TABLE
authors
Nested iteration
Table Scan
A multiple-step example is shown in the next section.
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, select the values from that table. This is most often seen with a query which involves a
group by clause, as the results are first put into a worktable, and then the qualifying rows in the worktable are grouped based on the given column in the
group by clause.
The following query returns a list of cities and indicates the number of authors who 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.
1> select city, total_authors = count (*)
2> from authors group by city
3> go
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 is
select,
insert,
update, or
delete. If
showplan is turned on while other commands are issued, the
query_ type reflects the command that was issued. The following two examples show output for different queries or commands:
1> create table Mytab (col1 int)
2> go
STEP 1
The type of query is CREATE TABLE
1> insert publishers
2> values (“9904”, “NewPubs”, “Nome”, “AL”)
3> go
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 that will be inserted or deleted (in the case of
insert and
delete).
When all log records have been constructed, the changes are applied to the data pages. This method generates more log records than a direct update, but it has the advantage of allowing commands to execute which may cascade changes throughout a table. For example, consider a table that has a column
col1 with a unique index on it and data values numbered consecutively from 1 to 100 in that column. Execute an
update statement to increase the value in each row by one:
1> update Mytable set col1 = col1 + 1
2> go
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 until the end of the table. This violates the unique index. First, updating the first row (which has an initial value of 1) to 2 would cause an error, since 2 already exists in the table. Second, by updating the second row or any row in the table except the last one does the same.
Deferred updates avoid unique index violations. The log records are created to show the new values for each row, the existing rows are deleted and new values are inserted. In the following example, the table
authors has no clustered index or unique index:
1> insert authors select * from authors
2> go
STEP 1
The type of query is INSERT
The update mode is deferred
FROM TABLE
authors
Nested iteration
Table Scan
TO TABLE
authors
Because the table does not have a clustered index, new rows are added at the end of the table. The query processor distinguishes between existing rows now in the table (before the
insert command) from the rows to be inserted, thus avoiding the continuous loop of selecting a row, inserting it at the end of the table, re-selecting the row just inserted and reinserting it. The deferred insertion method first creates the log records to show all currently existing values in the table. Then SQL Server rereads those log records to insert the rows into the table.
The Update Mode Is Direct
Whenever possible, SQL Server tries to directly apply updates to tables, since this is faster and creates fewer log records 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. The criteria are as follows:
- insert Using the direct method, 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, the query optimizer must be able to determine that either zero or one row qualifies for the delete. The only way to verify this is to check that one unique index exists 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 commands, the same criteria apply as for delete: a unique index must exist so that the query optimizer can determine that no more than one row qualifies for the update, and the only table in the update command is the target table to update. Also, all updated columns must be fixed-length datatypes, not variable- length datatypes. Note that any column that allows null values is internally stored by SQL Server as a variable-length datatype column.
1> delete from authors
2> where au_id = “172-32-1176”
3> go
STEP 1
The type of query is DELETE
The update mode is direct
FROM TABLE
authors
Nested iteration
Using Clustered Index
TO TABLE
authors
1> update titles set type = ‘popular_comp’
2> where title_id = “BU2075”
3> go
STEP 1
The type of query is UPDATE
The update mode is direct
FROM TABLE
titles
Nested iteration
Using Clustered Index
TO TABLE
titles
1> update titles set price = $5.99
2> where title_id = “BU2075”
3> go
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 updated. In the second query, the direct update method is used, whereas in
the third query, the deferred method is used. This difference occurs because of the datatype of the column being updated: the
titles.type column is defined as “char(12) NOT NULL” where 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 are always two-step queries: the first step selects the qualifying rows into a table and groups them; the second step returns the rows from the table as seen in the following example:
1> select type, avg (advance),
sum(ytd_sales)
2> from titles group by type
3> go
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,
max,
min, and
sum. Whenever you use an aggregate function in a
select statement that does not include a
group by clause, the result is a single value, regardless of whether it operates on all table rows or on a subset of the rows defined in the
where clause. When an aggregate function produces a single value, the function is called a
scalar aggregate and
showplan lists it that way as seen in the
following example:
1> select avg(advance), sum(ytd_sales) from
titles
2> where type = “business”
3> go
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
showplan considers this a two-step query, which is similar to the
group by output. Since the query contains a scalar aggregate which will return a single value, SQL Server keeps a “variable” internally 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 are evaluated from the table in step 1, the final value of the variable is selected in step 2 to return the scalar aggregate result.
Vector Aggregates
When a
group by clause is used in a query that 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. The following example query includes a vector aggregate:
1> select title_id, avg (qty) from sales
2> group by title_id
3> go
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 Statement
This
showplan output shows the table from which the query reads. In most queries, the
from table is followed by the table’s name. In other cases, it may show that it is selecting from a worktable. The significant fact is that the
from table output show the query optimizer’s order for joining tables. The order in which the tables are listed is the order in which the tables are joined. This order often differs from the order in which tables are listed in the query’s
from or
where clauses. The reason for this is that the query
optimizer checks many join orders for the tables and picks the order that uses the fewest I/Os.
1> select authors.au_id, au_fname, au_lname
2> from authors, titleauthor, titles
3> where authors.au_id = titlesauthor.au_id
4> and titleauthor.title_id =
titles.title_id
5> and titles.type = “psychology”
6> go
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 join order that the query optimizer chose for the tables, which is not the order listed in either the
from or
where clauses. By examining the order of the
from table statements, it can be seen that the qualifying rows from the titles table are first located with 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. 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 Statement
When you issue a command that tries to modify one or more table rows, such as
insert,
delete,
update,
or select into, the
to table statement shows the target table that is being modified. If the operation requires an intermediate step and inserts the rows into a worktable, the
to table statement names the worktable instead of the user table.
1> insert sales
2> values (“8042”, “QA973”, “7/15/94”, 7,
3> “Net 30”, “PC1035”)
4> go
STEP 1
The type of query is INSERT
The update mode is direct
TO TABLE
sales
1> update publishers
2> set city = “Los Angeles”
3> where pub_id = “1389”
4> go
STEP 1
The type of query is UPDATE
The update mode is deferred
FROM TABLE
publishers
Nested iteration
Using Clustered Index
TO TABLE
publishers
Note that the
showplan for the second query indicates that the
publishers table is used for both
from table and
to table. With
update operations, the query optimizer must first read the table containing the row(s) to be updated, resulting in the
from table statement, and then must modify the row(s), resulting in the
to table statement.
Worktable
For some queries, such as those that require ordered or grouped output, the query optimizer creates its own temporary table called a worktable. The worktable holds all the intermediate results of the query where they are ordered and/or grouped, and then the final select is done. When all results are returned, the table is dropped automatically. The
tempdb database holds all temporary tables so the System Administrator may need to increase the size of that database to accommodate very large worktables. For more information about worktables, refer to Chapter 8, “The tempdb Database.”
Since the query optimizer creates these worktables for its own internal use, the worktable names are not listed in the
tempdb..sysobjects table.
Nested Iteration
The nested iteration is the default technique used to join table and return rows from a table. It indicates that the query optimizer uses one or more sets of loops to read a table and fetch a row, qualify the row based on the search criteria given in the
where clause, return the row to the front end, and then loop again for the next row. The following example shows the query optimizer doing nested iterations through each of the tables in the join:
1> select title_id, tile
2> from titles, publishers
3> where titles.pub_id = publishers.pub_id
4> and publishers.pub_id = ‘1389’
5> go
STEP 1
The type of query is SELECT
FROM TABLE
publishers
Nested iteration
Using clustered index
FROM TABLE
titles
Nested iteration
Table Scan
Table Scan
This
showplan statement identifies the method used to fetch the physical result rows from the given table. When the
table scan method is used, execution begins with the first row on the table. Then each row is fetched and compared with the conditions set in the
where clause, then returned as valid data if the conditions are met. No matter how many rows qualify, every row in the table must be checked, and this causes problems if the table is large (the scan has a high I/O overhead). If a table has one or more indexes on it, the query optimizer may still choose a table scan
instead of reading the index. The following query shows a typical table scan:
1> select au_lname, au_fname
2> from authors
3> go
STEP 1
The type of query is SELECT
FROM TABLE
authors
Nested iteration
Table Scan
