Microsoft SQL Server Performance Tuning and Optimization for Developers, Part 2: The Query Optimizer

Adam Shapiro

Objectives

Query Optimizer Overview

The query optimizer evaluates each SQL statement and determines the best plan of execution.

Understanding how the query optimizer works will help you write better queries and create useful indexes.

Purpose of the Query Optimizer

Determining the most efficient plan

The query optimizer determines the best plan for retrieving data and the order of execution for data manipulation queries, such as SELECT, INSERT, UPDATE, and DELETE. The query optimizer produces a query plan that outlines the sequence of steps required to perform the query. The optimizer can only optimize the processes of finding rows, joining rows, and ordering rows.

SQL Server uses cost-based optimization

A cost-based optimizer evaluates each query plan generated and estimates the cost of executing the plan in terms of the number of rows returned and the amount of physical disk I/O required for each relational operation.

A cost-based optimizer uses its knowledge of underlying data and storage structures such as table size, table structure, and available indexes. It also estimates the selectivity of each relational operation based on the statistics kept for each index.

By evaluating various orderings of the relational operations required to produce the results set, a cost-based optimizer arrives at an execution plan that has the lowest estimated cost in terms of system overhead.

The cost estimates can only be as accurate as the available statistical data that estimates the selectivity of each relational operation.

Performance of a query is determined by the speed of the individual tactics and by whether an efficient join order was selected. The query optimizer chooses to limit the number of choices it considers, so it can run in a reasonable amount of time.

Performance is also measured by the amount of logical and physical page access.

The query optimizer considers both logical and physical access to evaluate the cost of a query plan. It also takes into account that a fixed percentage of pages are in cache.

The query optimizer evaluates strategies to find the one with the least amount of work, which is the accumulation of CPU and I/O time. The amount of physical I/O is used to measure this. The goal is to reduce the amount of physical I/O.

Understanding how the query optimizer works should provide you with insights to help you write better queries, choose better indexes, and detect performance problems.

Where Does Query Optimization Fit in?

After a query is submitted to SQL Server, several steps occur to transform the original query into a format that the optimizer can interpret. The following briefly outlines the steps that must occur before the query can be processed and a results set returned.

Parsing process

The parsing process checks the incoming query for correct syntax and breaks down the syntax into component parts that can be understood by the relational database management system. The output of this step is a parsed query tree.

Standardization process

The standardization process transforms a query into a useful format for optimization. Any redundant syntax clauses that are detected are removed. Subqueries are flattened if possible. The output of this step is a standardized query tree.

Query optimization

The query optimizer produces an efficient query plan for processing the query. There are three steps involved in this phase: query analysis, index selection, and join selection. The query optimizer automatically limits the number of possible execution plans. The output of this step is called an execution plan or a query plan.

Compilation

The code is compiled into executable code.

Database access routines

The optimizer has determined the best method to access data, choosing to perform a table scan or use an available index. That method is now applied.

Query Optimizer Information

The query optimizer analyzes the information it has available to determine the best choice of query plan.

The sysindexes table

The optimizer can use the following information in sysindexes:

Column Name Description
indid ID of the index. Possible values are:

0 Table (nonclustered table)

1 Clustered index

>1 Nonclustered

255 Entry for tables that have text or image data

dpages For indid = 0 or indid = 1, dpages is the count of used data-only pages. For indid = 255, rows is set to 0. Otherwise dpages is the count of leaf-level index pages.
rows The data-level row count based on indid = 0 or indid = 1. This value is repeated for indid > 1. For indid = 255, rows is set to 0.
distribution Pointer to distribution page (if entry is an index).
rowpage Maximum count of rows per page.
minlen Minimum size of a row.
maxlen Maximum size of a row.
maxirow Maximum size of a nonleaf index row
keys1 Description of key columns (if entry is an index).
keys2 Description of key columns (if entry is an index).
soid Sort order ID that the index was created with; 0 if there is no character data in the keys.
csid Character set ID that the index was created with; 0 if there is no character data in the keys.

Statistical distribution of key values

This information is found on the distribution page.

The query to be executed

The query gives the optimizer the selection criteria it needs to determine which index, if any, would be most useful. The way that row qualifications are expressed in the query can affect the optimizer’s decisions.

SHOWPLAN

This SET statement option will report the optimizer’s final decision on which indexes have been selected for use with which tables, the order in which the tables will be joined, and the update mode selected. Work tables and other strategies are also reported in the SHOWPLAN output.

