Sybase® SQL Server Performance and Tuning Guide

Chapter 9: Advanced Optimizing Techniques

Specifying Table Order in Joins

SQL Server optimizes join orders in order to minimize I/O. In most cases, the order that the optimizer chooses does not match the order of the from clauses in your select command. To force SQL Server to access tables in the order they are listed, use the command:

set forceplan [on|off]

The optimizer still chooses the best access method for each table. If you use forceplan, specifying a join order, the optimizer may use different indexes on tables than it would with a different table order, or it may not be able to use existing indexes.

You might use this command as a debugging aid if other query analysis tools lead you to suspect that the optimizer is not choosing the best join order. Always verify that the order you are forcing reduces I/O and logical reads by using set statistics io on and comparing I/O with and without forceplan.

If you use forceplan, your routine performance maintenance checks should include verifying that the queries and procedures that use it still require the option to improve performance.

You can include forceplan in the text of stored procedures.

forceplan example

This example is executed with these indexes on the tables in pubtune:

Unique nonclustered on titles(title)

Unique clustered on authors(au_id)

Unique nonclustered on titleauthor(au_id, title_id)

Without forceplan, this query:

select title, au_lname
from titles t, authors a, titleauthor ta
where t.title_id = ta.title_id
and a.au_id = ta.au_id
and title like "Computer%"

joins the tables with the join order titles-titleauthor-authors, the join order that the optimizer has chosen as the least costly.

Here is the showplan output for the unforced query:

QUERY PLAN FOR STATEMENT 1 (at line 1).


    STEP 1
        The type of query is SELECT.

        FROM TABLE
            titles
        Nested iteration.
        Index : title_ix
        Ascending scan.
        Positioning by key.
        Keys are:
            title
        Using I/O Size 2 Kbytes.
        With LRU Buffer Replacement Strategy.

        FROM TABLE
            titleauthor
        Nested iteration.
        Index : ta_au_tit_ix
        Ascending scan.
        Positioning at index start.
        Index contains all needed columns. Base table will not be read.
        Using I/O Size 2 Kbytes.
        With LRU Buffer Replacement Strategy.

        FROM TABLE
            authors
        Nested iteration.
        Using Clustered Index.
        Index : au_id_ix
        Ascending scan.
        Positioning by key.
        Keys are:
            au_id
        Using I/O Size 2 Kbytes.
        With LRU Buffer Replacement Strategy.

statistics io for the query shows a total of 154 physical reads and 2431 logical reads:

Table: titles  scan count 1,  logical reads: 29,  physical reads: 27
Table: authors  scan count 34,  logical reads: 102,  physical reads: 35
Table: titleauthor  scan count 25,  logical reads: 2300,  physical reads: 92

Total writes for this command: 0

If you use forceplan, the optimizer chooses a reformatting strategy on titleauthor, resulting in this showplan report:

QUERY PLAN FOR STATEMENT 1(at line 1).


    STEP 1
        The type of query is INSERT.
        The update mode is direct.
        Worktable1 created for REFORMATTING.

        FROM TABLE
            titleauthor
        Nested iteration.
        Index : ta_au_tit_ix
        Ascending scan.
        Positioning at index start.
        Index contains all needed columns. Base table will not be read.
        Using I/O Size 2 Kbytes.
        With LRU Buffer Replacement Strategy.
        TO TABLE
            Worktable1.

    STEP 2
        The type of query is SELECT.

        FROM TABLE
            titles
        Nested iteration.
        Index : title_ix
        Ascending scan.
        Positioning by key.
        Keys are:
            title
        Using I/O Size 2 Kbytes.
        With LRU Buffer Replacement Strategy.

        FROM TABLE
            authors
        Nested iteration.
        Table Scan.
        Ascending scan.
        Positioning at start of table.
        Using I/O Size 2 Kbytes.
        With LRU Buffer Replacement Strategy.

        FROM TABLE
            Worktable1.
        Nested iteration.
        Using Clustered Index.
        Ascending scan.
        Positioning by key.
        Using I/O Size 2 Kbytes.
        With LRU Buffer Replacement Strateg

Table: titles  scan count 1,  logical reads: 29,  physical reads: 27
Table: authors  scan count 25,  logical reads: 5525,  physical reads: 221
Table: titleauthor  scan count 1,  logical reads: 92,  physical reads: 60
Table: Worktable1   scan count 125000,  logical reads: 389350,  physical reads: 27
Total writes for this command: 187

Figure 9-1 shows the sequence of the joins and the number of scans required for each query plan.

Risks of Using forceplan

Forcing join order has these risks:

Misuse can lead to extremely expensive queries.

It requires maintenance. You must regularly check queries and stored procedures that include forceplan. Also, future releases of SQL Server may eliminate the problems which led you to incorporate index forcing, so all queries using forced query plans need to be checked when new releases are installed.

Things to Try Before Using forceplan

As the preceding example shows, specifying the join order can be risky. Here are options to try before using forceplan:

Check showplan output to determine whether index keys are used as expected.

Use dbcc traceon(302) to look for other optimization problems.

Be sure that update statistics been run on the index recently.

If the query joins more than four tables, use set table count to see if it results in an improved join order. See "Increasing the Number of Tables Considered by the Optimizer".