Phase One: Query Analysis

The first phase of the query optimizer is called query analysis. In this phase the optimizer looks at each clause that was parsed and determines whether it can be optimized. Clauses that can be optimized are those that limit a scan - for example, those containing a search argument or join clause. For those clauses that can be optimized, the optimizer determines whether there is an appropriate index.

Search Arguments

A search argument limits a search because it is very specific in the information it is requesting. It specifies an exact match, a range of values, or a conjunction of two or more items joined by an AND operator. A search argument contains a constant expression that acts on a column using an operator.

Examples

name = 'jones'
salary > 40000
60000 < salary
department = 'sales'
name = 'jones' AND salary > 100000

Non-Search Arguments

If an expression does not limit a search, it is considered a non-search argument. This includes expressions that are exclusive rather than inclusive.

For example, a not-equal (!=) expression must first look at all the data before it can determine which data does not fit the search criteria.

Another example is a comparison between columns, such as:

salary = commission

Because both columns are contained in the table itself, an index may not be very useful.

Another example is one that involves computation prior to data access. For example:

salary * 12 > 36000

In this case, the salary column must be accessed and the calculation performed before SQL Server can determine whether a row qualifies.

Class discussion

What are the search arguments in the following query?

SELECT COUNT(*) 
FROM dept, empl, job
WHERE empl.age > 30
AND (dept.floor = 2 OR dept.floor = 3)
AND job.rate > $20.00
AND empl.jobno = job.jobno

Converting Non-search Arguments

In many cases, non-search arguments can be rewritten into search arguments. A query that contains a search argument increases the chances that the optimizer will select an index.

Expressions that involve computations on a column can be converted into a search argument by isolating the column.

Some expressions are internally modified by the query optimizer into search arguments, such as BETWEENs and LIKEs.

Using non-search arguments to avoid the use of an index

If you want the query optimizer to avoid selecting a particular index, you can use a non-search argument in the search clause - for example, add a zero to the column as follows:

 salary + 0 > 30000

This statement guarantees that the optimizer will not evaluate an index on salary.

OR Clauses

OR clauses are mentioned here because they are detected as part of query analysis. They will be covered in much more detail later.

Join Clauses

Retrieving data from two or more tables requires a join clause. A join clause links the data from various tables in the same database or in different databases.

A self-join is also an example of a join clause.

Example

SELECT e1.manager_name, e2.name
FROM empl e1, empl e2
WHERE e1.emplno = e2.manager_no

Phase Two: Index Selection

Index selection is the second phase of query optimization. During this phase, the query optimizer determines whether an index exists for a clause, assesses the usefulness by determining the selectivity of the clause (how many rows will be returned), and estimates the number of page accesses (both logical and physical) required to find the qualifying rows.

Determining Whether a Useful Index Exists

The first step in determining whether a useful index exists is to check for an index that matches the clause.

An index is useful if:

Considerations

The query optimizer can evaluate using a nonclustered index if the high-order column is specified in the WHERE clause.

The query optimizer can always evaluate a covering index regardless of whether the indexed column is specified in the WHERE clause.

Determining the Selectivity of a Clause

If statistics are available

After a useful index is found that matches the clause, its usefulness is assessed by determining the selectivity of the clause. Even if a useful index is present, it may not be used if the optimizer determines that index access is not the best access method. The selectivity is determined by estimating the number of rows that satisfy the clause. If statistics are available, the server evaluates indexes using the distribution steps.

If no statistics are available

If no statistics are available, the server uses fixed percentages depending on the operator.

The optimizer uses the following defaults if no statistics are available:

Operator Assumed Percentage of Rows
= 10%
> 33%
< 33%
BETWEEN 25%

A special case is when the optimizer recognizes that there is an equality in the WHERE clause and the index is unique. Because this is an exact match and always returns one row, the optimizer doesn’t have to use statistics.

There will be no statistics available if the index was created before there was any data in the table, or if the table has been truncated.

Index Statistics

Statistics are used by the optimizer to estimate how useful an index is in limiting a search or in deciding the join order for multiple table queries. Statistics are kept on every index to provide information about the distribution of values in a given index.

In SQL Server, an even distribution of values is maintained for the index statistics. The number of rows per step remains constant while the key value ranges change. (In a standard distribution, the key value ranges remain constant while the number per range changes.) An even distribution allows the query optimizer to easily determine the selectivity of a query by estimating the number of qualifying rows as a percentage of the total rows in the table.

Distribution Page

The distribution page represents a sampling of the values contained in the index.

To determine whether a distribution page has been created for an index, query the distribution column in the sysindexes table. A zero in the distribution column indicates that no statistics are available for that index. Otherwise, the number indicates the location of the distribution page.

Executing UPDATE STATISTICS creates a distribution page for each index on a table.

The density refers to the average number of duplicates. A separate value is maintained for each left-based subset of columns in a composite index.

Distribution Steps

The size of the index key determines the total number of distribution steps for each index. A limit is imposed by the number of values that can fit on the distribution page. The first and last key values in the index are always included.

The total number of distribution steps is then divided into the total number of index keys to determine the number of keys to be included in each step. One index key at each step is recorded on the distribution page. SQL Server calculates the size of the step.

The number of index keys per page minus 1 equals the number of distribution steps. The greater the number of steps, the more accurate the information is. Indexes with smaller keys have more accurate statistics. If the number of steps equals the total number of rows, you have complete information.

For composite indexes, only the keys in the first column are used to determine the distribution steps.

Distribution Steps: Example

Calculating the number of distribution steps

Index key size = 250 bytes

8 index keys per page minus 1 = 7 distribution steps

Total number of index keys = 22

There are a total of 7 steps, with 3 keys per step.

One seventh of the table is in each step.

One index key at each step (every third row) is placed on the distribution page.

Class example

Index key size = 18 bytes

How many index keys per page?__________________________________

How many distribution steps?_____________________________________

Total number of index keys = 94,795

How many keys per step?________________________________________

How much of the table is in each step?______________________________

Looking at Distribution Steps

Syntax

DBCC SHOW_STATISTICS (table_name, index_name)

Displays all the statistical information in the distribution page for an index (index_name) on a specified table (table_name). The results returned indicate the selectivity of an index (the lower the density returned, the higher the selectivity) and provide the basis for determining whether or not an index would be useful to the optimizer.

Enterprise manager

Using SQL Enterprise Manager, on the Manage menu you can click Indexes, or drill down to the name of a table, right-click the name, and then click Indexes. In either case, you will get a dialog box that enables you to examine any index on any table in the database.

Click the Distribution button and you will see much of the same information that DBCC SHOW_STATISTICS provides.

UPDATE STATISTICS

UPDATE STATISTICS [[database.]owner.]table_name [index_name]

The table_name parameter specifies the table with which the index is associated. It is required because SQL Server does not require index names to be unique in a database.

The index_name parameter specifies the index to be updated. If an index name is not specified, the distribution statistics for all indexes in the specified table are updated. To see a list of index names and descriptions, execute the sp_helpindex system stored procedure with the table name.

Syntax

STATS_DATE    (table_id, index_id)

This function returns the date that the statistics for the specified index (index_id) were last updated.

Example

To see the date on which the statistics were updated for all indexes on a table, use the following statement:

SELECT 'Index Name' = i.name, 'Statistics Updated' = 
stats_date(i.id, i.indid) FROM sysobjects o, sysindexes i WHERE o.name = 'charge' AND o.id = i.id

Exceptions to Using Statistics

There are some cases in which statistics are not used. This happens when statistics are not available or there is an unknown value in a WHERE clause. Statistics will be unavailable if the index was created before any data was put into the table and UPDATE STATISTICS has not been run, or if the table has been truncated.

Unknown value

Example

DECLARE @var int
SELECT @var = 15
SELECT X FROM Y WHERE col = @var

Because the WHERE clause contains an unknown value, the key values in the index statistics cannot be used. However, if the operator is =, SQL Server will use the density information to estimate the number of qualifying rows.

The fixed percentages are slightly different from the default numbers used if there are no statistics.

Operator Assumed Percentag be of Rows
= Determined by density
<, >, BETWEEN 33%

Note   Just because statistics are available does not mean they are up to date.

Determining the Amount of Page Access

As a second part of determining the selectivity of a clause, the query optimizer calculates the logical page estimates based on row estimates. This determines the best index to select for a particular clause. There can be a big difference between the page estimates for a clustered index and a nonclustered index. With a nonclustered index, the query optimizer assumes the worst-case scenario: that each row will be found on a different page. This factors into the cost-based optimization calculation of the query optimizer.

For no index

Logical page accesses = total number of data pages in table.

For a clustered index

Logical page accesses = number of levels in index plus the number of data pages to scan (data pages = number of qualifying rows / rows per data page).

For a nonclustered index

Logical page accesses = number of levels in index plus the number of leaf pages (qualifying rows / rows per leaf page) plus the number of qualifying rows. (This assumes each row is on a separate page.)

For a covering index

Logical page accesses = number of levels in the index plus the number of leaf pages (qualifying rows / rows per leaf page).

For a unique index

If the query is searching for an equality on all parts of the key of a unique index, logical page accesses = 1 plus the number of index levels.

Phase Three: Join Selection

Join selection is the third major step in the query optimization phase. If there is a multiple table query or self-join, the optimizer will evaluate join selection. The optimizer compares how the clauses are ordered and selects the join plan with the lowest estimated processing costs in terms of logical page I/O.

Determining the Selectivity of a Join Clause

Join selectivity determines how many rows from table A will join with a single row from table B. This is different from determining how many rows match a search argument. Join selectivity is a useful element in determining the order in which joins will be processed.

If statistics are available, join selectivity is based on the density of the index. If statistics are not available, the heuristic is 1 divided by the number of rows in the smaller table.

Join selectivity refers to the number of rows expected from a join clause. This can be calculated or based on density (average percentage of duplicate rows).

Join clause example

WHERE dept.deptno = empl.deptno

Assumptions:
    1,000 employees
    100 departments

Intuitively one would estimate that there are 10 employees per department. Because the query optimizer lacks intuition, it must calculate the selectivity through other means.

The selectivity for the above clause is 1 / 100 or .01.

Given a row in the department table, the number of rows in the employee table that join it is: 1,000 * .01 = 10

Given a row in the employee table, the number of rows in the department table that join it is: 100 * .01 = 1

Nested Iteration of Joins

If there is a join clause in the query, the optimizer evaluates the number of tables, indexes, and joins to determine the optimal order for the nested iteration.

Strategy

Guidelines

Nested Iteration of Joins: Example

The optimizer may choose to do a nested iteration of joins. If this strategy is chosen, SQL Server constructs a set of nested loops by finding a row from the first table and then using that row to scan the next table, and so on until the result that matches is used to scan the last table. The results set is narrowed down as it progresses from table to table with each iteration.

The query plan specifies the ordered set of nested tables to use. The number of different possible plans is related to the number of tables, indexes, and joins.

Join of titles to titleauthor: Example

Example

SELECT title 
FROM titles, titleauthor
WHERE titles.title_id = titleauthor.title_id
AND titleauthor.royaltyper > 50

Processing steps

  1. Get next row of titles.

  2. Get value of title_id.

    Using index on title_id, locate each matching row in titleauthor.

    Compare value of royaltyper, and return row if it’s > 50.

  3. Repeat Steps 1 and 2 until all qualifying rows in the outer table have been accessed.

Join of titleauthor to titles: Example

Example

SELECT title 
FROM titles, titleauthor
WHERE titles.title_id = titleauthor.title_id
AND titleauthor.royaltyper > 50

Processing steps

  1. Get next row of titleauthor WHERE royaltyper > 50.

  2. Use index on title_id to search titles and return rows.

  3. Repeat Steps 1 and 2 until all qualifying rows in the outer table have been accessed.

Three-Way Join: Examples

Example

SELECT t.title, a.au_lname 
FROM titles t, authors a, titleauthor ta
WHERE t.title_id = ta.title_id
AND a.au_id = ta.au_id
AND a.au_lname = 'Green'

Example 1

titles to ta (titleauthor) to authors (shown above)

Because there are three titles, search titleauthor three times and search authors six times.

Example 2

authors to ta (titleauthor) to titles (shown above)

Because there is only one author = Green, search titleauthor one time and search titles two times.

The difference in these examples is that authors is searched six times in the first case and only one time in the second case.

Key points

Choosing the Best Plan

Calculating the Total Cost

Summary of Query Processing Phases

SHOWPLAN Output

The output from the SHOWPLAN parameter of the SET statement details the final access method that the query optimizer chooses for processing a query. Below is an explanation of the output messages.

STEP n

This statement is included in the SHOWPLAN output for every query. In some cases, SQL Server cannot effectively retrieve the results in a single step, so it breaks the query plan down into several steps.

The type of query is <query type>

This statement describes the type of query (SELECT, INSERT, UPDATE, or DELETE) used in each step. If SHOWPLAN is turned on while other commands are issued, <query type> reflects the issued command.

The update mode is deferred

This statement indicates that the update mode that was selected is deferred.

The update mode is direct

This statement indicates that the update mode that was selected is direct.

GROUP BY

This appears in the SHOWPLAN output for any query that contains a GROUP BY clause. A GROUP BY always requires at least two steps: one step to select the qualifying rows into a worktable and group them, and another step to return the results.

Scalar aggregate

This indicates that an aggregate function was used in the SELECT statement. Because a single value is returned, regardless of the number of rows involved, the first step calculates the aggregate and the second step returns the final value.

Vector aggregate

If a GROUP BY clause is used in conjunction with an aggregate function, the query optimizer uses a vector aggregate. A single value is returned for each group.

FROM TABLE

This statement indicates the name of the table that the query is accessing. The order of the tables listed after FROM TABLE indicates the order in which the tables were joined together to process the query.

TO TABLE

This indicates the target table that is being modified. In some cases, the table is a worktable rather than a physical table in the database.

Worktable

This indicates that a temporary table was created to hold the intermediate results of a query. This occurs when rows need to be sorted. Worktables are always created in the tempdb database and are automatically dropped after the results are returned.

Worktable created for <query type>

This indicates that a worktable was created to process a query. The query type could be: SELECT_INTO, DISTINCT, or ORDER BY, or the worktable could be created for the purposes of REFORMATTING.

This step involves sorting

This indicates that the intermediate results of the query must be sorted before they are returned to the user. This happens when there is no useful index for queries that either specify DISTINCT or that include an ORDER BY.

Using GETSORTED

This indicates that SQL Server has created a temporary worktable to sort the rows in the results set. Note that not all queries that return rows in sorted order use this step.

Nested iteration

Nested iteration is the default technique of the optimizer, and this phrase occurs in all SHOWPLAN output.

EXISTS TABLE: nested iteration

This statement indicates a nested iteration on a table that is used as part of an existence test. In Transact-SQL, an existence test can be written as EXISTS, IN, or =ANY.

Table scan

This indicates that the query optimizer has selected the table scan strategy to retrieve the results.

Using clustered index

This indicates that the query optimizer is using the clustered index to retrieve the results set.

Index: <index name>

This indicates the name of the nonclustered index that the query optimizer is using to retrieve the results set.

Using dynamic index

This indicates that the optimizer has chosen to build its own index as part of the OR processing strategy.

STATISTICS IO Output

The output from STATISTICS IO includes the following values:

Objectives

Analyzing the Optimizer’s Choices

In most cases the optimizer chooses the best indexes and the best join order for the queries it processes.

If you suspect that the optimizer may not have made the best choice, there are tools available to analyze why the optimizer made the choices it made. Sometimes just knowing the reasons is enough to persuade you that the right choice was made.

In other situations, you may still not be convinced. There are also tools available to override the optimizer. You can use these tools to determine if your choice really is better than the optimizer’s choice.

Statistics management tools

DBCC UPDATEUSAGE

This command reports and corrects inaccuracies in the sysindexes table that can result in incorrect space usage reports by the sp_spaceused system stored procedure.

This statement corrects the used, reserved, and dpages columns of the sysindexes table for any clustered indexes on objects of the type U (user-defined table) or S (system table). Size information is not maintained for nonclustered indexes. This statement can be used to synchronize space usage counters in sysindexes, which will result in accurate usage information being returned. When you use 0 instead of the database_name, the update is performed in the current database.

Syntax

DBCC UPDATEUSAGE ({0 | database_name} [, table_name [, index_id]])
[WITH COUNT_ROWS]

The WITH COUNT_ROWS option specifies that the rows column of sysindexes is updated with the current count of the number of rows in the table. This only applies to sysindexes rows that have an index_id of 0 or 1. This option can affect performance on large tables.

Note   The stored procedure sp_spaceused can be used with the @updateusage qualifier to provide the same functionality as DBCC UPDATEUSAGE. The sp_spaceused stored procedure takes longer to execute. Using this option on large tables may take longer to complete because every row in the table is counted.

DBCC SHOW_STATISTICS

This command displays all the statistical information in the distribution page for an index on a specified table. The results returned indicate the selectivity of an index (the lower the density returned, the higher the selectivity) and provide the basis for determining whether or not an index would be useful to the optimizer. The results returned are based on distribution steps of the index.

Syntax

DBCC SHOW_STATISTICS (table_name, index_name)

STATS_DATE function

This function returns the date that the statistics for the specified index were last updated.

Syntax

STATS_DATE (table_id, index_id)

All of the above information is available in Enterprise Manager, using the Manage Indexes dialog box. (On the Manage menu, click Indexes.)

Trace flags

Trace flags

SQL Server trace flags provide additional information about SQL Server operations or change certain behaviors, usually for backward compatibility. In general, trace flags should only be used as a temporary work-around for a problem until a permanent solution is put in place. Although the information provided by trace flags can help diagnose problems, keep in mind that trace flags are not part of the supported feature set. This means that future compatibility or continued use is not assured. In addition, your primary support provider, including Microsoft, will usually not have further information and will not answer questions regarding the trace flags or their output. In other words, the information provided in this section is to be used at your own risk.

Optimizer trace flags

Trace flag Information
302 Gives information about whether the statistics page is used, the actual selectivity (if available), and what SQL Server estimated the physical and logical I/O would be for the indexes. Trace flag 302 should be used with trace flag 310 to show the actual join ordering.
310 Gives information about join order. Index selection information is also available in a more readable format using SET SHOWPLAN ON, as described in the SET statement.
325 Gives information about the cost of using a nonclustered index or a sort to process an ORDER BY clause.
326 Gives information about the estimated and actual cost of sorts.
330 Enables full output when using the SET SHOWPLAN option, which gives detailed information about joins.
3604 Sends trace output to the client. This trace flag is used only when setting trace flags with DBCC TRACEON and DBCC TRACEOFF.
3605 Sends trace output to the error log. (If SQL Server is started from a command prompt, the output will also appear on the screen.)

Alternate startup options

When SQL Server is installed, the setup program writes a set of default startup options in the Windows NT Registry under the key:

HKEY_LOCAL_MACHINE
	\SOFTWARE
		\Microsoft
			\MSSQLServer
				\MSSQLServer

If you want to create and store alternate sets of startup options in the Registry-for example, to start SQL Server in single-user mode or with a specific set of trace flags-copy the MSSQLServer key (under MSSQLServer) to a new key, and then edit the options in the new key to suit your needs. Each startup option, including each trace flag, is stored as a separate parameter in the Parameters entry of the MSSQLServer key, starting with SQLArg0, then SQLArg1, and so on. The order of the parameters is not important.

Editing of the Registry is not generally recommended, and inappropriate or incorrect changes can cause serious configuration problems for your system.

You could create a new key called SingleUser, and then edit this entry

HKEY_LOCAL_MACHINE
	\SOFTWARE
		\Microsoft
			\MSSQLServer
				\SingleUser
					\Parameters

to include the additional -m startup option. The entire Parameters entry for the SingleUser key would look like this:

HKEY_LOCAL_MACHINE
	\Software
		\Microsoft
			\MSSQLServer
				\SingleUser
					\Parameters
SQLArg0 : REG_SZ : -dC:\SQL\DAT\MASTER.DAT
SQLArg1 : REG_SZ : -eC:\SQL\LOG\ERRORLOG
SQLArg2 : REG_SZ : -m

To start SQL Server using this alternate key, you would start SQL Server from the command prompt using the –s startup option, as shown in the following example:

  sqlservr -c -sSingleUser

Using optimizer trace flags

The optimizer trace flags provide a lot of information, most of it intended for Microsoft engineers. There are a few specific things you can look for:

Example

Here is some Transact-SQL code and its output. The output that answers the questions above is in bold type.

DBCC TRACEON(3604, 302)
SET SHOWPLAN ON
SET NOEXEC ON
GO
SELECT * FROM charge
WHERE charge_no > 99950
DBCC execution completed. If DBCC printed error messages, see your System Administrator.
 
*******************************
Leaving q_init_sclause() for table 'charge' (varno 0).

The table has 100000 rows and 3408 pages.

Cheapest index is index 0, costing 3408 pages per scan.
 
*******************************
Entering q_score_index() for table 'charge' (varno 0).
The table has 100000 rows and 3408 pages.
Scoring the search clause:
AND (!:0xb8e492)  (andstat:0xa)
  GT (L:0xb8e47e)  (rsltype:0x38 rsllen:4 rslprec:10 rslscale:0
  opstat:0x0)
    VAR (L:0xb8e4d0)  (varname:charge_no varno:0 colid:1
    coltype(0x38):INT4 colen:4 coloff:2 colprec:10 colscale:0
    vartypeid:101 varusecnt:2 varstat:0x4001 varlevel:0 varsubq:0)
    INT4 (R:0xb8e464)  (left:0xb8e46c len:4 maxlen:4 prec:5 scale:0
    value:99950)
 
Scoring clause for index 6
Relop bits are: 0x4000,0x80,0x10,0x1

Qualifying stat page; pgno: 10616 steps: 332 
Search value: INT4 value:99950
No steps for search value--qualpage for LT search value finds 
value between steps 330 and 331--use betweenSC 

Estimate: indid 6, selectivity 4.513098e-003, rows 451 pages 457 
Cheapest index is index 6, costing 457 pages and generating 451 rows
per scan.
Search argument selectivity is 0.004513.
*******************************
STEP 1
The type of query is SELECT
FROM TABLE
charge 
Nested iteration
Index : charge_charge_amt

Overriding the Optimizer

FORCEPLAN

FORCEPLAN is an option of the SET statement, and it can be either ON or OFF. Once FORCEPLAN is turned ON, it stays in effect for the session, or until set to OFF.

When FORCEPLAN is ON, the order in which tables are listed in the FROM clause controls the order in which the tables will actually be joined; the optimizer will bypass making any decisions about join order.

Using FORCEPLAN

Example

In this example, the query will be processed by accessing the corporation table first, and then the member table, no matter what the optimizer might have chosen as the best order.

SET FORCEPLAN ON
GO
SELECT *
FROM corporation, member
WHERE member.corp_no = corporation.corp_no
AND member_no < 100
GO

Forcing an index

Optimizer indexing hints

SQL Server provides a number of hints that can be supplied to the optimizer within a SELECT statement. Most of these will be discussed in a later module, because they apply to locking behavior. One hint that deals with indexing is the INDEX hint. You must supply an index ID or an index name after the name of the table in the SELECT statement:

Partial syntax

SELECT select_list
FROM table_name [(INDEX = {index_name | index_id})]

The hint specifies the index name or ID to use for the table. An index_id of 0 forces a table scan, and 1 forces the use of a clustered index (if one exists).

Example

In this example, the query will be processed by using the index on corp_no, no matter what the optimizer might have chosen as the best index.

SELECT *
FROM member (INDEX = member_corporation_link)
WHERE member_no < 100
AND corp_no BETWEEN 200 AND 300

FASTFIRSTROW

This option causes the optimizer to use a nonclustered index if it matches the ORDER BY clause and there is no WHERE clause. The first row will be returned more quickly and a work table for sorting will not be built in tempdb; read-ahead will not be used, and the total amount of I/O and the time required to complete the query may be greater. If the query contains a WHERE clause as well as an ORDER BY clause, then SQL Server may use an index that resolves the WHERE clause instead of the index that resolves the ORDER BY clause. The decision will be based on the selectivity of the WHERE clause, but it will be influenced by the presence of FASTFIRSTROW.

Additional Considerations

When it improves performance

Be sure to verify that performance has been improved. Turn on STATISTICS IO and STATISTICS TIME to determine that overriding the optimizer has had a positive impact. Usually the optimizer really does know best, and overriding the optimizer does not make performance better.

As a last resort

Try other methods of getting the optimizer to behave as you would like it to behave. Have you updated statistics recently? Have stored procedures been recompiled lately? Can you rewrite your query or search arguments? Can you build slightly different indexes?

Document your reasons for hinting

Make sure to leave a record of why you needed to override the optimizer. If those reasons change long after you originally wrote the code, you may not realize that your hints are no longer necessary.

Retest after every upgrade

The SQL Server optimizer is continually being improved. After installing a new version, it may no longer be necessary to override the optimizer. Your suggestions may actually be worse than the optimizer’s own choices.

The SQL Server optimizer is dynamic and can find a new best plan as your data changes. If you have to force the optimizer, that decision becomes non-dynamic. As your data changes, your plan will stay the same. For this reason, you should consider retesting on a regular basis any query for which you chose to override the optimizer, even if the version of SQL Server has not been upgraded.