Analyzing and Resolving Optimizer
Problems/Symptoms

Last revised: January 1997
Send comments to eminer@sybase.com.

What's New in This Version? Optimizer Problem Checklist
Sample Script for Obtaining Output: Batch Query Via isql
Sample Script for Obtaining Output: Stored Procedure Via isql
Introduction
Terms Used in this Document
Steps to Take Before Gathering Output for Analysis
Run update statistics
update statistics Syntax
Example of the Effects of Running update statistics
Break the Query Down to Its Simplest Components
Some Basic Questions to Start With
Collect Output and Perform Analysis
The Text of the Query, Stored Procedure(s), View Definition(s) or Trigger(s)
sp_help for All Tables Involved
Look for Qualifying Indexes
Look for Mismatched Datatypes
showplan
Reading showplan Output
statistics io
Reading set statistics io Output
set rowcount and statistics io Output
statistics time
Reading statistics time Output
When Working with Stored Procedures
noexec, set fmtonly on and Stored Procedure Analysis
When Working with Views
Using forceindex and/or forceplan in Problem Analysis
What are forceindex (Specifying an Index) and forceplan?
Pre-System 11 Syntax: forceindex
System 11 Syntax: Specifying an Index
Specifying/Forcing an Index as an Analysis Tool
Using forceplan
The Effect of noexec on Output Necessary for Analysis
dbcc traceon Commands
Using dbcc traceon 302 in Analysis
Understanding Selectivity
Selectivity and SARGs
Selectivity and Joins
What are "MagicSC" and "DensitySC"?
Situations that Will Cause the MagicSC or DensitySC to be Used
Effect on Estimated Selectivity of Two Clauses Being Scored at the Same Time
Using traceon 310 and 317 to Analyze Join Performance and Processing
What are dbcc traceon 310 and 317?
Permutations and Join Plans
Determining the Join Order Used
What You Will See ­ dbcc traceon 310 Output
Understanding dbcc traceon 310/317 Output
Items in the Body of traceon 310/317 Output
Joins and Forcing a Join Order, an Index, or a Join Plan
Forcing an Index
Using traceon 317 in the Analysis of Join Problems
What is a Work Plan?
Differences Between traceon 310 and 317 Output in Pre-System 11 and System 11 SQL Servers
What To Do If an Inefficient Join Plan is Being Chosen Over an Efficient One

The Sample Final Plan
Determine the Join Plan of the Efficiently Optimized Join
Find the Join Order of the Efficient Join Plan
Find the Table Access Method Used in the Efficient Plan
What is the Pathtype Being Used?
What is the Method Used in the Costing of the Join?
A Quick Practice Test Investigation
Reading Through the Work Plans for the Efficient Join Plan
Compare the Final Plan Costs for Both the Efficient and Inefficient Join
Other Information You Might See in traceon 310/317 Output
The Effects of Sorting and Reformatting on the Cost of Plans
New and Miscellaneous Optimizer Items
A Change to Non-clustered Index Reads in SQL Server 11.x
What The Change Did
Data Page Clustering
Examples
Common Optimizer Problems and Causes
Optimizer is Choosing the "Wrong" Index
Lead Index Columns with a High Number of Duplicates
Creating, Populating, Indexing, and Querying a Table in the Same Batch
Use of Variables, Functions, and Arithmetic Expressions in a Batch Query
When the Same Query on Two "Identical" Databases and/or Servers are Performing Differently
Other Resources
The Performance and Tuning Guide
AnswerBase



What's New in This Version?

This version of Analyzing, Handling, and Resolving Optimizer Problems/Symptoms includes several new modules:
• "A Change to Non-clustered Index Reads in SQL Server 11.x" on page 90. This module, in a new section entitled "New and Miscellaneous Optimizer Items", describes new non-clustered index read behavior in SQL Server 11.x.

• "The Use of IS NOT NULL and Not Equals (!=) Statements" on page 50. This module, in the section entitled "Situations that Will Cause the MagicSC or DensitySC to be Used", discusses the effect on optimization of using a != or IS NOT NULL statement.

• "About SORTAVERT" on page 38. This module, in the section entitled "Using dbcc traceon 302 in Analysis", discusses SORTAVERT and 302 output.

Optimizer Problem Checklist

The following is a checklist of information to gather when analyzing an optimizer problem. This writeup includes short descriptions of the output and references to other sources of information.

---

Note
Detailed information about the items in this checklist is provided after the checklist.

---

Assure that update statistics has been run before acquiring output for a performance/optimization problem. If you cannot confirm that update statistics has been run prior to acquiring the output, all output and subsequent analysis is suspect.

Following is the update statistics syntax:

update statistics table_name [index_name]

If you provide the index name, update statistics will be run only on that index.

Then get the following information:

• The text of the query, stored procedure(s), view definition(s) or trigger(s) involved in the problem
• sp_help for all tables involved (this includes all tables of a join, all base tables of a view, and all tables affected by a trigger)
• The output of set showplan on
• The output of set statistics io on
• The output of set statistics time on
• The output of dbcc traceon 302 and 310 310 is only necessary when a join is involved (this includes subqueries). In addition, 317 is useful when a force results in better join performance.

• select @@version ­ put this in each script you run

Sample Script for Obtaining Output: Batch Query Via isql

	select @@version
go

	use database database_name
go

	sp_help table_name
go

---

Note
You need one sp_help for each table involved.

---

	set showplan on
go

	set statistics io on
go

	set statistics time on
go

	dbcc traceon(3604,302,310,317)
go

	run the query here
go

---

Note
310 is only necessary when a join is involved (this includes subqueries), 317 is only necessary when a force results in better join performance. If noexec is to be used, add the set command below, just prior to running the query and after all other output options have been set on. Note that if set noexec is used, no output from set statistics io or time will be returned.

---

	set noexec on
go

Sample Script for Obtaining Output: Stored Procedure Via isql

The process for obtaining output from a stored procedure is very similar to the process for batch queries. Gather all information as listed above for batch queries.

Make sure the procedure is recompiled. This can be done by any one of the following options:

• Execute the procedure with recompile ­ exec proc_name with recompile.
• Run sp_recompile on at least one table the procedure references.
• Drop and re-create the procedure.
• Re-create the procedure using the with recompile option (refer to "create procedure" in the SQL Server Reference Manual for details).
If you do not want the procedure to fully execute, but still want to obtain the necessary output, run the following set command just prior to running the query:

	set fmtonly on

---

Note
Do not use noexec with procedures ­ compilation and execution will not occur and you will not get the necessary output.

---

Introduction

The purpose of this writeup is to supply you with the tools you will need to use and information about how to interpret the resulting output. It is recommended that you familiarize yourself with the material in the Performance and Tuning Guide.

Problems involving the optimizer can be among the most difficult to resolve. In many cases, there is no cut and dry answer to the problem/symptom you are seeing. At other times, the situation may involve the need to redesign the query and/or index strategy. It can be very frustrating when daily queries are running fine, but a quarterly report brings performance down.

Analyzing and resolving optimizer situations require that you determine:

• Where the problem is
• If it is a problem with SQL Server
• The strategies employed
• The query
• Or a combination of all of these
This will call for a basic knowledge of how optimization, query processing, and general performance is handled by SQL Server. You will need to perform some level of analysis; sometimes this may only require a quick glance through a short amount of output whereas at other times it may involve an in-depth analysis of a large amount of material.

Often the cause of the situation that is being encountered could best be called a "weakness" in the optimizer. Since the optimizer is cost based, that is it makes decisions based on the cost of access the required data, hardcoded algorithms and formulas are used. There are situations in which the algorithms and formulas that make up the optimizer may perform as well in an isolated situation as they do in the majority of situations.

The area of performance and tuning is largely based on compromise. For example, when the index strategy of a database is designed, the designer has to balance the need for fast access time via indexes with the time it takes to maintain indexes as the data changes. In the case of the optimizer, an example of design compromise is the "four tables at a time" method of join processing (for details see the section "Optimizing Joins" in the Performance and Tuning Guide). If the optimizer processed more than four tables at a time, the total elapsed could adversely affect otherwise good performance.

---

Note
Examples in the document were run on SQL Server release 11.0.1. Where differences in output exist between SQL Server 11.x and previous releases, notes are included.

---

Terms Used in this Document

query ­ a Transact-SQL query sent to SQL Server via isql or a script, a query within a stored procedure or trigger, or a view definition. The SQL that has been identified as a problem and is undergoing analysis.

batch query ­ SQL that is passed directly into SQL Server either via a script or isql, not via a compiled object.

SARG (search argument) ­ the query's qualifying clause(s). Following is the structure of a SARG and an example:

<column><relop><constant expression>

where stor_id="8042" 

join clause ­ a clause that joins two tables in the query; each side of the clause must reference a different table in the from list. Following is the structure of a join clause and an example:

<column><relop><column>

and titles.title_id=salesdetail.title_id

---

Note
relop is a representation of any valid relational operator, such as =, >, <=, etc.

---

join plan ­ the combination of the order of the tables in a join and the method of data access chosen by the optimizer.

varno ­ the number assigned to a table by SQL Server. The varno is based on the position of a given table in the from clause of a query. varno=0 represents the first table in the from clause, varno=1 the second table, and so on.

costing ­ the action the optimizer is taking in order to determine the cost, in I/Os, of accessing the required data in a given manner.

Steps to Take Before Gathering Output for Analysis

Run update statistics

update statistics ensures that the distribution page contains current information about the distribution of values in all indexes on the table(s).

Run the update statistics command on each table involved. Always ensure that update statistics has been run before beginning your analysis or calling Sybase Technical Support. All subsequent notes assume that update statistics has been run on all tables involved.

When an index is created on a table containing data, a new distribution page is created and statistical information is written to it. In a situation where an index has been created and no data has been added to the table, there is no need to run update statistics.

---

Note
There may be times when running update statistics is a performance burden. However, the procedure is necessary to guarantee the accuracy of your analysis, or of the optimizer's performance. If update statistics cannot be run, or if you suspect it has not been run, look for the use of the outsideSC in dbcc traceon 302 output. See "Using dbcc traceon 302 in Analysis" on page 32 for details.

---

update statistics Syntax

 update statistics table_name [index_name]  

If you specify the index name, update statistics will be run only on that index.

Example of the Effects of Running update statistics

In the example below, the table originally had 10,000 rows when the clustered index was built. Then 300 extras rows were added, and the query was run. The first selectivity estimate from traceon 302 is from that run.

Since the query looks for all rows that are greater than the last value in the statistics (steps) of the distribution page, the optimizer uses the outsideSC. The effect of this is to estimate a very low selectivity, 0.000000, for the index with the optimizer expecting to read one row by doing two page reads (one index one data page). In fact, there are slightly over 300 rows greater than 1001 in the table but the new statistical information has not yet been written to the distribution page.

---

Note
For more information on selectivity, go to "Understanding Selectivity" on page 40 of this document.

---

After running update statistics, the information on the distribution page is current with the data in the index column(s). Now the estimated selectivity is 0.031486 and the optimizer expects to read 324 rows from 4 pages; this is very close to the actual number of rows added to the table.

In the following example, the query was run before update statistics was run on the table (some of the traceon 302 output was removed):

	
1> select * from test
2> where id > 10001
3> go

No steps for search value--qualpage for LT search value finds 
value > last step--use outsideSC 
Estimate: indid 1, selectivity 0.000000, rows 1 pages 2 index height 1 

Cheapest index is index 1, costing 2 pages and 
            generating 1 rows per scan,
      using no data prefetch (size 2)
      on dcacheid 0 with LRU replacement
Search argument selectivity is 0.000000.

In the following example, the query was run after update statistics was run on the table (some of the traceon 302 output was removed):

*******************************
No steps for search value--qualpage for LT search value finds 
value between step K, K + 1, K = 322--use betweenSC 
Estimate: indid 1, selectivity 0.031486, rows 324 pages 4 index height 1 

Cheapest index is index 1, costing 4 pages and 
      generating 324 rows per scan, using no data prefetch (size 2)
      on dcacheid 0 with LRU replacement
Search argument selectivity is 0.031486.

*******************************

Break the Query Down to Its Simplest Components

In many cases where a large query or procedure is involved, only a small area causes the performance and/or optimization problem. The sheer amount of output can be overwhelming in such cases.

It is not always easy to determine where the problem lays. Sometimes you can focus on a part of the query that you have identified as a problem: "It slows down when it gets to the update." In most cases, you need to perform a full analysis to locate the problem area.

You can use print statements, particularly in stored procedures, to determine which part of the SQL produced the output you are looking at.

In addition, the -e option of isql will echo the SQL that is being processed. This is useful when analyzing a large batch script:

isql -Usa -P -e -ifile_name

The results in isql will look like those below (the go is not echoed back):

	
1> select *
2> from VBT1
3> where id between 1 and 4
4> go

QUERY PLAN FOR STATEMENT 1 (at line 1).

     STEP 1
        The type of query is SELECT.

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

Parse and Compile Time 1.
SQL Server cpu time: 100 ms.
Table: VBT1  scan count 1,  logical reads: 3,  physical reads: 0
Total writes for this command: 0

Execution Time 0.
SQL Server cpu time: 0 ms.  SQL Server elapsed time: 160 ms.

(4 rows affected)

Some Basic Questions to Start With

After making sure update statistics has been run prior to running the query, consider the following questions:
• How often is the query run? As a rule of thumb, run update statistics whenever the data in the table has changed by 5%-10%.
• Has the query in question just started performing poorly? Or has it performed poorly all along? What has changed since it started performing poorly?
• Have there been any changes to the database/table? Have columns and/or an index been added recently?
• Have indexes or the columns in the table been changed recently? For example, have columns been added to the table?
• Have you changed SQL Server versions?
• Has the data changed significantly?
• Has the query been rewritten?

Collect Output and Perform Analysis

---

Note
It is important to ensure that all output is obtained from the same run of the query. If output is not from the same run of the query, any analysis is suspect. For example, do not use showplan and statistics io output from one run of the query and statistics time and dbcc traceon output from another run of the query.

---

This section lists the necessary output for analysis, the syntax of commands to use, and a brief description of how to interpret and use the output. Not all output is required in all situations. For example, if there is no join statement involved, there is no need to use dbcc traceon 310 or 317. Or, if noexec is to be turned on, there is no need to use set statistics io or set statistics time since the query will not be executed and this output will not be returned.

More information on syntax and usage of each item is available in the SQL Server Reference Manual and the Performance and Tuning Guide.

The Text of the Query, Stored Procedure(s), View Definition(s) or Trigger(s)

Always determine the full text of the query, compiled object, or view definition. There is little analysis you can do without this. Keep in mind that a view is not an object in the same sense that a table is. You will need to get the "view definition", that is the SQL that creates the view. You can get this by running sp_helptext on the view. In many situations involving views, you will need to run the view definition as an ad hoc query.

---

Note
It can be difficult to get the query in situations where a product that generates SQL statements internally is being used, such as "drag and drop" query generating applications. In many of these cases, the SQL is hidden from you and it is sometimes necessary to get the third-party vendor involved so that the query can be retrieved from the code.

---

sp_help for All Tables Involved

Get sp_help output for all tables involved in the query. In the case of a view, get sp_help output for all tables that are the base tables of the view.

Only getting create table scripts is not sufficient for an accurate analysis. In many cases, indexes, triggers, and even columns can be added to the table after it has been created. The most accurate and up-to-date information about a table is from sp_help. If there are referential constraints on the table, run sp_helpconstraints; sp_help will not return any information about constraints on the table.

sp_help output will tell you about the structure of the table(s) and available indexes. This will be important in determining why the query is or is not performing the way it is expected to.

sp_help is a system stored procedure. The syntax is:

sp_help object_name 

See the SQL Server Reference Manual for details.

Look for Qualifying Indexes

Check that there are indexes that qualify for the SARG or join clauses in the query. Keep in mind that, at minimum, the lead column of the index must be referenced in the SARG or join clauses. If the lead column of the index is not referenced in the SARG (where clause), the distribution page of the index cannot be used for the query.

For a join clause, if the lead column is not a used in the join clause, the density information stored on the index's distribution page cannot be used. Additionally, if the index is composite, column references must be sequential. For example, if the index consists of three columns and the first and third columns are in the join clause only, the density value stored for the first column can be used. Since density information is stored on multiple columns, they must be referenced sequentially in order for the optimizer to take full advantage of the density information.

See the Performance and Tuning Guide for a detailed description on how information is stored on and used from the distribution page.

There are several considerations that go into which columns to place indexes on and which type of index to use. The Performance and Tuning Guide goes into great detail on the subject. Read the sections on the distribution page and how covering non-clustered indexes work.

Look for Mismatched Datatypes

Using the query and sp_help, look for mismatched datatypes in join clauses. For example:

	where tableA.col1=tableB.col1

A datatype mismatch on join optimization can lead to performance problems. When a join between mismatched columns occurs, the optimizer will only run a cost estimate (via q_score_index) on one table in the join. This eliminates consideration of indexes on one of the tables and will result, in most cases, in a table scan of the table that did not have a cost estimate run against it.

Problems involving mismatched datatypes usually involve mismatches between int and float, char and varchar and commonly between char null and char not null. A char null is stored internally as a varchar.

A less common mismatch of datatypes occurs between numeric/decimal columns of differing precision and/or scale. For example, if a join of two identity columns (an identity column must be numeric with a scale of 0) with differing precisions will be handled as a datatype mismatch.

However, binary/varbinary and float/int mismatches are also a problem.

Most mismatched datatypes are easy to spot:

	where col(char)=col(varchar)

	where col(float)=col(int)

Others are less obvious:

	where col(char NULL)=col(char NOT NULL)

	where col(binary NULL)=col(binary NOT NULL) 

char (null) and binary (null) are stored in SQL Server as a varchar datatype. Thus:

	where col(char NULL)=col(varchar)

is not a mismatch and statistics will be used by the optimizer. However:

	where col(char NULL)=col(char NOT NULL)

is a datatype mismatch. Be suspicious of columns with the same datatype but different handling of NULLs.

This does not apply to col(int NULL)=col(int NOT NULL). Datatype int NOT NULL and int NULL are not handled as a datatype mismatch.

If datatype mismatches are found, you can either rebuild and repopulate the tables with matching datatypes (this is often not a reasonable option) or use the convert function. The convert command may have detrimental effects on optimization (see "Using a Variable, Function, or an Arithmetic Expression in a Batch Query or Stored Procedure" on page 46 for details). If the convert function is used, it is advisable to run a performance analysis with and without it. The cost of a datatype mismatch may be cheaper than the cost of incomplete optimization.

In the case where parameters are being passed into a stored procedure, you must make sure that the parameters are specified as the same datatype as the columns they will be used to search or join. It is easy to create a procedure with a char NOT NULL parameter that will be used against a char NULL column.

If user-defined datatypes are used, run sp_help on the datatype name to ensure that there is not a datatype mismatch involved. If the same user-defined datatype name is used in a cross database join, make sure that they are defined the same way in both databases.

showplan

showplan is an option of the set command. The syntax is:

set showplan on 

showplan returns a written representation of the query plan chosen by the optimizer for the given query. This can be a single query or a set of queries (as might be found in a stored procedure). The query plan is the optimizer's chosen method for accessing the required data.

showplan is only a part of the material you investigate an optimizer problem. Getting only showplan output, the query, and the table schema is not enough to properly diagnose the problem. You need to use showplan in conjunction with other output. If you have a query in which you are forcing an index (forceindex) or a join plan (forceplan), you should still get showplan output even though you know the structure of the resulting query plan.

Reading showplan Output

Before System 11, showplan output for the query plan for a given table is preceded with "FROM TABLE". As of System 11, each "section" of showplan output begins with "The type of query is...".

When determining join order, keep in mind that showplan will print tables in the order they are joined, thus the first table printed by showplan is the outer most table, etc.

This document will not go into detail on reading the output of showplan. See "Understanding Query Plans" in the Performance and Tuning Guide for details on reading showplan output for System 11 and later SQL Servers. For pre-System 11 SQL Servers, see "v4.2-10.x How to interpret showplan Output in SQL Server" in AnswerBase for details.

statistics io

statistics io is an option of the set command. The syntax is:

set statistics io on 

statistics io returns information about the number and types of I/O performed by the query. It also describes how many times a table is accessed during the query.

statistics io is necessary to fully analyze an optimizer problem. It is one of the most useful types of output in analysis. It will give you information on the amount of work SQL Server had to do to retrieve the necessary rows for the query.

See the Performance and Tuning Guide for more information about set statistics io.

There are cases in which the query runs far too long to get statistics io information and noexec must be used. However, if at all possible, get statistics io output.

When you have a situation in which you believe that the optimizer should be using an index other than the one it chooses to use, do the following:

1. Run the query with statistics io; save the output.

2. Force the "right" index with statistics io still on.

3. Compare the two: is the run without the force more expensive? If so, you will need to determine why the index you thought should be used is more expensive than the one chosen (or a table scan). See the Performance and Tuning Guide for details.

Reading set statistics io Output

statistics io output is one of the most useful and important diagnostics for performance measurement within SQL Server.

Suppose a query returned the following set statistics io output:

Table: titles  scan count 20,  logical reads: 46,  physical reads: 0
Table: salesdetail  scan count 1,  logical reads: 3,  physical reads: 0 
Total writes for this command: 0

The line:

Table: table_name scan count number 

indicates the name of the table and the number of times the optimizer scanned through the table or an index on the table (see showplan output to determine if a table scan was performed or an index was used).

If the query is a simple select, scan count will be 1, since it read through the table or index only once. In general, this will be higher than 1 on the inner table(s) of a join. In the case of a join scan, the count indicates how many times the table was accessed to perform the join. The outermost table will be accessed once and each inner table will be accessed multiple times, depending on the number of rows in it that will join to the next table out. The innermost table will have the highest number for scan count. See "Join Processing" in the Performance and Tuning Guide for details.

scan count can be 0 when temporary tables are used, a deferred update occurs, or when worktables are created for order by or distinct statements.

The line:

logical reads: number

indicates the total number of page reads the optimizer had to perform for the query. This is a combination of reads from disk and data cache. A read is the action of reading a page. If a query requires that a row be read more than once, it will be reflected here each time the page is read. Pay attention to logical reads since they encompass both disk and cache reads.

The line:

physical reads: number

indicates the number of reads directly from disk that were required by the query. Because SQL Server has to go to disk, read the page into cache, and then either manipulate or read it from there, these reads can be a performance hit. A high amount of physical reads on a regular basis are an indication of performance problems. Make sure you configured adequate data cache for SQL Server, or that SQL Server has started with the amount of cache it is configured for (for more information, see "Determining Total Cache Space from SQL Server Errorlog" in the System Administration Guide).

On the other hand, if a query is being run for the first time after SQL Server has been started, there will usually be a high number of physical reads in order to place the pages in cache. Subsequent runs of the query will show few if any physical reads, as in the example:

Table: titles  scan count 20,  logical reads: 46,  physical reads: 0

The line

total writes for this command: number

indicates the number of writes to pages that were done to satisfy the query (inserts, update, deletes, and creation of temporary and/or worktables). If the number is higher than expected, reexamine the query to ensure that only those rows that are indicated were affected or that unnecessary temporary and/or worktables are not being used.

set rowcount and statistics io Output

If set rowcount has been set to return a certain number of rows, statistics io output will not be useful for analysis. When set rowcount is used, the number of reads that need to be performed to return the requested rows will be less than the number of rows if set rowcount in not used.

In the example below, the query selects all values from title_id in the table titles, a total of 5000 values. The query used a covering non- clustered index and performed 75 reads to return the requested rows.

Table: titles  scan count 1,  logical reads: 75,  physical reads: 75
Total writes for this command: 0

In this second example, set rowcount was set to 50, returning only 50 rows of the 5000 qualifying rows for the query. In this case, the query required only one read.

Table: titles  scan count 1,  logical reads: 1,  physical reads: 0
Total writes for this command: 0

It is important to ensure that set rowcount is not on when using statistics io in analysis.

statistics time

statistics time is an option of the set command. The syntax is:

set statistics time on

statistics time displays parse and compile time, SQL Server CPU time, and Server elapsed time for a query (in milliseconds). In general, set statistics time is not as useful as the other items you have at your disposal.

Reading statistics time Output

Parse and Compile Time: number ms. The total amount of time it took to parse, optimize, and compile the query. This can be very useful when analyzing the problem. A large, complex query may take more time to parse and compile than to run.

SQL Server CPU time: number ms. The total time this section of the query received in the CPU. This number is of interest when it is high; in most cases, it will be 0. This is because the value is too small to be represented here, and thus is rounded to 0. A high number indicates that the CPU was used heavily for this query. Since CPU time is estimated by the optimizer when it estimates the query's cost for a join, this information can be useful

SQL Server Elapsed Time: number ms. The total elapsed time for this part of the query to run. This figure is of interest if no other processes were running on SQL Server at the time that the query ran. If there is I/O, lock, or network contention, the query will have to wait its turn. The time the query waits is included in the figure.

When Working with Stored Procedures

When you need to analyze an issue with a stored procedure (or trigger), there are a few extra considerations to keep in mind.

The query plan for a compiled object is created when the object is compiled. Generally, this occurs the first time the object is executed. This query plan is then stored in sysprocedures and thus on disk. Each time the procedure executes, the query plan is placed into cache and used by the procedure.

This can be a problem when analyzing the performance of a compiled object because the query plan created at compile time was created with the values that were passed into the procedure at that time. This occurs if the procedure is created for use with input parameters, which is generally the case with stored procedures. The optimized query plan may perform well for the values that were passed into the procedure at compilation, but that same query plan may be poorly optimized for another set of parameter values.

If you try to get all the required output from an execution of a stored procedure that is not recompiled, you will only get showplan, statistics io, and statistics time. The dbcc traceon output will not be returned because the optimizer is not creating a new query plan. To get all the output, ensure that the procedure is recompiled before executing it. There are a four ways to do this:

• Drop the procedure, re-create it, and execute it.
• Execute the procedure with recompile; see the SQL Server Reference Manual for the syntax.
• Run sp_recompile on a table that is referenced by the procedure; see the SQL Server Reference Manual for the syntax.
• Re-create the procedure with the recompile option; see the SQL Server Reference Manual for the syntax.

noexec, set fmtonly on and Stored Procedure Analysis

If noexec is used when executing a procedure, the procedure will not execute or compile unlike noexec's effect on a batch query. On a batch query, noxec will not execute the query but the query plan will be compiled.

If you do not want the procedure to execute, you can still see the query plan and the dbcc traceon output. To do this, run the set command option fmtonly. The syntax for this command is:

set fmtonly on 

This allows the procedure to compile but not execute. You will not see the output of set statistics io or time because the procedure will not execute.

set fmtonly is designed to output the format (column headers) of a query only, but it also allows compilation of a procedure or batch query.

When Working with Views

Views require that the query plan for the view definition be merged with the query plan of the query being run against the view. When analyzing problems involving views, it is usually necessary to run the view definition (the SQL that creates the view) as a single query in isql. Do this with all the output mentioned in this writeup:
• showplan
• statistics time
• statistics io
• dbcc traceon(3604,302...) ­ add 310 if there is a join involved and 317 if better performance of a join can be forced.
This is necessary in order to determine how well optimized the view itself is. Check the output for the run of the view definition for any optimization problems. If any are found, try to resolve them in the definition first. Another approach is to manually merge the view definition and the query against it into a single query and run that.

Using forceindex and/or forceplan in Problem Analysis

What are forceindex (Specifying an Index) and forceplan?

Generally, the access plan that the optimizer chooses for a given query is the most efficient. However, there may be times when you know or feel that a different access method may be better. In such situations, there are two "force commands" that can be used.

Both forceindex and forceplan have been a part of the Sybase SQL Server for many versions. Both are documented as of System 11. Both are excellent analysis tools and can be used in situations in which an optimizer weakness has been encountered, or in situations where you need to determine if the optimizer is in fact building and using the most efficient query plan.

Should you find that there is a weakness or problem in the optimizer, the forceindex and forceplan can be used to improve query efficiency and performance until a fix can be supplied. See the example below of using forceindex as an analysis tool.

---

Note
While forceindex and forceplan are useful analysis tools and as a temporary fix, it is inadvisable to become dependent on them.

---

forceindex/Specifying an Index

---

Note
forceindex is the pre-System 11 term. In System 11 and above, the term "specifying an index" is used. forceindex is used in this document.

---

forceindex tells the optimizer to use the index that is specified for a table, and to ignore all other indexes on the table for the query.

In many optimization investigations, you will need to verify that the optimizer is making the right index choice. To test this, use forceindex.

Previous to System 11, forceindex was not documented and was "officially" unsupported. As of System 11, it is documented and supported. The syntax for its use changed in System 11.

Pre-System 11 Syntax: forceindex

Place the indid of the index you wish to force in parentheses immediately following the table name in the from clause of the query:

select colA,colB,colC
from table_name(indid)
where.....

There are a couple of ways to get the indid for an index:
• To get the indid and name of all indexes on a table, run the following query:

  	
  1> select indid,sysindexes.name
  2> from sysindexes, sysobjects
  3> where sysindexes.id=sysobjects.id
  4> and sysobjects.name="table_name"
  5> go
  

• To get the name of an index using its indid, run the following query:

  	
  1> select indid,sysindexes.name
  2> from sysindexes, sysobjects
  3> where sysindexes.id=sysobjects.id
  4> and sysobjects.name="table_name"
  5> and sysindexes.indid=index_id
  6> go
  

System 11 Syntax: Specifying an Index

In System 11, forceindex is now fully supported. Functionally the same as in previous SQL Server versions, the new syntax allows the use of an index name rather than the index ID:

select colA,colB,colC
from table_name (index index_name)
where.....

Refer to the Performance and Tuning Guide for more information about forceindex.

Specifying/Forcing an Index as an Analysis Tool

The following is an example of specifying/forcing an index as an analysis tool. The table titles has a clustered index on column title_id, a non-clustered index on columns title_id, title, type, and price, and a non-clustered index on column price.

The following query is run first without specifying an index. Then the query is run twice, each time specifying an index not chosen by the optimizer. The output of showplan indicates that the specified (forced) indexes were used. The output of statistics io show that, in this case, the index chosen by the optimizer was the most efficient. This is seen by the number of logical reads performed.

In this first run of the query, the showplan output indicates that the optimizer chose to use the non-clustered index tid_title_type_price, which covers the query (see the Performance and Tuning Guide for a discussion of non-clustered covering indexes). The statistics io output shows that a total of three logical reads were performed:

	
1> select title_id,title,type,price
2> from titles 
3> where title_id between "T16000" and "T16100"
4> and price < 20
5> go

QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1
        The type of query is SELECT.

        FROM TABLE
            titles
        Nested iteration.
        Index : tid_title_type_price
        Ascending scan.
        Positioning by key.
        Index contains all needed columns. Base table will not be read.
        Keys are:
            title_id
        Using I/O Size 2 Kbytes.
        With LRU Buffer Replacement Strategy.

Table: titles  scan count 1,  logical reads: 3,  physical reads: 0
Total writes for this command: 0
(1 row affected)

In the run of the query below, the clustered index tid was specified. showplan output indicates it was used and statistics io output shows that four logical reads were performed. This is slightly more expensive than the non-clustered index chosen by the optimizer.

	
1> select title_id,title,type,price
2> from titles (index tid)
3> where title_id between "T16000" and "T16100"
4> and price < 20
5> go

QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1
        The type of query is SELECT.

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

Table: titles  scan count 1,  logical reads: 4,  physical reads: 0
Total writes for this command: 0
(1 row affected)

In this final example of the query, the non-clustered index on column price is specified for use. showplan indicates that the index was used. statistics io shows that 1171 reads were performed; this is considerably more expensive than the non-clustered index chosen by the optimizer.

	
1> select title_id,title,type,price
2> from titles (index price)
3> where title_id between "T16000" and "T16100"
4> and price < 20
5> go

QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1
        The type of query is SELECT.

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

Table: titles  scan count 1,  logical reads: 1171,  physical reads: 0
Total writes for this command: 0
(1 row affected)

Using forceplan

forceplan is an option of the set command. The syntax is:

set forceplan on 

(The syntax is the same in System 11 as it was in previous SQL Server releases.)

forceplan tells the optimizer to join tables in the order (join order) they are listed in the query's from clause. forceplan is used when analyzing the performance and optimization of joins. See "join optimization" in the Performance and Tuning Guide for more information.

In situations where you believe that a join order other than that chosen by the optimizer would be more efficient, use forceplan to test it.

Forcing a Join Order as an Analysis Tool In the first example below, the optimizer is allowed to chose a join plan without the join order being forced with forceplan. The join order it chooses is titles, titleauthor, and authors. This join plan does not result in reformatting. (See "Using traceon 310 and 317 to Analyze Join Performance and Processing" on page 56 of this document and the Performance and Tuning Guide for a discussion of join order.)

	
1> select au_lname, title
2> from authors a, titles t, titleauthor ta
3> where a.au_id=ta.au_id
4> and t.title_id=ta.title_id
5> and a.au_lname like "L%"
6> and t.type="computer"
7> go

QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1
        The type of query is SELECT.

        FROM TABLE
            titles
        Nested iteration.
        Index : tid_title_type_price
        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
            titleauthor
        Nested iteration.
        Using Clustered Index.
        Index : t_id
        Ascending scan.
        Positioning by key.
        Keys are:
            title_id
        Using I/O Size 2 Kbytes.
        With LRU Buffer Replacement Strategy.

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

Table: authors  scan count 737,  logical reads: 2240,  physical reads: 0
Table: titles  scan count 1,  logical reads: 193,  physical reads: 0
Table: titleauthor  scan count 582,  logical reads: 1180,  physical 
reads: 0
Total writes for this command: 0

	
1> set forceplan on
2> go

	
1> select au_lname, title
2> from titleauthor ta, authors a, titles t 
3> where a.au_id=ta.au_id
4> and t.title_id=ta.title_id
5> and a.au_lname like "L%"
6> and t.type="computer"
7> go

QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1
        The type of query is SELECT.

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

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

        FROM TABLE
            titles
        Nested iteration.
        Index : tid_title_type_price
        Ascending scan.
        Positioning by key.
        Index contains all needed columns. Base table will not be read.
        Keys are:
            title_id
        Using I/O Size 2 Kbytes.
        With LRU Buffer Replacement Strategy.

Table: titleauthor  scan count 1,  logical reads: 126,  physical 
reads: 0
Table: authors  scan count 6250,  logical reads: 19030,  physical reads: 
0
Table: titles  scan count 250,  logical reads: 775,  physical reads: 0
Total writes for this command: 0

statistics io and the Use of forceindex and/or forceplan in Analysis


statistics io is the key element when using forceindex and/or forceplan as analysis tools. statistics io will display the type and quantity of I/O performed by a query. See "statistics io" on page 18 of this document for details. The examples following the above sections on specifying an index and using forceplan help illustrate how important statistics io can be when using these analysis tools.

When using statistics io with forceindex and/or forceplan, follow these steps:

1. Run the query with statistics io on (you should also set the other output on too, particularly set showplan on, traceon 302 and/or 310).

2. Note the values for statistics io.

3. Run the query again with the same output on using forceindex and/or forceplan.

4. Note the value of statistics io.

5. Verify that the query plan you expect to be used is being used; check via showplan.

6. Determine which plan is cheaper in terms of logical I/O.

Looking at statistics io first in this type of analysis will give you a quick idea of whether the force was more efficient than the optimizer's chosen query plan.

forceindex is useful:

• In situations where you believe that an index other than that chosen by the optimizer should be used
• When you believe that a join order other then that chosen by the optimizer would be most efficient
In such cases, set statistics io should be turned on and the query run both with and without forcing the index in question. If the logical reads are cheaper without forcing the index, then the index was too "expensive" to use. However, there are cases in which the optimizer makes a wrong decision about which index to use. In these cases, the force of the index will be more efficient (fewer logical reads) than the index chosen by the optimizer. These situations will require further analysis.

The Effect of noexec on Output Necessary for Analysis

noexec is an option of the set command. The syntax is:

set noexec on 

The use of noexec will limit the output available for use in analysis. If noexec is on, values for set statistics io on and set statistics time on will not be returned. The use of noexec will not allow the query to execute, thus no I/O or time statistics will be generated. When noexec is used in that analysis of a procedure, the procedure will neither compile or execute. In such a case, you will on see showplan output saying: "The type of query is EXECUTE". Since noexec will not turn off optimization, the optimizer will run its cost estimates on qualifying indexes and create a query plan. This means that the output of set showplan on and the optimizer-related dbcc traceons will be generated.

If noexec is used, it should be the last set command option used prior to the query being run. noexec will prevent the execution of any subsequent commands.

dbcc traceon Commands

There are three dbcc traceon commands that are particularly useful in analysis. They should be included in the materials you use to analyze an optimizer issue. In some cases, all three will be necessary and in other cases, only one or two will be called for. The following sections discuss all three, give examples of their output, and describe their use as analysis tools.

Using dbcc traceon 302 in Analysis

The Sybase SQL Server Optimizer is a cost-based optimizer. This means that the optimizer's decisions on which data access method (use an index or table scan) is best is based on how much it will cost in terms of reads ­ both logical (cache) reads and physical (from disk) reads. This "costing" involves the use of complex algorithms to come to a decision.

What is dbcc traceon 302? dbcc traceon 302 displays the cost estimates generated by q_score_index. This part of the code looks at the query for SARGs and join statements. It then checks the referenced table(s) for qualifying index(es). If any are found, it then estimates the cost of using a particular index. It compares the costs of all qualifying index(es) and chooses the cheapest.

It is strongly recommended that you read through the Performance and Tuning Guide to get a basic understanding of traceon 302 output and related subjects.

The syntax is:

dbcc traceon(3604,302)

---

Note
traceon 3604 sends the output of other traceon commands that provide output to the monitor or to the file you specify. traceon 3605 sends the output to the error log. Note that this can consume a large amount of space in the error log.

---

The Distribution Page The optimizer depends on statistical information about an index or index(es) that qualify for a query in order to determine how useful the index is for the query. The index's selectivity for the query describes how useful it will be for the query (see below). The information that the optimizer needs to determine the index's selectivity is stored in the index's distribution page.

There are two forms of statistics stored on the distribution page. The first set of statistics are called the "steps". This stores information about the distribution of values on the lead column of the index. The second set of statistics is the density. This is information describing the average percentage of duplicates on the first column of the index and all sequential combinations of columns in the index.

The steps are used by the optimizer when determining the selectivity of an index for a SARG. The density is used to determine the selectivity of an index for a join.

This writeup will not go into detail about the distribution page. Refer to the Performance and Tuning Guide for more information.

Things to Look For The following sections provide information about what to look for in traceon 302 output. The query and its traceon 302 output below will be used in many of the following sections. However, in sections that discuss unusual or exceptional output, different queries are used. A different query has also been used for examples where larger tables with different distributions of values were needed. In all cases, the query and a short explanation of the table are given.

	
1> select ta.au_id,t.title,pub_id,pubdate,price
2> from titles t, titleauthor ta
3> where type = "cooking"
4> and price = 10
5> and t.title_id = ta.title_id
6> order by price
7> go

*******************************
Entering q_score_index() for table 'titles' (objectid 208003772, varno = 
0).
The table has 5000 rows and 621 pages.
Scoring the SEARCH SORTAVERT CLAUSE:
	price EQ 
	type EQ 

Base cost: indid: 0 rows: 5000 pages: 621 prefetch: N 
	I/O size: 2 cacheid: 0 replace: LRU
Estimated sort avert cost: indid: 3 rows: 5000 pages: 5050
Relop bits are: 5 
Qualifying stat page; pgno: 1188 steps: 132 
Search value: *** CAN'T INTERPRET ***
No steps for search value--qualpage for LT search value finds value 
between step K, K + 1, K = 15--use betweenSC 
Estimate: indid 3, selectivity 0.012665, rows 63 pages 65 index height 2 

Cheapest sort-avert index is index 3, costing 65 pages 	and generating 6 
rows per scan.
Search argument selectivity is 0.001266.

*******************************

*******************************
Entering q_score_index() for table 'titles' (objectid 208003772, varno = 
0).
The table has 5000 rows and 621 pages.
Scoring the SEARCH CLAUSE:
	price EQ 
	type EQ 

Base cost: indid: 0 rows: 5000 pages: 621 prefetch: N 
	I/O size: 2 cacheid: 0 replace: LRU
Relop bits are: 5 
Qualifying stat page; pgno: 1188 steps: 132 
Search value: *** CAN'T INTERPRET ***
No steps for search value--qualpage for LT search value finds 
value between step K, K + 1, K = 15--use betweenSC 
Estimate: indid 3, selectivity 0.012665, rows 63 pages 65 index height 2 

Cheapest index is index 3, costing 65 pages and 
	generating 6 rows per scan, using no data prefetch (size 2)
	on dcacheid 0 with LRU replacement
Search argument selectivity is 0.001266.

*******************************

*******************************

Entering q_score_index() for table 'titleauthor' (objectid 176003658, 
varno = 1).
The table has 6250 rows and 126 pages.
Scoring the SEARCH SORTAVERT CLAUSE:
No Search clauses for table

No sort avert index has been found for table 'titleauthor' (objectid 
176003658, varno = 1).
*******************************

******************************* 
Entering q_score_index() for table 'titles' (objectid 208003772, varno = 
0). 
The table has 5000 rows and 621 pages. 
Scoring the JOIN SORTAVERT CLAUSE: 
	title_id EQ title_id 
 
Base cost: indid: 0 rows: 5000 pages: 621 prefetch: N  
	I/O size: 2 cacheid: 0 replace: LRU 
Relop bits are: 4  
Estimate: indid 1, selectivity 0.000198, rows 1 pages 3 index height 2  
Unique clustered index found--return rows 1 pages 3  
Relop bits are: 804  
Estimate: indid 2, selectivity 0.000198, rows 1 pages 4 index height 3  
Estimated sort avert cost: indid: 3 rows: 5000 pages: 5050 
Relop bits are: 804  
Estimate: indid 4, selectivity 0.000198, rows 1 pages 4 index height 3  
 
Cheapest sort-avert index is index 3, costing 5050 pages  
	and generating 1 rows per scan. 
Join selectivity is 5000.000000. 

******************************* 

******************************* 
Entering q_score_index() for table 'titles' (objectid 208003772, varno = 
0). 
The table has 5000 rows and 621 pages. 
Scoring the JOIN CLAUSE: 
	title_id EQ title_id 
 
Base cost: indid: 0 rows: 5000 pages: 621 prefetch: N  
	I/O size: 2 cacheid: 0 replace: LRU 
Relop bits are: 4  
Estimate: indid 1, selectivity 0.000198, rows 1 pages 3 index height 2  
Unique clustered index found--return rows 1 pages 3  
Relop bits are: 804  
Estimate: indid 2, selectivity 0.000198, rows 1 pages 4 index height 3  
Relop bits are: 804  
Estimate: indid 4, selectivity 0.000198, rows 1 pages 4 index height 3  
 
Cheapest index is index 1, costing 3 pages and  
	generating 1 rows per scan, using no data prefetch (size 2) 
	on dcacheid 0 with LRU replacement 
Join selectivity is 5000.000000. 

******************************* 

******************************* 
Entering q_score_index() for table 'titleauthor' (objectid 176003658, 
varno = 1). 
The table has 6250 rows and 126 pages. 
Scoring the JOIN SORTAVERT CLAUSE: 
	title_id EQ title_id 
 
Base cost: indid: 0 rows: 6250 pages: 126 prefetch: N  
	I/O size: 2 cacheid: 0 replace: LRU 
Relop bits are: 4  
Estimate: indid 1, selectivity 0.000229, rows 1 pages 2 index height 1  
 
No sort avert index has been found for table 'titleauthor' (objectid 
176003658, varno = 1).

******************************* 

*******************************
Entering q_score_index() for table 'titleauthor' (objectid 176003658, 
varno = 1). 
The table has 6250 rows and 126 pages. 
Scoring the JOIN CLAUSE: 
	title_id EQ title_id 
 
Base cost: indid: 0 rows: 6250 pages: 126 prefetch: N  
	I/O size: 2 cacheid: 0 replace: LRU 
Relop bits are: 4  
Estimate: indid 1, selectivity 0.000229, rows 1 pages 2 index height 1  
 
Cheapest index is index 1, costing 2 pages and  
	generating 1 rows per scan, using no data prefetch (size 2) 
	on dcacheid 0 with LRU replacement 
Join selectivity is 4369.000001. 

*******************************

Check the Table Name that is Being Costed

Entering q_score_index()for table 'titleauthor'(objectid 176003658, 
varno = 1). 

Make sure that the scoring is being run on the table you expect it to. The varno is of use when there is a join involved and you are working with traceon 310 and/or 317.

Check that Row and Page Counts are Accurate

The table has 6250 rows and 126 pages. 

The second line of 302 output will give you a count of rows and pages in the table. This information is critical to the optimizer. From this information the optimizer determines how many rows per page there are, and what the base cost of the table is. Base cost is the cost of doing a table scan (indid 0). The row and page count information is obtained form the OAM (Object Allocation Management) page. There are times when this information may not be up to date. This may result in row and page counts that are not accurate and thus affect optimization. If you suspect that the counts are off, run dbcc checktable on the table to update the OAM page (see the SQL Server Reference Manual for syntax).

Check the Type of Statement that is Being Costed

Scoring the SEARCH CLAUSE: 
        price EQ 
        type EQ 
Scoring the JOIN CLAUSE: 
        title_id EQ title_id 
      Scoring the SEARCH SORTAVERT CLAUSE: 
	        price EQ  
	        type EQ  
      Scoring the JOIN SORTAVERT CLAUSE: 
	        title_id EQ title_id 

There are three types of SQL clauses that q_score_index will generate cost estimates for: SARGs (search clause), joins (join clause) and SORTAVERT (occurs when the query contains an ORDER BY statement). All SARGs will be costed first, followed by all joins. In both the case of a SARG or join, if SORTAVERT is called it will be costed before either the SARG or the join clause. See the section below for more details. Check the type of clause for the portion of the output you are reading.

About SORTAVERT

In SQL Server version 11.x the optimizer is able to determine if an index can be used instead of a worktable when the query contains an ORDER BY clause. If an index is useful, the optimizer can avoid the extra cost of creating, populating and sorting a worktable.

When the query contains an ORDER BY statement q_score_index() will be run first to determine if an index can be efficiently used to avoid a sort. This costing will be run before costing of other SARGs or join clauses. First SEARCH SORTAVERT will run for qualifying SARGs followed by regular SARGs, then JOIN SORTAVERT will run followed by regular joins.

In the example output q_score_index() has run a SEARCH SORTAVERT CLAUSE on table titles before it runs a cost estimate of the SARG on the same table (titles). The result is that it estimates that a non- clustered index (indid 3) would be the cheapest to use in order to avoid a sort. It then goes on to run a SEARCH SORTAVERT CLAUSE for table titleauthor, however it determines that there is no SARG on titleauthor and reports this. Since there is no SARG on titleauthor so q_score_index() moves on to the join clauses.

The next cost estimate is a JOIN SORTAVERT CLAUSE on table titles. Here the optimizer estimates that a non-clustered index (indid 3) will be cheapest to perform the join. The optimizer then goes on to score the join clause. It then finds that there is no sort avert index available on table titleauthor and goes on to score the join clause for the table.

For more information on sorts and their performance impact see the SQL Server Performance and Tuning Guide.

Immediately under the line that tells you the type of clause that is being costed is a listing of the columns involved and the relational operator(s) used. In the "SEARCH CLAUSE" example above, the columns that are being costed in this section of 302 output are type and price. There is an equity on type and a less than on price. In the "JOIN CLAUSE" example, the join is on title_id in this table and in the other table of the join.

Base cost: indid: 0 rows: 5000 pages: 621

The optimizer must have a baseline to compare the cost of qualifying indexes to. The base cost is the cost of doing a table scan (indid 0). This can sometimes be confusing in analysis. The base cost rows and pages equals the rows and pages in the table.

Relop bits are: 5

You can ignore relop; it is a representation of the relational operator being used, and of no value in analysis.

Qualifying stat page; pgno: 1161 steps: 132

Next you will usually see that a statistics page (distribution page) was found. It will give you the page number and the number of steps on the page. See the Performance and Tuning Guide for information on the distribution page and discussion of the step values.

A message reading "No statistics page--use magicSC" means that while there is an index that qualifies for the clause, there is no distribution page from which to get the necessary statistics. This happens when a table is empty when an index is built on it. If a table is built, then an index created on it, and then the table is populated with data, there is no distribution page for the index(es). Sometimes this occurs when a table is truncated and then repopulated. In both cases, run update statistics to have a distribution page written.

If the output skips from "Base cost..." to "Cheapest index is...", there is no qualifying index for the clause. In such a case, the "Cheapest index is..." line will show indid 0 (a table scan). Either add an index (usually not practical) or see if an indexed column can be referenced in the query. If this is a commonly used query, adding an index may be the best answer.

Search value: *** CAN'T INTERPRET ***

This message appears when a datatype other than int, tinyint, smallint, char, varchar, binary, or varbinary is involved in the SARG. This is not a problem. It simply means that SQL Server does not want to take the time to print a representation of the datatype's structure.

If one of the above datatypes is involved in the SARG, the value of the constant being searched on will be printed:

Search value:10

Order of Clauses in traceon 302 Output It is important to understand that q_score_index runs scores on SARGs first, followed by join clauses. The order of traceon 302 output reflects this. In some cases, an index that is found to be cheap for a SARG will not be used because it is too expensive to be used for a join clause. This is often a point of confusion when reading traceon 302 output.

To understand the optimizer's costing of join clauses, use traceon 310 and in some cases 317 (see "Using traceon 310 and 317 to Analyze Join Performance and Processing" on page 56 for details).

Understanding Selectivity

Selectivity is a description of how useful a qualifying index is for a given SARG or join clause. It is reported as a value.

An indexed column with few duplicate values (low density) is more selective than an indexed column with many duplicates (high density).

In the case of a SARG, the selectivity is reported as a float representing a fraction of the rows in the first column of the qualifying index that the optimizer expects will qualify for the SARG. Thus, a selectivity of 1 would indicate that all rows in the indexed column will qualify for the SARG. A selectivity of 1 also indicates that all values in the column are the same. A selectivity of 0.50 indicates there are two distinct values in the column, and so on.

For a join clause, the selectivity is also printed as a float (in pre- System 11 SQL Servers it is printed as a whole number). Here, the higher the number, the fewer rows are expected to qualify for the join, and thus the selectivity is better.

A rule of thumb is that the lower the selectivity value for a SARG, the better, and the higher the selectivity value for a join clause, the better.

If the selectivity of an index in the "Cheapest index is..." of 302 output has a "bad" selectivity value (a large number for a SARG or a small number for a join clause), consider reindexing to change the first column to a more selective column. Or, in the case of a join clause, adding a selective column and including it in the join clause will improve selectivity.

Selectivity and SARGs

SARGs and joins use different statistical information to derive a selectivity value and a qualifying index. As mentioned above, there is a difference in the selectivity values for joins and SARGs. The three examples below illustrate how the number of duplicate values (increased density) can affect SARG selectivity:

Table VBT2 has 75000 rows and 1787 data pages. 

Column test3 has two distinct values, thus there are 37500 rows containing the value 10. Since there are only two values, the SARG selectivity of a query against an index with test3 as its lead column will be 0.500000. This indicates that half of the rows in the column will qualify for the SARG. A table scan will be chosen since it is cheaper than using the index in this case.

	
1> select * 
2> from VBT2
3> where test3 = 10
4> go

*******************************
Entering q_score_index() for table 'VBT2' (objectid 1600008731, varno 
= 0).
The table has 75000 rows and 1786 pages.
Scoring the SEARCH CLAUSE:
        test3 EQ 

Base cost: indid: 0 rows: 75000 pages: 1786 prefetch: N 
        I/O size: 2 cacheid: 0 replace: MRU
Relop bits are: 5 
Qualifying stat page; pgno: 38097 steps: 181 
Search value: 10
Match found on statistics page 
equal to several rows including 1st or last--use endseveralSC 
Estimate: indid 2, selectivity 0.500000, rows 37500 pages 37800 index 
height 3 

Cheapest index is index 2, costing 37800 pages and 
        generating 37500 rows per scan, using no data prefetch (size 2)
        on dcacheid 0 with LRU replacement
Search argument selectivity is 0.500000.

*******************************

Column qty has 250 distinct values, each value having 300 rows. The increase in the number of distinct values (decrease in density) increases selectivity, output as a lower selectivity value. In this case, the optimizer determines that the number of reads via the index is considerably cheaper than a table scan and the index is chosen.

	
1> select * 
2> from VBT2
3> where qty = 10
4> go

*******************************
Entering q_score_index() for table 'VBT2' (objectid 1600008731, varno 
= 0).
The table has 75000 rows and 1786 pages.
Scoring the SEARCH CLAUSE:
        qty EQ 

Base cost: indid: 0 rows: 75000 pages: 1786 prefetch: N 
        I/O size: 2 cacheid: 0 replace: MRU
Relop bits are: 5 
Qualifying stat page; pgno: 39545 steps: 500 
Search value: 10
Match found on statistics page 
equal to several rows in middle of page--use midseveralSC 
Estimate: indid 5,selectivity 0.004000,rows 300 pages 304 index height 3 

Cheapest index is index 5, costing 304 pages and 
        generating 300 rows per scan, using no data prefetch (size 2)
        on dcacheid 0 with LRU replacement
Search argument selectivity is 0.004000.

*******************************

Column id is unique, but not created as a unique index; it contains 75000 distinct values with one row each. In this case, the optimizer sees that there can only be one row containing the value 10 and determines the selectivity to be 0. See "What Does Selectivity of 0.000000 Mean?" on page 55; also see the Performance and Tuning Guide for a discussion of how indexes created as unique are handled.

	
1> select * 
2> from VBT2
3> where id = 10
4> go

*******************************
Entering q_score_index() for table 'VBT2' (objectid 1600008731, varno 
= 0).
The table has 75000 rows and 1786 pages.
Scoring the SEARCH CLAUSE:
        id EQ 

Base cost: indid: 0 rows: 75000 pages: 1786 prefetch: N 
        I/O size: 2 cacheid: 0 replace: MRU
Relop bits are: 5 
Qualifying stat page; pgno: 39993 steps: 334 
Search value: *** CAN'T INTERPRET ***
No steps for search value--qualpage for LT search value finds 
value between step K, K + 1, K = 0--use betweenSC 
Estimate: indid 6, selectivity 0.000000, rows 1 pages 4 index height 3 

Cheapest index is index 6, costing 4 pages and 
        generating 1 rows per scan, using no data prefetch (size 2)
        on dcacheid 0 with LRU replacement
Search argument selectivity is 0.000000.

*******************************

Selectivity and Joins

The following two examples illustrate the effects of density (number of duplicate values) on join clause selectivity. The optimizer uses information about the density of the columns of the index to derive a selectivity for qualifying indexes in a join.

As mentioned earlier, table VBT2 has 75000 rows with 1757 data pages. Column test in VBT2 has 2 distinct values, giving it a low selectivity and a very high density. Table VBT has 150000 rows with 3261 data pages. Column test2 in VBT has 10001 distinct values, giving it a higher selectivity and a lower density. The traceon 302 output below shows that the optimizer ran a cost of both tables using the two possible permutations (see "Using traceon 310 and 317 to Analyze Join Performance and Processing" on page 56 for details).

In the first example, the optimizer makes VBT2 the outermost table of the join, and accesses it via a non-clustered index. This will result in a selectivity of 10.999496 and cost 6858 reads (this is higher than the number of pages in the table). In the second example, with table VBT as the outermost table and accessing it via a non-clustered index, a selectivity of 10922.500003 will result with a cost of only 16 reads. Using VBT as the outer table is obviously cheaper. See the Performance and Tuning Guide for a detailed discussion of nested iteration and its effects on join processing and performance.

	
1> select *
2> from VBT,VBT2
3> where VBT.test=VBT2.test2
4> and VBT.id between 100 and 200
5> go

q_score_index with VBT2 as the Outermost Table of the Join

*******************************
Entering q_score_index() for table 'VBT2' (objectid 1600008731, varno 
= 1).
The table has 75000 rows and 1786 pages.
Scoring the JOIN CLAUSE:
        test EQ test2

Base cost: indid: 0 rows: 75000 pages: 1786 prefetch: N
        I/O size: 2 cacheid: 0 replace: MRU
Relop bits are: 5
Estimate: indid 4, selectivity 0.090913, rows 6818 pages 6858 index 
height 3

Cheapest index is index 4, costing 6858 pages and
        generating 6818 rows per scan, using no data prefetch (size 2)
        on dcacheid 0 with LRU replacement
Join selectivity is 10.999496.

*******************************

q_score_index with VBT as the Outermost Table of the Join

*******************************
Entering q_score_index() for table 'VBT' (objectid 1136007078, varno = 
0).
The table has 150000 rows and 3261 pages.
Scoring the JOIN CLAUSE:
        test2 EQ test

Base cost: indid: 0 rows: 150000 pages: 3261 prefetch: N
        I/O size: 2 cacheid: 0 replace: MRU
Relop bits are: 5
Estimate: indid 6, selectivity 0.000092, rows 14 pages 16 index height 3

Cheapest index is index 6, costing 16 pages and
        generating 14 rows per scan, using no data prefetch (size 2)
        on dcacheid 0 with LRU replacement
Join selectivity is 10922.500003.

*******************************

What are "MagicSC" and "DensitySC"?

MagicSC and densitySC are "methods" that the optimizer uses when an estimate of selectivity based on full statistical information is not possible.

Under some circumstances, either the distribution page is missing, or the steps information on it cannot be used. In such cases, the magicSC or densitySC will be used. traceon 302 output will contain the following line:

SARG is a subbed VAR or expr result or local variable (constat = 4)--use 
magicSC or densitySC

The magicSC is a set of three hardcoded values that are used as selectivity when the steps information on the distribution page and the densitySC cannot be used. Which of the three hardcoded values used as selectivity will be dependent on the relational operator used in the SARG or join clause. Following are the values used for the three sets of operators:

equity (=) ­ the optimizer assumes that 10% of the rows in the indexed column will qualify for SARG or join clause, making the selectivity 0.100000.

closed interval (columnA >= value1 and columnA < value2, BETWEEN) ­ the optimizer assumes that 25% of the rows in the indexed column will qualify, making the selectivity 0.250000.

open interval (>, <, <=, >=) ­ the optimizer assumes that 33% of the rows in the indexed column will qualify, making the selectivity 0.330000.

In most situations, the selectivity values that result from the magicSC are not close to the actual selectivity of the index if a constant and thus known value were to be used in the SARG or join. However, there may be cases in which the use of the magicSC's choice of a value may result in a better selectivity for the index than it actually has. In such a case, the index may be chosen by the optimizer, and may result in fewer reads depending on SARG or join clause. This is possible when the lead column of the index has a high percentage of duplicate values.

The densitySC ­in certain situations when the steps are not usable, it may be possible for the optimizer to estimate a selectivity by using the index's density (average percentage of duplicate values across the columns of the index). The same line will appear in traceon 302 output that appears when the magicSC is used. The only way to tell if the densitySC is being used, rather than the magicSC, is by the estimated selectivity.

If the selectivity is one of the values mentioned for the magicSC, or a value divisible by 10 (that is, 0.010000), then the magicSC is being used. If the line appears in the 302 output and the selectivity is any other value, then the densitySC is being used.

The use of the densitySC is generally more desirable than the magicSC. This is due to the fact that using density values from the distribution page can provide a more accurate measure of selectivity than a hardcoded value. However, there is a very slight possibility that the selectivity derived from the magicSC may be more efficient than the densitySC.

Situations that Will Cause the MagicSC or DensitySC to be Used

There is No Qualifying Index on the SARG and/or Join Column(s)

This situation is not truly one in which either SC is called. Since there is no index available, the optimizer has to establish a selectivity for a table scan (indid 0). The values will be the same as those used by the magicSC and are also based on the operator. A table scan will occur no matter the selectivity, but a value for selectivity is necessary to complete q_score_index.

Following is sample traceon 302 output when there is no qualifying index:

*******************************
Entering q_score_index() for table 'VBT' (objectid 1136007078, varno = 
0).
The table has 150000 rows and 3261 pages.
Scoring the SEARCH CLAUSE:
        onum EQ

Base cost: indid: 0 rows: 150000 pages: 3261 prefetch: N
        I/O size: 2 cacheid: 0 replace: MRU

Cheapest index is index 0, costing 3261 pages and
        generating 15000 rows per scan, using no data prefetch (size 2)
        on dcacheid 0 with MRU replacement
Search argument selectivity is 0.100000.

*******************************

There is No Distribution Page Available

This situation arises when an index is created on an empty table. In this case, there is no distribution page and thus no statistics for the index to use. When there are no statistics to be used, the magicSC or densitySC must be used by the optimizer to estimate selectivity.

*******************************
Entering q_score_index() for table 'VBT' (objectid 80003316, varno = 0).
The table has 1 rows and 1 pages.
Scoring the SEARCH CLAUSE:
        id EQ

Base cost: indid: 0 rows: 1 pages: 1 prefetch: N
        I/O size: 2 cacheid: 0 replace: LRU
Relop bits are: 5
No statistics page--use magicSC
Estimate: indid 1, selectivity 0.100000, rows 1 pages 3 index height 1
Unique clustered index found--return rows 1 pages 2

Cheapest index is index 1, costing 2 pages and
        generating 1 rows per scan, using no data prefetch (size 2)
        on dcacheid 0 with LRU replacement
Search argument selectivity is 1.000000.

*******************************

This situation can happen when a table is truncated with indexes in place and then repopulated with data.

In this case, the distribution page is dropped along with all but the index's root page. When data is reinserted into the table, the intermediate and leaf level of the index are rebuilt. However, the distribution pages is not. In order to build a new distribution page, update statistics must be run.

The steps to take to ensure that index statistics are up to date in this situation is to:

1. Truncate the table

2. Repopulate the table

3. Run update statistics

   Using a Variable, Function, or an Arithmetic Expression in a Batch Query or Stored Procedure

When a variable, function, or an arithmetic expression is used in a SARG or join clause, the optimizer does not know their value at the time it creates the query plan which is created before a value is assigned. In these situations, the optimizer cannot fully use the statistical information on the distribution page to determine a selectivity value for a qualifying index. Rather it must depend on the magic or density SC.

(A variable, function, or an arithmetical expression is used to compare against a column. See "Using a Mathematical Expression in a Query" on page 49 for details and examples.)

Using a Variable in a Batch Query

In the example below, a variable is being declared and its value is selected. The variable is then used in a SARG. The result is that the magicSC is used, and selectivity is based on the operator, in this case a between statement.

	
1> declare @var1 numeric(7,0)
2> go

	
1> declare @var2 numeric(7,0)
2> go

	
1> select @var1 = 100
2> go

	
1> select @var2 = 125
2> go

	
1> select * 
2> from VBT
3> where id between @var1 and @var2
4> go

*******************************
Entering q_score_index() for table 'VBT' (objectid 1936009928, varno = 
0).
The table has 75000 rows and 1787 pages.
Scoring the SEARCH CLAUSE:
        id LE
        id GE

Base cost: indid: 0 rows: 75000 pages: 1787 prefetch: N
        I/O size: 2 cacheid: 0 replace: MRU
Relop bits are: d
SARG is a subbed VAR or expr result or local variable(constat = 4)--
use magicSC or densitySC
Estimate: indid 6, selectivity 0.330000, rows 24750 pages 24888 index 
height 3

Cheapest index is index 6, costing 24888 pages and
        generating 24750 rows per scan, using no data prefetch (size 2)
        on dcacheid 0 with LRU replacement
Search argument selectivity is 0.330000.

*******************************

Variables in a Stored Procedure

Stored procedures that are created to use parameters will not cause the optimizer to use the magicSC or densitySC as long as the parameters are used in SARGs and join clauses as they are passed into the procedure. For example, the procedure below is fully optimizable and magicSC and densitySC are not used:

	
1> create proc tst1 @t1 varchar(6), @t2 varchar(6), @price money
2> as
3> select * from titles
4> where title_id between t1 and t2
5> and price = @price
6> go

However, if you declare a variable within a procedure and select a value into it, and then use the declared variable in the SARGs and/or join clauses, the magicSC or densitySC will be used. The following example shows such a procedure:

	
1> create proc tst2 @t1 varchar(6), @t2 varchar(6), @price money
2> as
3> declare @t3 varchar(6)
4> declare @t4 varchar(6)
5> declare @price2 money
6> select @t3=@t1
7> select @t4=@t2
8> select @price2=@price
9> select * from titles
10> where title_id between @t3 and @t4
11> and @price2 = @price
12> go

Using a Function in a Batch Query

In the example below, a function is used in the SARG. This results in the magicSC being used to determine a selectivity value for the qualifying index. In this case, the column id is unique and there are 11 rows that qualify for the query; the use of the magicSC has led the optimizer to believe that 49500 (150000 * 0.33) rows will need to be read.

	
1> select * from VBT
2> where id between convert(numeric(7,0,100) and
3> convert(numeric(7,0),110)
4> go

*******************************
Entering q_score_index() for table 'VBT'(objectid 1136007078,varno = 0).
The table has 150000 rows and 3261 pages.
Scoring the SEARCH CLAUSE:
        id LE
        id GE

Base cost: indid: 0 rows: 150000 pages: 3261 prefetch: N
        I/O size: 2 cacheid: 0 replace: MRU
Relop bits are: d
SARG is a subbed VAR or expr result or local variable (constat = 40)--
use magicSC or densitySC
Estimate: indid 1, selectivity 0.330000, rows 49500 pages 1079 index 
height 2
Relop bits are: 1825

Cheapest index is index 1, costing 1079 pages and
        generating 49500 rows per scan, using no data prefetch (size 2)
        on dcacheid 0 with LRU replacement
Search argument selectivity is 0.330000.

*******************************

Using a Mathematical Expression in a Query

In the example below, a mathematical expression is used. Again, the magicSC is used to estimate the selectivity of the qualifying index. The column test2 is not unique and the query returns 47 rows. And again, the magicSC has caused the optimizer to believe that many more rows will be returned than actually qualify for the SARG.

	
1> select *
2> from vbt
3> where test2 between 4 * 2 and 5 * 2
4> go

*******************************
Entering q_score_index() for table 'vbt' (objectid 1136007078, varno = 
0).
The table has 150000 rows and 3261 pages.
Scoring the SEARCH CLAUSE:
        test2 LE
        test2 GE

Base cost: indid: 0 rows: 150000 pages: 3261 prefetch: N
        I/O size: 2 cacheid: 0 replace: MRU
Relop bits are: d
SARG is a subbed VAR or expr result or local variable (constat = 40)--
use magicSC or densitySC
Estimate: indid 7, selectivity 0.330000, rows 49500 pages 49895 index 
height 3

Cheapest index is index 7, costing 49895 pages and
        generating 49500 rows per scan, using no data prefetch (size 2)
        on dcacheid 0 with LRU replacement
Search argument selectivity is 0.330000.

*******************************

The Use of IS NOT NULL and Not Equals (!=) Statements

IS NOT NULL and != statements present another situation in which an assumed (hardcoded) magicSC value will be used. The magicSC value for a != or IS NOT NULL search argument is 0.900000 which is the converse of the magicSC value for an equity search argument (=), as in this example:

	where col = X 

or

	where col = NULL

---

Note
In SQL Server versions preceding 11.0.3, the magicSC value for a search argument using a != or IS NOT NULL is 0.330000

---

While IS NOT NULL or != statements can be used in a search argument, an index cannot be used to position the scan of the index. An assumed selectivity must then be applied by the optimizer. In the case of either type of SARG the assumed selectivity will be 0.9000. Due the type of search that is to be done this value is the opposite of the magicSC or hardcoded assumed selectivity for an equity SARG (=).

Consider this example:

	
1> select count(test2)
2> from vbt
3> where test2 != 7500
4> go

*******************************

Entering q_score_index() for table 'vbt' (objectid 80003316, 
varno = 0).

The table has 150000 rows and 3261 pages.

Scoring the SEARCH CLAUSE:

	test2 NE 

Base cost: indid: 0 rows: 150000 pages: 3261 prefetch: N 

	I/O size: 2 cacheid: 0 replace: MRU

Cheapest index is index 2, costing 1190 pages and 	generating 
49500 rows per scan, using no data prefetch (size 2) on icacheid 
0 with LRU replacement

Index covers query.

Search argument selectivity is 0.900000.

*******************************

  STEP 1

        The type of query is SELECT.

        Evaluate Ungrouped COUNT AGGREGATE.

        FROM TABLE

            vbt

        Nested iteration.

        Index : test2_nc

        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.

    STEP 2

        The type of query is SELECT.

Parse and Compile Time 1.

SQL Server cpu time: 100 ms.

 ----------- 

      144023 

Table: vbt  scan count 1,  logical reads: 1200,  physical reads: 
1183

Total writes for this command: 0

	
1> select count(test2)
2> from vbt
3> where test2 is not null
4> go

*******************************

Entering q_score_index() for table 'vbt' (objectid 80003316, 
varno = 0).

The table has 150000 rows and 3261 pages.

Scoring the SEARCH CLAUSE:

	test2 ISNOTNULL 

Base cost: indid: 0 rows: 150000 pages: 3261 prefetch: N 

	I/O size: 2 cacheid: 0 replace: MRU

Cheapest index is index 2, costing 1215 pages and generating 
49500 rows per scan, using no data prefetch (size 2) on icacheid 
0 with MRU replacement

Index covers query.

Search argument selectivity is 0.900000.

*******************************

    STEP 1

        The type of query is SELECT.

        Evaluate Ungrouped COUNT AGGREGATE.

        FROM TABLE

            vbt

        Nested iteration.

        Index : test2_nc

        Ascending scan.

        Positioning at index start.

        Index contains all needed columns. Base table will not
        be read.

        Using I/O Size 2 Kbytes.

        With MRU Buffer Replacement Strategy.

    STEP 2

        The type of query is SELECT.

Parse and Compile Time 0.

SQL Server cpu time: 0 ms.

 ----------- 

      150000 

Table: vbt  scan count 1,  logical reads: 1200,  physical reads: 
1200

Total writes for this command: 0

Effect on Estimated Selectivity of Two Clauses Being Scored at the Same Time

If a query contains two SARGs and at least one of the columns in the SARG is either not a part of an index, or is not the lead column of an index, q_score_index will move the decimal in the selectivity to the left one place for each such column. This is done because each such SARG effectively filters out rows. The optimizer is applying the magicSC again for each SARG. This effectively divides selectivity by 10 for each non-indexed column that is referred to in the SARG. In some cases, this could result in an index being used where it otherwise would not have been if the magicSC selectivity remained unchanged.

In the first example below, there is no qualifying index on the table. Since there are two equity SARGs on non-indexed columns, the decimal in the selectivity value has been moved to the left one place for the second SARG. In both of the examples, the query uses the magicSC to better illustrate the change to the selectivity value of two columns being costed together.

	1> select *
2> from titles
3> where type = "computer"
4> and price = 10
5> go

*******************************
Entering q_score_index() for table 'titles' (objectid 240003886, varno = 
0).
The table has 5000 rows and 620 pages.
Scoring the SEARCH CLAUSE:
        price EQ
        type EQ

Base cost: indid: 0 rows: 5000 pages: 620 prefetch: N
        I/O size: 2 cacheid: 0 replace: LRU

Cheapest index is index 0, costing 620 pages and
        generating 50 rows per scan, using no data prefetch (size 2)
        on dcacheid 0 with LRU replacement
Search argument selectivity is 0.010000.

*******************************

In the example below, there is a non-clustered index on price and no index on type. The equity clause on column type filters the number of rows considered and moves the decimal in the selectivity value one place to the left. The magicSC selectivity value for a between operator is 0.250000, so it now reads 0.025000

	
1> declare @var1 money
2> declare @var2 money
3> select @var1 = 10
4> select @var2 = 15
5> select *
6> from titles
7> where type = "computer"
8> and price between @var1 and @var2
9> go

*******************************
Entering q_score_index() for table 'titles' (objectid 240003886, varno = 
0).
The table has 5000 rows and 620 pages.
Scoring the SEARCH CLAUSE:
        price LE
        price GE
        type EQ

Base cost: indid: 0 rows: 5000 pages: 620 prefetch: N
        I/O size: 2 cacheid: 0 replace: LRU
Relop bits are: d

SARG is a subbed VAR or expr result or local variable (constat = 4)--use 
magicSC or densitySC
Estimate: indid 2, selectivity 0.250000, rows 1650 pages 1668 index 
height 2

Cheapest index is index 2, costing 1668 pages and
        generating 165 rows per scan, using no data prefetch (size 2)
        on dcacheid 0 with LRU replacement
Search argument selectivity is 0.025000.

*******************************

What Does Selectivity of 0.000000 Mean?

Occasionally you may see a SARG selectivity of 0.000000 for a range search. This will happen when the range search is on a column that contains all unique values or values that are very close to being unique.

In some cases, if the range of the search is changed to return even a few more rows, a selectivity other than 0.000000 may be returned.

If the upper and lower boundary of the range search fall on the same "step" of the distribution page, the "net selectivity of interval" will be calculated at 0, or less than 0, and rounded to the float 0.000000. If either or both of the boundaries "cross" over to another step, a selectivity greater than 0 will be calculated. In most cases, this change from a selectivity of 0 will not affect optimization (index choice). However, if the size of the range is changed enough to cause a large change in selectivity, a different access plan may be the result.

Only Partial q_score_index Information Returned

If there is no SARG or join clause in the query, and there is a non- clustered index that covers the query, the optimizer will not print the complete output of traceon 302. This is because the optimizer has to identify the table in order to run a cost estimate for the covering non- clustered index.

This query is asking for all values in the column price to be returned. Since there is a non-clustered index on price, it covers the query (data pages will not have to be read). There is no need to run a full q_score_index to cost this index. The optimizer only needs to basic information about the table to use the covering non-clustered index.

The first line will read "Finishing q_score_index()" rather than "Entering q_score_index()".

	
1> select price
2> from titles
3> go

Finishing q_score_index() for table 'titles' (objectid 240003886).
Cheapest index is index 2, costing 50 pages and
        generating 5000 rows per scan, using no data prefetch (size 2)
        on icacheid 0 with LRU replacement
Index covers query.
Search argument selectivity is 1.000000.

*******************************

Using traceon 310 and 317 to Analyze Join Performance and Processing

What are dbcc traceon 310 and 317?

dbcc traceon 310 and 317 are internal trace facilities used to view the optimizer's cost estimates for performing a join. Their output is similar, but each reports a slightly different aspect of the optimizer's cost estimates.

dbcc traceon 310 prints information on cost estimates of the first "join plan" that the optimizer performs, and then on each subsequently cheaper join plan. These are reported as "new plan"; each new plan is cheaper than the previous one. New plans are only printed by dbcc traceon 310.

dbcc traceon 317 prints the cost estimates for join plans that are rejected by the optimizer as being too expensive. These are reported as "work plan". Like 310, the first join plan is printed (as a work plan) and each subsequently rejected plan is also printed as a work plan. Work plans are only printed by dbcc traceon 317.

The printed costing output of traceon 317 is exactly the same as that of traceon 310 with the above mentioned exception that only rejected join plans will be printed.

When used in conjunction, dbcc traceon(3604,310,317) prints both new plans and work plans. This is particularly useful when you need to see both the join plans that were rejected and those that were not.

---

Note
If you use 310 and 317 in conjunction, a work plan is printed for each new plan.

---

The information that the dbcc commands print can be cryptic, but with a little practice it can be helpful in analyzing join processing and performance.

Permutations and Join Plans

There are two basic elements that traceon 310 output is based on: permutations and plans.

Cost estimates are printed for each efficient plan of a join permutation.

A permutation is one combination (order) of tables from all the possible combinations.

Each of the table orders below is a permutation, and each will be considered by the optimizer. A permutation is a possible join order. For example:

• table 1, table 2, table 3
• table 1, table 3, table 2
• table 2, table 1, table 3
• table 2, table 3, table 1
• table 3, table 1, table 2
• table 3, table 2, table 1
See "Optimizing Joins" in the Performance and Tuning Guide for a detailed discussion on nested iteration and how the order of tables in a join is determined.

A plan is how the data will be accessed, via a table scan or an index.

Each of the examples below is a plan; it is a permutation of the tables along with an access method.

• table 1 using clustered index, table 2 using a table scan, table 3 using a non-clustered index = plan
• table 1 using table scan, table 2 using a non-clustered, table 3 using a non-clustered index = plan
• table 1 using a table scan, table 3 using clustered index, table 2 using clustered index = plan
A permutation can have more than one plan. When the optimizer estimates the cost of a plan, it is estimating the cost of the plan based on the data access method (index or table scan) and the permutation (join order) of tables in the permutation.

The term plan in relation to a join should not be confused with a query plan (see the Performance and Tuning Guide for a discussion of query plans).

Plan, as seen in traceon 310 output, is the combination of join order and data access methods. The plan that is costed by the optimizer for a join can be better thought of as a "join plan".

This document will use the term "join plan" when referring to the combination of join order (permutation) and access method (plan) that is being used to estimate the cost of the join. This is because each set of output printed by traceon 310 is costed for both a permutation and a plan.

It is important to understand the concepts of permutations, join order, and plans when attempting to analyze join performance.

The final plan is the join plan that will be used to perform the join.

In the final plan of the example query that starts on page 59:

• authors will be the outer table of the join, and will be accessed via a table scan
• titleauthor will be the next table in, and will accessed via its clustered index
• titles will be the innermost table, and will be accessed via its clustered index

  varno=0 (authors) indexid=0 ()
  varno=1 (titleauthor) indexid=1 (taind)
  varno=2 (titles) indexid=1 (titleidind)
  

Determining the Join Order Used

There are two ways to determine the join order that has been used to perform a join:
• The first is to look at the output of showplan. Tables are printed in join order, with the outer table first and all tables inward following. The table name follows the from table statement.
• The second way to find the join order is to note the order of tables in the final plan for the join as printed by traceon 310. The outer table is the first table in the join plan with the inner most being the last table.

What You Will See ­ dbcc traceon 310 Output

traceon 310 prints the first join plan it runs a cost estimate on. This is because it is the cheapest up to that point. It then prints all join plans that are cheaper than the previous plans. The costing information on each table is printed in the join order, with the outer table first, and so on to the inner most table. Plans that are more expensive than the previous one(s) are not printed.

The optimizer assigns a number to each table in the from clause, based on its position there (see varno in "Items in the Body of traceon 310/317 Output" on page 69). This number is used to track the table through all the possible plans.

Each plan that is costed is given a "total cost" value. This value is used by the optimizer to compare to the costs of other plans to determine the cheapest. The cheapest total cost is the plan (join order) that the optimizer will use. See "total cost =" on page 67 for more information.

Understanding dbcc traceon 310/317 Output

This section describes the output of traceon 310/317 in the order they appear in the output. Exceptions are discussed at the place they may appear.

It is important to keep in mind that, in most cases, not all of the output of traceon 310 is necessary in normal analysis of join performance.

Following is the sample query and script used to illustrate traceon 310 in this document. It includes the query, output of dbcc traceon(3604,302,310), set showplan on, and set statistics io on.

---

Note
Some material in this section is based on traceon 310 output from other queries. This was necessary in order to illustrate situations not illustrated by the output of the sample query below.

---

	
1> use pubs2
2> go

	
1> set showplan on
2> go

	
1> set statistics io on
2> go

	
1> dbcc traceon(3604,302,310)
2> go

	
1> select au_lname, title
2> from titles t, titleauthor ta, authors a
3> where a.au_id=ta.au_id
4> and t.title_id=ta.title_id
5> and a.au_lname like "L%"
6> and t.type="computer"
7> go

*******************************
Entering q_score_index() for table 'titles' (objectid 208003772, varno = 
0).
The table has 5000 rows and 621 pages.
Scoring the SEARCH CLAUSE:
        type EQ 

Base cost: indid: 0 rows: 5000 pages: 621 prefetch: N 
        I/O size: 2 cacheid: 0 replace: LRU

Cheapest index is index 4, costing 204 pages and 
        generating 500 rows per scan, using no data prefetch (size 2)
        on icacheid 0 with LRU replacement
Index covers query.
Search argument selectivity is 0.100000.

*******************************

*******************************
Entering q_score_index() for table 'authors' (objectid 16003088, varno = 
2).
The table has 5000 rows and 221 pages.
Scoring the SEARCH CLAUSE:
        au_lname LT 
        au_lname GE 

Base cost: indid: 0 rows: 5000 pages: 221 prefetch: N 
        I/O size: 2 cacheid: 0 replace: LRU
Relop bits are: d 
Qualifying stat page; pgno: 22129 steps: 41 
Search value: M
No steps for search value--qualpage for LT search value finds 
value between step K, K + 1, K = 22--use betweenSC 
Scoring SARG interval, lower bound. 
Qualifying stat page; pgno: 22129 steps: 41 
Search value: L
No steps for search value--qualpage for LT search value finds 
value between step K, K + 1, K = 20--use betweenSC 
Net selectivity of interval: 4.897886e-002 
Estimate: indid 2, selectivity 0.048979, rows 245 pages 249 index height 
2 

Cheapest index is index 2, costing 249 pages and 
        generating 245 rows per scan, using no data prefetch (size 2)
        on dcacheid 0 with LRU replacement
Search argument selectivity is 0.048979.

*******************************

*******************************
Entering q_score_index() for table 'titles' (objectid 208003772, varno = 
0).
The table has 5000 rows and 621 pages.
Scoring the JOIN CLAUSE:
        title_id EQ title_id

Base cost: indid: 0 rows: 5000 pages: 621 prefetch: N 
        I/O size: 2 cacheid: 0 replace: LRU
Relop bits are: 4 
Estimate: indid 1, selectivity 0.000198, rows 1 pages 3 index height 2 
Unique clustered index found--return rows 1 pages 3 
Relop bits are: 804 
Estimate: indid 2, selectivity 0.000198, rows 1 pages 4 index height 3 
Relop bits are: 804 
Estimate: indid 4, selectivity 0.000198, rows 1 pages 3 index height 3 

Cheapest index is index 4, costing 3 pages and 
        generating 1 rows per scan, using no data prefetch (size 2)
        on icacheid 0 with LRU replacement
Index covers query.
Join selectivity is 5000.000000.

*******************************

*******************************
Entering q_score_index() for table 'titleauthor' (objectid 176003658, 
varno = 1).
The table has 6250 rows and 126 pages.
Scoring the JOIN CLAUSE:
        title_id EQ title_id

Base cost: indid: 0 rows: 6250 pages: 126 prefetch: N 
        I/O size: 2 cacheid: 0 replace: LRU
Relop bits are: 4 
Estimate: indid 1, selectivity 0.000229, rows 1 pages 2 index height 1 

Cheapest index is index 1, costing 2 pages and 
        generating 1 rows per scan, using no data prefetch (size 2)
        on dcacheid 0 with LRU replacement
Join selectivity is 4369.000001.

*******************************

*******************************
Entering q_score_index() for table 'titleauthor' (objectid 176003658, 
varno = 1).
The table has 6250 rows and 126 pages.
Scoring the JOIN CLAUSE:
        au_id EQ au_id

Base cost: indid: 0 rows: 6250 pages: 126 prefetch: N 
        I/O size: 2 cacheid: 0 replace: LRU

Cheapest index is index 0, costing 126 pages and 
        generating 1 rows per scan, using no data prefetch (size 2)
        on dcacheid 0 with LRU replacement
Join selectivity is 5000.000000.

*******************************

*******************************
Entering q_score_index() for table 'authors' (objectid 16003088, varno = 
2).
The table has 5000 rows and 221 pages.
Scoring the JOIN CLAUSE:
        au_id EQ au_id

Base cost: indid: 0 rows: 5000 pages: 221 prefetch: N 
        I/O size: 2 cacheid: 0 replace: LRU
Relop bits are: 4 
Estimate: indid 1, selectivity 0.000198, rows 1 pages 3 index height 2 
Unique clustered index found--return rows 1 pages 3 

Cheapest index is index 1, costing 3 pages and 
        generating 1 rows per scan, using no data prefetch (size 2)
        on dcacheid 0 with LRU replacement
Join selectivity is 5000.000000.

*******************************

QUERY IS CONNECTED

 0 - 1 - 2 -
NEW PLAN (total cost = 493033):

varno=0 (titles) indexid=4 (tid_title_type_price)
path=0x2075c168 pathtype=sclause method=NESTED ITERATION
outerrows=1 rows=500 joinsel=1.000000 cpages=204 prefetch=N iosize=2 
replace=LRU lp=204 pp=204 corder=1

varno=1 (titleauthor) indexid=0 ()
path=0x2075c3d0 pathtype=sclause method=NESTED ITERATION
outerrows=500 rows=715 joinsel=4369.000001 cpages=126 prefetch=N 
iosize=2 replace=LRU lp=63000 pp=126 corder=2

varno=2 (authors) indexid=2 (lname_fname)
path=0x2075c800 pathtype=sclause method=NESTED ITERATION
outerrows=715 rows=35 joinsel=5000.000000 cpages=249 prefetch=N iosize=2 
replace=LRU lp=178101 pp=249 corder=2

NEW PLAN (total cost = 452474):

varno=0 (titles) indexid=4 (tid_title_type_price)
path=0x2075c168 pathtype=sclause method=NESTED ITERATION
outerrows=1 rows=500 joinsel=1.000000 cpages=204 prefetch=N iosize=2 
replace=LRU lp=204 pp=204 corder=1

varno=1 (titleauthor) indexid=0 ()
path=0x2075c3d0 pathtype=sclause method=NESTED ITERATION
outerrows=500 rows=715 joinsel=4369.000001 cpages=126 prefetch=N 
iosize=2 replace=LRU lp=63000 pp=126 corder=2

varno=2 (authors) indexid=0 ()
path=0x2075c800 pathtype=sclause method=NESTED ITERATION
outerrows=715 rows=35 joinsel=5000.000000 cpages=221 prefetch=N iosize=2 
replace=LRU lp=158073 pp=221 corder=1

NEW PLAN (total cost = 140618):

varno=0 (titles) indexid=4 (tid_title_type_price)
path=0x2075c168 pathtype=sclause method=NESTED ITERATION
outerrows=1 rows=500 joinsel=1.000000 cpages=204 prefetch=N iosize=2 
replace=LRU lp=204 pp=204 corder=1

varno=1 (titleauthor) indexid=0 ()
path=0x2075c3d0 pathtype=sclause method=NESTED ITERATION
outerrows=500 rows=715 joinsel=4369.000001 cpages=126 prefetch=N 
iosize=2 replace=LRU lp=63000 pp=126 corder=2

varno=2 (authors) indexid=1 (au_id)
path=0x2075ca68 pathtype=join method=NESTED ITERATION
outerrows=715 rows=35 joinsel=5000.000000 cpages=3 prefetch=N iosize=2 
replace=LRU lp=2145 pp=221 corder=1
jnvar=1 refcost=0 refpages=0 reftotpages=0 ordercol[0]=1  ordercol[1]=1

NEW PLAN (total cost = 16618):

varno=0 (titles) indexid=4 (tid_title_type_price)
path=0x2075c168 pathtype=sclause method=NESTED ITERATION
outerrows=1 rows=500 joinsel=1.000000 cpages=204 prefetch=N iosize=2 
replace=LRU lp=204 pp=204 corder=1

varno=1 (titleauthor) indexid=1 (t_id)
path=0x2075c638 pathtype=join method=NESTED ITERATION
outerrows=500 rows=715 joinsel=4369.000001 cpages=2 prefetch=N 
iosize=2 replace=LRU lp=1000 pp=126 corder=2
jnvar=0 refcost=0 refpages=0 reftotpages=0 ordercol[0]=2  ordercol[1]=1

varno=2 (authors) indexid=1 (au_id)
path=0x2075ca68 pathtype=join method=NESTED ITERATION
outerrows=715 rows=35 joinsel=5000.000000 cpages=3 prefetch=N iosize=2 
replace=LRU lp=2145 pp=221 corder=1
jnvar=1 refcost=0 refpages=0 reftotpages=0 ordercol[0]=1  ordercol[1]=1

 0 - 2 - 1 -
 1 - 0 - 2 -
 1 - 2 - 0 -
 2 - 0 - 1 -
 2 - 1 - 0 -

TOTAL # PERMUTATIONS: 6

TOTAL # PLANS CONSIDERED: 66

CACHE USED BY THIS PLAN:

        CacheID = 0:     (2K) 551     (4K) 0     (8K) 0     (16K) 0

FINAL PLAN (total cost = 16618):

varno=0 (titles) indexid=4 (tid_title_type_price)
path=0x2075c168 pathtype=sclause method=NESTED ITERATION
outerrows=1 rows=500 joinsel=1.000000 cpages=204 prefetch=N iosize=2 
replace=LRU lp=204 pp=204 corder=1

varno=1 (titleauthor) indexid=1 (t_id)
path=0x2075c638 pathtype=join method=NESTED ITERATION
outerrows=500 rows=715 joinsel=4369.000001 cpages=2 prefetch=N 
iosize=2 replace=LRU lp=1000 pp=126 corder=2
jnvar=0 refcost=0 refpages=0 reftotpages=0 ordercol[0]=2  ordercol[1]=1

varno=2 (authors) indexid=1 (au_id)
path=0x2075ca68 pathtype=join method=NESTED ITERATION
outerrows=715 rows=35 joinsel=5000.000000 cpages=3 prefetch=N iosize=2 
replace=LRU lp=2145 pp=221 corder=1
jnvar=1 refcost=0 refpages=0 reftotpages=0 ordercol[0]=1  ordercol[1]=1

QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1
        The type of query is SELECT.

        FROM TABLE
            titles
        Nested iteration.
        Index : tid_title_type_price
        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
            titleauthor
        Nested iteration.
        Using Clustered Index.
        Index : t_id
        Ascending scan.
        Positioning by key.
        Keys are:
            title_id
        Using I/O Size 2 Kbytes.
        With LRU Buffer Replacement Strategy.

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

(results deleted)

Table: titles  scan count 1,  logical reads: 193,  physical reads: 0

Table: titleauthor  scan count 582,logical reads: 1180,physical reads: 
Table: authors  scan count 737,  logical reads: 2240,  physical reads: 0
Total writes for this command: 0

Execution Time 11.
SQL Server cpu time: 1100 ms.  SQL Server elapsed time: 1283 ms.

(30 rows affected)

QUERY IS CONNECTED

This message indicates that all the tables in the from clause can be joined to one another without causing a Cartesian product. It is rare to see "QUERY IS NOT CONNECTED". Should this appear in the output, check to ensure that no Cartesian product would result from the query. The join will not be connected even if the cost of the Cartesian product is minimal. If the Cartesian product is small and the performance of the join is acceptable, a "QUERY IS NOT CONNECTED" message can be ignored.

Whether or not the query is connected is important in analysis.

At times there will be permutations that are ignored by the optimizer. In these cases, no cost estimates will be printed for join plans that are based on the given permutation(s). Refer to "Using traceon 317 in the Analysis of Join Problems" on page 77 for information about how to print rejected join plans.

The Permutation List

This example shows a listing of permutations (table orders) printed by traceon 310. The estimated cost of join plans based on these permutations, except for the last permutation of the list, are more expensive than join plan costs from preceding permutations. Since all are more expensive than the previous one, none will be printed by traceon 310. In this example, only the permutation 2-1-0 will have new plan costs for some or all of the possible join plans printed.

---

Note
When working with traceon 317, the permutation list is not printed. Since traceon 317 prints all join plans, none will be passed over and thus there is no need for the permutation list.

---

0 - 2 - 1 -
1 - 0 - 2 
1 - 2 - 0 -
2 - 0 - 1 -
2 - 1 - 0 -

IGNORING THIS PERMUTATION

One reason this occurs is that the optimizer has decided to disregard a join order that will result in a Cartesian product, or is likely to be far more expensive than plans in previous permutations. Note that this applies to a single join order (permutation). If the entire join, as written will result in a Cartesian product, the "QUERY NOT CONNECTED" message will be printed, and no join orders will be ignored.

The message "IGNORING THIS PERMUTATION" will only occur when certain situations are present. The most common are:

• there are more than five tables in the query
• forceplan is not turned on
• The optimizer has already estimated the cost of one plan
• The query is connected and the "outerrows" is more than 25 (see "outerrows=" on page 71 for details)
While this saves processing time, there is a possibility that efficient join plans are being ignored. In general, this message can be ignored. However, if you feel that the join order (permutation) that is being ignored by the optimizer is the best, you may want to try to force it with forceplan.

0 - 1 - 2 -

0 - 1 - 2 - 
NEW PLAN (total cost = 493033):

This is the permutation (table order) that the following set of join plans is based on (see the description of varno in "Items in the Body of traceon 310/317 Output" on page 69 for the meaning of these numbers). Once the optimizer has estimated that join plan costs based on this permutation are cheaper than those previously printed, it will proceed to print its cost estimates of join plans using this permutation. If there are join plans that are cheaper, the permutation will be printed, followed by the join plans. The permutation will be listed above the first printed join plan using the permutation. If there are no join plans that are cheaper than previously printed ones, the permutation will be printed and the optimizer will move to the next permutation.

The cost of all printed join plans are then compared, and the cheapest is chosen to perform the join. The chosen join plan will be printed as the final plan. See "FINAL PLAN (total cost = 16618):" on page 76 for information about the final plan.

NEW PLAN

"NEW PLAN" indicates that this join plan is cheaper than the previously printed new plan, or it is the first join plan that has been costed. traceon 310 only prints "NEW PLAN", thus it only shows you the first join plan that was costed and each subsequently cheaper join plan. traceon 317 prints output for join plans that are more expensive than new plans. These are printed as "WORK PLAN". See "Using traceon 317 in the Analysis of Join Problems" on page 77 for more information.

total cost =

This is the cost the optimizer assigns to a given plan. Total cost is the optimizer's estimated cost of performing the join based on the plan. This value represents the time (in milliseconds) the optimizer believes the join will take. The fact that this is a time measurement should not be taken as absolute. It is based on assumptions that are built into the optimizer. These assumptions are that a logical read (a read of a page from cache, this is also known as CPU cost) will take 2 milliseconds and a physical read (the read of a page from disk, this is also known as I/O cost) will take 18 milliseconds. The actual time to perform each of these I/O types varies between machines and operating systems. Think of the value of "total cost" as a base line value for determining which is the most efficient join plan.

The number of logical reads estimated by the optimizer is based on the access method and the size of cache. In this case, the optimizer is estimating how many pages are likely to be in cache. Physical reads are estimated based on the number of logical reads and the cache size. The optimizer is estimating how many pages will not be available in cache and will need to be read from disk. See "lp=" on page 73 and "pp=" on page 73 for more information.

The formula for costing a join plan is run for each table in the join plan. These are then added together to get the total cost. The formula used for each table is:

(2 ms * total logical reads lp) + (18 ms * total physical reads pp)

            2 * lp + 18 * pp = total cost

In this example, the formula will be applied to the new plan which is chosen by the optimizer as the final plan also:

NEW PLAN (total cost = 16618):

varno=0 (titles) indexid=4 (tid_title_type_price)
path=0x2075c168 pathtype=sclause method=NESTED ITERATION
outerrows=1 rows=500 joinsel=1.000000 cpages=204 prefetch=N iosize=2 
replace=LRU lp=204 pp=204 corder=1

varno=1 (titleauthor) indexid=1 (t_id)
path=0x2075c638 pathtype=join method=NESTED ITERATION
outerrows=500 rows=715 joinsel=4369.000001 cpages=2 prefetch=N 
iosize=2 replace=LRU lp=1000 pp=126 corder=2
jnvar=0 refcost=0 refpages=0 reftotpages=0 ordercol[0]=2  ordercol[1]=1

varno=2 (authors) indexid=1 (au_id)
path=0x2075ca68 pathtype=join method=NESTED ITERATION
outerrows=715 rows=35 joinsel=5000.000000 cpages=3 prefetch=N iosize=2 
replace=LRU lp=2145 pp=221 corder=1
jnvar=1 refcost=0 refpages=0 reftotpages=0 ordercol[0]=1  ordercol[1]=1

(2*204)+(18*204)    = 4080

(2*1000)+(18*126)  = 4268

(2*2145)+(18*221)  = 8268

Total cost = 16616

---

Note
This is two less than the new plan cost. From time to time, a cost estimate is off by a few values. It is important to note that the resulting cost is extremely close to the formula and the complexity of algorithm may be a factor in the slight difference.

---

Items in the Body of traceon 310/317 Output

varno=0 (titles) indexid=4 (tid_title_type_price)

An understanding of varno and indexid is very important for analysis.

varno =

This indicates the table's position in the from clause. The first table in the from clause is varno 0, the second is varno 1, and so on. In SQL Server 10.0.2 and later releases, varno 16 and higher are worktables used by the optimizer in the join and/or query. These worktables are not counted in the total number of tables in the join. There is a maximum of 16 tables per join. In pre-10.0.2 SQL Servers, such worktables are included with the other tables in the join and are counted toward the total.

In pre-System 11 SQL Servers, only the varno is given. You will need to check the from clause and take note of the table names. In System 11 and above, the table name is included (see example above).

indexid=

This is the index ID or table scan (indid 0) that is being used in the costing of the plan. If the index ID is greater than 0, the index ID number is printed. In System 11 and above, the index name is also printed. If it is 0 (table scan), () is printed. Since no index name is printed in pre-System 11 SQL Servers, the name will have to be determined by checking sp_help, or in the case of a table with many non-clustered indexes, a select from sysindexes.

To get the indid and name of all indexes on a table, run the following query:

	
1> select indid,sysindexes.name
2> from sysindexes, sysobjects
3> where sysindexes.id=sysobjects.id
4> and sysobjects.name="table_name"
5> go

To get the name of an index using its indid, run the following query:

	
1> select indid,sysindexes.name
2> from sysindexes, sysobjects
3> where sysindexes.id=sysobjects.id
4> and sysobjects.name="table_name"
5> and sysindexes.indid=index_id
6> go

The combination of varno (table position) and indexid are very useful in quickly identifying the join order and access method being costed in the plan. This combination can also be called a "join plan".

path=0x2075c168 pathtype=sclause method=NESTED ITERATION

path= This is the in-memory location of the sclause, join, or orstruct. It is of no value in the analysis of join performance or problems.

pathtype=

This output indicates the type of operation that is being performed and costed. It will be one of three path types:

• sclause (search clause)
• join
• orstruct (the OR strategy is being used)
This is useful when there is a need to identify the type of operation being performed.

When you are using 310 and/or 317 to compare two different join plans, make sure to check that pathtype, along with the order of varno and indexid match. It is easy to see a situation in which varno order and indexid match but the pathtype is different. If this is the case, the two join plans are not comparable.

A final plan will usually show an sclause for the outer table and a join for the inner table(s). This occurs with or without a where clause in the query. However, it is possible for other plans in the costing to show sclause on all tables in the join.

method=

This indicates the method used for the join in this plan costing. In the majority of situations, this will read "NESTED ITERATION". If reformatting is to be costed in the plan, "REFORMATTING" will be shown. If the OR strategy is used, then "OR OPTIMIZATION" will be shown. Method is useful when there is a need to determine how the join is being performed.

outerrows=1 rows=500 joinsel=1.000000 cpages=204 prefetch=N iosize=2 
replace=LRU

outerrows=

---

Note
"current table" is the table that is costed in the block of traceon 310/317 output you are currently reading.

---

This indicates the estimated number of rows, based on the query, from the outer table that will join to the current table to rows in the current table. This is the number of iterations (passes through) of the current table to be performed.

If you think of the outermost table of the join as being on the left, then these are rows from the table to the left of the current table that will satisfy the join.

Outerrows will always be 1 for the outermost table. Once again, this is an estimate. You can see the number of iterations actually performed on a table by referring to the value of scan count for the table in the output of set statistics io on. In the example below from the sample output, the scan count for titles is 1, and the outerrows value is also 1. For table titleauthor, the scan count is 582 and the outerrows value is 500. For table authors, the scan count is 737 and the outerrows value is 715. These outerrows values are very close to the actual scan counts for the two innermost tables.

Table: titles  scan count 1,logical reads: 193,physical reads: 0
Table: titleauthor  scan count 582,logical reads: 1180,physical reads: 0
Table: authors  scan count 737,logical reads: 2240,physical reads: 0

rows=

This is the number of rows from the current table that is estimated to satisfy the join.

joinsel=

This is the estimated join selectivity for an index or table scan of the current table. This corresponds to the join selectivity that was determined by q_score_index and output by traceon 302. In pre-System 11, this number is an integer; in System 11 and above, it is displayed as a float. The added granularity allows for more accurate selectivity. Join selectivity is important in analysis. If the selectivity of an index is too low, it may make the current join plan too expensive. For a join, the higher the selectivity value the better. For more details on the importance of selectivity, see "Understanding Selectivity" on page 40 of this document and "Optimizing Joins" in the Performance and Tuning Guide.

cpages=

This is the number of pages (index and data) that the optimizer estimates it will need to read from the current table. This is important since the number of pages to be read can influence which join plan is seen by the optimizer as the cheapest.

prefetch=

This is the prefetch strategy being used in the costing. A value of "N" means no prefetch being used; "S" indicates that prefetch strategy is being used. This is important only when SQL Server is configured for large I/O and it is expected that large I/O will be used. There may be situations in which the use of prefetch is not appropriate.

---

Note
This feature is only available in SQL Server 11.0 and later releases.

---

iosize=

This is the block I/O size the optimizer is using to estimate this table's cost. It will be 2K, 4K, 8K, or 16K (double the size on Stratus). This is important when I/O sizes larger than the default of 2K are expected to be used.

---

Note
This feature is only available in SQL Server 11.0 and later releases.

---

replace=

This indicates the Replacement Strategy that is being applied to this plan's costing. It will be LRU (least recently used) or MRU (most recently used). See "Overview of Cache Strategies" in the Performance and Tuning Guide for more information.

---

Note
This feature is only available in SQL Server 11.0 and later releases.

---

lp=734 pp=105 corder=1
jnvar=0 refcost=0 refpages=0 reftotpages=0 ordercol[0]=1  ordercol[1]=1

The number of logical and physical reads that the optimizer expects to perform for a plan is very important to analysis. Since all costs the optimizer estimates are based on I/O (logical or physical), this estimate can determine whether a join plan is efficient.

lp=

This is the number of expected logical reads from the table. This value is derived by multiplying the number of estimated page reads (cpages) by the estimated number of passed through or iterations of the table (outerrows).

pp=

This is the number of estimated physical reads from the table. For the outermost table, this represents the total estimated number of pages read in a single scan (via an index or table scan) of the table and will equal the value for lp=. For inner tables, this represents the total number of physical page reads estimated for all iterations of the table. (A physical read represents a read from disk.)

---

Note
lp and pp often differ from the logical and physical reads shown in set statistics io output. In general, the lp and pp estimates usually differ from the actual reads. In the example below, the "FINAL PLAN" and statistics io output from the sample query are compared.

---

FINAL PLAN (total cost = 16618):

varno=0 (titles) indexid=4 (tid_title_type_price)
path=0x2075c168 pathtype=sclause method=NESTED ITERATION
outerrows=1 rows=500 joinsel=1.000000 cpages=204 prefetch=N iosize=2 
replace=LRU lp=204 pp=204 corder=1

varno=1 (titleauthor) indexid=1 (t_id)
path=0x2075c638 pathtype=join method=NESTED ITERATION
outerrows=500 rows=715 joinsel=4369.000001 cpages=2 prefetch=N 
iosize=2 replace=LRU lp=1000 pp=126 corder=2
jnvar=0 refcost=0 refpages=0 reftotpages=0 ordercol[0]=2  ordercol[1]=1

varno=2 (authors) indexid=1 (au_id)
path=0x2075ca68 pathtype=join method=NESTED ITERATION
outerrows=715 rows=35 joinsel=5000.000000 cpages=3 prefetch=N iosize=2 
replace=LRU lp=2145 pp=221 corder=1
jnvar=1 refcost=0 refpages=0 reftotpages=0 ordercol[0]=1  ordercol[1]=1

Table: titles  scan count 1,  logical reads: 193,  physical reads: 0
Table: titleauthor scan count 582, logical reads:1180, physical reads: 0
Table: authors  scan count 737,  logical reads: 2240,  physical reads: 0

The final plan cost for table titles is estimating 204 logical reads and 204 physical reads. The statistics io output shows that there were actually 193 logical reads and 0 physical reads for the table. This shows that the estimates for this table were actually higher than the resulting reads. However, the estimates for tables titleauthor and authors are lower (lp=, outerrows=) than the actual reads performed, with the exception of pp=.

corder=

This is the column ID of the leading column of an index. If there is one column in the index, this is its column ID. If the index is composite, this is the column ID of the first (leading) column of the index. This number is used similarly as varno, but to keep track of columns instead of tables. This values is stored as colid in the syscolumns system table.

---

Note
The follow six types of output are not always seen in traceon 310. These values are displayed when an index is used in the join plan or the reformatting strategy is costed.

---

jnvar=

This indicates the varno of the table that an inner table is being joined from via an index. "jnvar" provides little useful information for analysis.

refcost=

This is the cost of reformatting. If it is chosen, it is given in milliseconds.

refpages=

This is the estimated number of pages from the reformatted table (dynamic index) that will need to be read if reformatting is used. It is similar to cpages. See the Performance and Tuning Guide for more information about reformatting.

reftotpages=

This is the estimated number of pages that will be in the reformatted table (dynamic index) created in tempdb.

ordercol[0]=

This is the column ID of the joining column from the inner table. In the example below, it is the first column specified in the join, T1.a. The [0] indicates it is the first column join.

	select * from T1, T2 where T1.a = T2.b

ordercol[1]=

This is the column ID of the second column specified, T2.b. The [1] indicates it is the second column in the join.

After the optimizer has completed its cost estimates (the body of traceon 310 output), traceon 310 will print the following items:

TOTAL # PERMUTATIONS:

This is the number of possible join orders. It does not take plans into account. In the case of the example, there are two tables and only two possible join orders. If the example joined three tables, the number of permutations would be six.

TOTAL # PLANS CONSIDERED:

This is the number of plans that have been considered by the optimizer. The optimizer runs a cost estimate on all combinations of indexes and table scans that will qualify for the join. Indexes that are not useful for the join are not costed in a plan. This number will be larger than the number of costings printed by 310. This is because those plans that were rejected by the optimizer as too expensive will not be counted here.

In SQL Server 11.x, the following will be printed (in System 10 and earlier releases, the output goes directly to final plan).

CACHE USED BY THIS PLAN:

    CacheID = 0:    (2K) 551    (4K) 0    (8K) 0    (16K) 0

This indicates from which cache buffers are being taken, what size buffer is being used, and how many are being used. In this case, 551 2K buffers were used.

FINAL PLAN (total cost = 16618):

This is the join plan that will be used for the join. It is the join plan found to be the cheapest by the optimizer, and appears at the end of the traceon 310 output.

In the majority of cases, the cost of the final plan is different than the time shown by set statistics time on. This is a reflection of the fact that the time it takes to perform a physical and logical read are built into SQL Server.

Note that the CPU and elapsed time are much faster than the total cost. Also notice that the logical reads of authors is exactly that of the estimate of lp in the final plan. The actual logical reads of titleauthor are very close to the lp estimates in the final plan. For table titles, the logical reads are much higher than the final plan estimate. This is an illustration of the fact that the optimizer makes estimates rather than returning final values.

Table: titles  scan count 1,  logical reads: 193,  physical reads: 0
Table: titleauthor  scan count 582,  logical reads: 1180,  physical 
reads: 0
Table: authors  scan count 737,  logical reads: 2240,  physical reads: 0
Total writes for this command: 0

Execution Time 11.
SQL Server cpu time: 1100 ms.  SQL Server elapsed time: 1283 ms.
(30 rows affected)

Table: titles  scan count 250,  logical reads: 779,  physical reads: 0
Table: titleauthor scan count 250, logical reads: 757, physical reads: 0
Table: authors  scan count 1,  logical reads: 221,  physical reads: 0
Total writes for this command: 0

Execution Time 6.
SQL Server cpu time: 600 ms.  SQL Server elapsed time: 610 ms.
(30 rows affected)

Joins and Forcing a Join Order, an Index, or a Join Plan

As with SARGs, the use of the forces with a join can be very helpful in analysis. The forces can be used to test a join plan for efficiency. Also, if you believe that a join plan other than the one chosen by the optimizer may perform better, you can test this by forcing the use of a particular index, the join order, or the join plan.

Forcing an Index

To force the join order (order of tables in the join), use the set forceplan on extension of the set command. See the SQL Server Reference Manual for information about set syntax. set forceplan on forces the optimizer to join tables in the order they appear in the from clause of the query. See the Performance and Tuning Guide for further discussion of join optimization.

To force a join plan, use set forceplan on in conjunction with specifying index. You can specify an index or table scan to be used on each table in the join. You can also tell the optimizer what order in which to join the tables of the join by changing their order in the from clause.

Before forcing a join order, an index, or a join plan, let the optimizer chose a query plan on its own.

Collect the output of set showplan on, set statistics io on, and dbcc traceon(3604,302,310) for the run of the join. Then gather the same output for a run of the join using a force or forces. This will give you information to make a useful comparison of the performance of the forced and non-force join plan.

Using traceon 317 in the Analysis of Join Problems

"Items in the Body of traceon 310/317 Output" on page 69 gives full descriptions of what each value of the output means. traceon 317 returns the same set of output. However, traceon 317 only prints cost estimates on join plans that are found to be too expensive by the optimizer. This contrasts with traceon 310 which prints only those join plans which were found to be cheaper than the previous cheapest join plan. Both traceon 310 and 317 print the cost estimate for the first join plan. This is because up to this point it is the cheapest and only join plan.

The primary use of traceon 317 in analysis is to verify that the optimizer gave a higher cost to a join plan that is found to be more efficient than the chosen join plan. If an efficient join plan is known, usually through the use of forceindex and/or forceplan, look for it in the 317 output. traceon 317 can also be used to check the cost given to a join plan that is expected to be efficient, but is not.

In many cases, traceon 317 output is very large as contrasted with traceon 310 output. For example, the sample query resulted in close to 29 pages of traceon 317 output, whereas it resulted in slightly less than 3 pages of traceon 302 output. For this reason, it is advisable to use traceon 317 only when there is a question whether an efficient join plan is being chosen or if you are curious about how the optimizer costed a join plan you believe should be more efficient.

What is a Work Plan?

As mentioned earlier, traceon 310 prints the first join plan costed and all subsequently cheaper join plans. These join plans are printed as the new plan. traceon 317 prints the first join plan costed and all plans that are subsequently found to be more expensive than previous cheaper join plans. These join plans are printed as the work plan. Below is a small sample of what you will see when using traceon 310 and 317 together.

This sample illustrates the relation of traceon 310 and 317 in the output. The first plan shown is a new plan costing 140618. The next join plan printed is a work plan costing 369033. The work plan was rejected because it was more expensive than the previous new plan.

NEW PLAN (total cost = 140618):

varno=0 (titles) indexid=4 (tid_title_type_price)
path=0x2075c168 pathtype=sclause method=NESTED ITERATION
outerrows=1 rows=500 joinsel=1.000000 cpages=204 prefetch=N iosize=2 
replace=LRU lp=204 pp=204 corder=1

varno=1 (titleauthor) indexid=0 ()
path=0x2075c3d0 pathtype=sclause method=NESTED ITERATION
outerrows=500 rows=715 joinsel=4369.000001 cpages=126 prefetch=N 
iosize=2 replace=LRU lp=63000 pp=126 corder=2

varno=2 (authors) indexid=1 (au_id)
path=0x2075ca68 pathtype=join method=NESTED ITERATION
outerrows=715 rows=35 joinsel=5000.000000 cpages=3 prefetch=N iosize=2 
replace=LRU lp=2145 pp=221 corder=1
jnvar=1 refcost=0 refpages=0 reftotpages=0 ordercol[0]=1  ordercol[1]=1

WORK PLAN (total cost = 369033, order by cost = 0)

varno=0 (titles) indexid=4 (tid_title_type_price)
path=0x2075c168 pathtype=sclause method=NESTED ITERATION
outerrows=1 rows=500 joinsel=1.000000 cpages=204 prefetch=N iosize=2 
replace=LRU lp=204 pp=204 corder=1

varno=1 (titleauthor) indexid=1 (t_id)
path=0x2075c638 pathtype=join method=NESTED ITERATION
outerrows=500 rows=715 joinsel=4369.000001 cpages=2 prefetch=N 
iosize=2 replace=LRU lp=1000 pp=126 corder=2
jnvar=0 refcost=0 refpages=0 reftotpages=0 ordercol[0]=2  ordercol[1]=1

varno=2 (authors) indexid=2 (lname_fname)
path=0x2075c800 pathtype=sclause method=NESTED ITERATION
outerrows=715 rows=35 joinsel=5000.000000 cpages=249 prefetch=N iosize=2 
replace=LRU lp=178101 pp=249 corder=2

Differences Between traceon 310 and 317 Output in Pre-System 11 and System 11 SQL Servers

In pre-System 11 SQL Servers, there is no difference in the body of the output of the two traceon commands, except as discussed in the section describing traceon 310 output. In SQL Server 11.0 and later releases, an extra piece of information has been added. This information relates to any extra cost added to the join plan by an order by clause. This is the result of the addition of the sort avert function in the optimizer. This function allows the optimizer to take the cost of ordering data into account when estimating the cost of the query.

In the first example, there is either no data ordering to be done or no extra cost to the join plan of performing ordering:

WORK PLAN (total cost = 369033, order by cost = 0)

In this example, there is an extra cost of 32 for performing data ordering:

WORK PLAN (total cost = 19538, order by cost = 32)

See the Performance and Tuning Guide for more information about how to avoid the extra costs of ordering data.

What To Do If an Inefficient Join Plan is Being Chosen Over an Efficient One

traceon 317 is particularly useful when the optimizer is choosing to use a join plan that is known not to be the most efficient. As mentioned earlier, this is usually discovered as a result of using a "force". If this is the case, following are a few methods and tips on how to perform an analysis.

Using the procedures below will aid you in finding the work plan that corresponds to an efficient final plan. Or you can use these procedures to find the work plan for a join plan you are interested in.

The Sample Final Plan

This is the sample final plan used to get the necessary information to find its corresponding work plan. Each of the procedures below uses a part of this output to help you find the work plan.

FINAL PLAN (total cost = 16618):

varno=0 (titles) indexid=4 (tid_title_type_price)
path=0x2075c168 pathtype=sclause method=NESTED ITERATION
outerrows=1 rows=500 joinsel=1.000000 cpages=204 prefetch=N iosize=2 
replace=LRU lp=204 pp=204 corder=1

varno=1 (titleauthor) indexid=1 (t_id)
path=0x2075c638 pathtype=join method=NESTED ITERATION
outerrows=500 rows=715 joinsel=4369.000001 cpages=2 prefetch=N 
iosize=2 replace=LRU lp=1000 pp=126 corder=2
jnvar=0 refcost=0 refpages=0 reftotpages=0 ordercol[0]=2  ordercol[1]=1

varno=2 (authors) indexid=1 (au_id)
path=0x2075ca68 pathtype=join method=NESTED ITERATION
outerrows=715 rows=35 joinsel=5000.000000 cpages=3 prefetch=N iosize=2 
replace=LRU lp=2145 pp=221 corder=1
jnvar=1 refcost=0 refpages=0 reftotpages=0 ordercol[0]=1  ordercol[1]=1

---

Note
The included traceon 310/317 sample output is not for a "problem" join. It comes from the output of the sample query.

---

Determine the Join Plan of the Efficiently Optimized Join

If you have a situation in which a forced index (specify index), a forced join order (forceplan), or a forced join plan (combination of both specifying and index and using forceplan) is more efficient than one chosen by the optimizer, you can use traceon 317 to find the costing information of the efficient join plan. The join plan will be in the form of a work plan.

Also, if you have a join plan in mind that you think should be efficient, or are curious about how efficient a join plan may be, you can use these techniques to find its work plan.

To get the costing information of the efficient join plan, follow the steps below.

Find the Join Order of the Efficient Join Plan

From the final plan and showplan output of the run in which forces are used, determine the join order of the efficient plan. In pre-System 11, the easiest way to determine the join order is by reading the output of showplan. Tables are referred to in showplan by their join order with the outermost table first and so on inward. Is SQL Server 11.0 and later releases, the table name is printed in the body of the traceon 310/317 output.

It is often easier to use the varno to keep track of join order. When looking through the often very large traceon 317 output, it can be easier to identify join order by the varno than by the table name.

The example below shows the order of tables in the join:

varno=0 (titles)
varno=1 (titleauthor)
varno=2 (authors)

Once you have determined the join order, note it, either by the order of varno or by the order of table name.

Find the Table Access Method Used in the Efficient Plan

Next determine the access method used for each table. Note if a table scan (indexid 0) or an index is used as the access to the data. Note the access used either by indexid or index name. In pre-System 11 SQL Servers, you will need to determine the index name from its indexid. In System 11 and above, the index name is included in the output.

The example below shows the order of tables in the join and the method used to access the data:

varno=0 (titles) indexid=4 (tid_title_type_price)
varno=1 (titleauthor) indexid=1 (t_id)
varno=2 (authors) indexid=1 (au_id)

At this point, you have the order of tables in the join and whether an index or table scan will be used to access the table. If an index is used, you know the index's indexid and its name.

In the example, tables titleauthor and authors will be accessed using their clustered index, and table titles will be accessed via non- clustered index tid_title_type_price with indexid 4.

What is the Pathtype Being Used?

Pathtype indicates the type of operation that is being used in this join plan. It will be one of three path types:
• sclause (search clause)
• join
• orstruct (the OR strategy is being used)
It is important to note the pathtype. While the join order and table access methods may be the same, if pathtype is different, the cost of the join plan can be affected. In other words, if the pathtype is not the same in the final plan and the work plan you are reading, the work plan is not the same as the final plan.

varno=0 (titles) indexid=4 (tid_title_type_price)
path=0x2075c168 pathtype=sclause method=NESTED ITERATION

varno=1 (titleauthor) indexid=1 (t_id)
path=0x2075c638 pathtype=join method=NESTED ITERATION

varno=2 (authors) indexid=1 (au_id)
path=0x2075ca68 pathtype=join method=NESTED ITERATION

In the example above, the pathtype for tables titleauthor and authors is join, for table titles the pathtype is search clause (sclause). Again, if the pathtypes do not match those of the final plan, continue reading through the work plans for the one that matches the final plan.

What is the Method Used in the Costing of the Join?

As stated earlier in this document, nested iteration will be the most common method. Make sure to check that the method for each table in the final plan matches the method for the corresponding table in the work plan. If join order, table access, and pathtype are the same, but the methods are different, the two join plans cannot be compared. In the sample output, the method for each of the tables is "NESTED ITERATION".

varno=0 (titles) indexid=4 (tid_title_type_price)
path=0x2075c168 pathtype=sclause method=NESTED ITERATION

varno=1 (titleauthor) indexid=1 (t_id)
path=0x2075c638 pathtype=join method=NESTED ITERATION

varno=2 (authors) indexid=1 (au_id)
path=0x2075ca68 pathtype=join method=NESTED ITERATION

A Quick Practice Test Investigation

For a practice investigation, look at the new plan and work plan in "What is a Work Plan?" on page 78. These plans are sequential in the output. Their permutation is the same. Use the information in the last few sections to identify the differences between these two join plans.

Answer: The permutation (order of tables in the join) is the same; the access method is different as is the pathtype.

Reading Through the Work Plans for the Efficient Join Plan

Once you have established the join order, the table access method, the pathtype, and the method, you are ready to locate the corresponding work plan in the traceon 317 output from the query where a forceplan and/or specified index was used.

To save time, first locate the permutation (join order) you are looking for in the traceon 317 output. In the case of the example output, permutation 0 - 1 - 2 is used. This is the easiest to locate because it is the first permutation costed. If the final plan has a permutation other than 0 - 1 - 2, you will need to search for the beginning of the permutation. In many cases, the permutation of the efficient join plan is not the same as that of the inefficient join plan and it may not be the first permutation considered by the optimizer.

The easiest way to locate the permutation you are looking for is to read through the traceon 317 output, noting the order of the varnos. You will soon find the area of the output you are looking for. Once you have located the permutation, follow it up until you find its beginning. The permutation will begin with the ordered list of varnos, in the case of the sample output list will appear as 0 - 1 - 2. It is easy to do a search on this string; remember that there is a space between each character.

Compare the Final Plan Costs for Both the Efficient and Inefficient Join

Once you have located the join plan for the efficiently optimized join in which a force, or combination of forces is used, note the total cost of the join plan from its final plan. Compare this cost to the cost of the final plan that was chosen by the optimizer. In some cases, you may find that the final cost of the efficient plan has been given a higher cost than that of the one chosen by the optimizer. Or there may be cases in which the efficient join plan is not costed by the optimizer, thus no work plan is printed. In either case, the optimizer is choosing the wrong join plan. If this is the case, and you cannot determine why this is happening, call Sybase Technical Support.

Other Information You Might See in traceon 310/317 Output

There are a few situations that will result in other then the usual information being returned by traceon 310 and 317. This section describes these situations.

Onerow Join Processing

NEW PLAN FOR ONEROW (total cost = ):

Under certain circumstances, the optimizer will use this type of new plan when estimating the cost of a join. When "NEW PLAN FOR ONEROW" is used, the optimizer can avoid full join processing on one or more tables of the join.

This form of new plan will be reported by traceon 310 when a qualifying unique index can be used in the join, thus guaranteeing that a single row will be returned from the table. "NEW PLAN FOR ONEROW" can only be used when a qualifying unique index is present. An index that may have fully unique values, but is not created as a unique index, will not be considered for onerow join processing. The onerow table is treated as a single row table by the optimizer when costing the join. The onerow table will then be placed as the outermost table of the join order. The unique index will be used for the join and no further processing is done by the optimizer. A table that will be used as a onerow table will not participate in any further permutations of the join.

The query must contain an equity SARG on a column that is the lead column of the unique index. In addition, the table involved in the SARG must also be a join table. This will effectively restrict the number of possible rows returned by the join to one. Onerow tables are treated as constants and thus avoid nested iteration processing.

In situations where other tables of the join cannot qualify for onerow, nested iteration processing will continue as usual on them. With the onerow table as the outermost table, it only has to be read once for the single row.

When multiple tables in the join have qualifying unique indexes on the join and SARG columns, each can be treated as a onerow table, thus avoiding the cost of performing nested iteration. In such cases, the join order will be the same as the order of tables in the from clause of the query.

In the examples below, the query joins VBT and VBT4 on column id. Column id contains fully unique values in both tables. The following query was run in both examples:

	
1> select VBT.id,VBT4.test,VBT.num,VBT4.date
2> from VBT,VBT4
3> where VBT.id=VBT4.id
4> and VBT4.id=1000
5> go

---

Note
In early versions of System 11, the final plan cost when onerow is used may be inaccurate. This is an output issue and does not effect the actual final plan cost.

---

In the first example, VBT has a unique clustered index on id and VBT4 has a non-clustered index on id (not created as unique). traceon 310/317 shows that the optimizer will use VBT as a onerow table, accessing it via its clustered index, ind_clu. It will be the outer table of the join order, and no further cost estimates are run on the table. The traceon output also shows that the optimizer must run cost estimates on table VBT4. It is then placed as the innermost table of the join order, and accessed via non-clustered index id_nc_nu. Note that there are three permutations considered.

Since there is only one possible join order, and cost estimates have to be run on VBT4, there in only one permutation.

QUERY IS CONNECTED

NEW PLAN FOR ONEROW (total cost = 60):

varno=0 (VBT) indexid=1 (id_clu)
path=0x207bb120 pathtype=sclause method=NESTED ITERATION
outerrows=1 rows=1 joinsel=0.000000 cpages=3 prefetch=N iosize=2 
replace=LRU lp=3 pp=3 corder=8

1 -
WORK PLAN (total cost = 80, order by cost = 0)

varno=1 (VBT4) indexid=6 (id_nc_nu)
path=0x207bb388 pathtype=sclause method=NESTED ITERATION
outerrows=1 rows=1 joinsel=75000.000000 cpages=4 prefetch=N iosize=2 
replace=LRU lp=4 pp=4 corder=8

NEW PLAN (total cost = 80):

varno=1 (VBT4) indexid=6 (id_nc_nu)
path=0x207bb388 pathtype=sclause method=NESTED ITERATION
outerrows=1 rows=1 joinsel=75000.000000 cpages=4 prefetch=N iosize=2 
replace=LRU lp=4 pp=4 corder=8

WORK PLAN (total cost = 36600, order by cost = 0)

varno=1 (VBT4) indexid=0 ()
path=0x207bb388 pathtype=sclause method=NESTED ITERATION
outerrows=1 rows=1 joinsel=75000.000000 cpages=1830 prefetch=N 
iosize=2 replace=MRU lp=1830 pp=1830 corder=0

WORK PLAN (total cost = 80, order by cost = 0)

varno=1 (VBT4) indexid=6 (id_nc_nu)
path=0x207bb800 pathtype=join method=NESTED ITERATION
outerrows=1 rows=1 joinsel=75000.000000 cpages=4 prefetch=N iosize=2 
replace=LRU lp=4 pp=4 corder=8
jnvar=0 refcost=0 refpages=0 reftotpages=0 ordercol[0]=8  ordercol[1]=8

TOTAL # PERMUTATIONS: 1  

---

Note
Only one join order is possible; VBT is the outer table because it is a onerow table.

---

TOTAL # PLANS CONSIDERED: 3

CACHE USED BY THIS PLAN:
        CacheID = 0:    (2K) 7  (4K) 0  (8K) 0  (16K) 0

FINAL PLAN (total cost = 80):

varno=0 (VBT) indexid=1 (id_clu)
path=0x207bb120 pathtype=sclause method=NESTED ITERATION
outerrows=1 rows=1 joinsel=0.000000 cpages=3 prefetch=N iosize=2 
replace=LRU lp=3 pp=3 corder=8

varno=1 (VBT4) indexid=6 (id_nc_nu)
path=0x207bb388 pathtype=sclause method=NESTED ITERATION
outerrows=1 rows=1 joinsel=75000.000000 cpages=4 prefetch=N iosize=2 
replace=LRU lp=4 pp=4 corder=8

QUERY PLAN FOR STATEMENT 1 (at line 1).

        STEP 1
            The type of query is SELECT.

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

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

id         test        num         dat
---------- ----------- ----------- --------------------------
      1000          10       22386        Jun 26 1995 12:00AM
Table: VBT  scan count 1,  logical reads: 3,  physical reads: 0
Table: VBT4  scan count 1,  logical reads: 4,  physical reads: 2
Total writes for this command: 0

(1 row affected)

In this second example, there is now a unique non-clustered index, id, on table VBT4. This is the same column that a non-unique non- clustered index was on in the first example. With the unique index in place, VBT4 now qualifies as a onerow table.

In some early versions of System 11, the final plan cost may be reported incorrectly. However, this is an issue with output; the internal costing is accurate.

QUERY IS CONNECTED

NEW PLAN FOR ONEROW (total cost = 60):

varno=0 (VBT) indexid=1 (id_clu)
path=0x206f7120 pathtype=sclause method=NESTED ITERATION
outerrows=1 rows=1 joinsel=0.000000 cpages=3 prefetch=N iosize=2 
replace=LRU lp=3 pp=3 corder=8

NEW PLAN FOR ONEROW (total cost = 80):

varno=1 (VBT4) indexid=6 (in_nc_u)
path=0x206f7388 pathtype=sclause method=NESTED ITERATION
outerrows=1 rows=1 joinsel=0.000000 cpages=4 prefetch=N iosize=2 
replace=LRU lp=4 pp=4 corder=8

TOTAL # PERMUTATIONS: 0

TOTAL # PLANS CONSIDERED: 0

CACHE USED BY THIS PLAN:

        CacheID = 0:    (2K) 7  (4K) 0  (8K) 0  (16K) 0

FINAL PLAN (total cost = 80):

varno=0 (VBT) indexid=1 (id_clu)
path=0x206f7120 pathtype=sclause method=NESTED ITERATION
outerrows=1 rows=1 joinsel=0.000000 cpages=3 prefetch=N iosize=2 
replace=LRU lp=3 pp=3 corder=8

varno=1 (VBT4) indexid=6 (in_nc_u)
path=0x206f7388 pathtype=sclause method=NESTED ITERATION
outerrows=1 rows=1 joinsel=0.000000 cpages=4 prefetch=N iosize=2 
replace=LRU lp=4 pp=4 corder=8

QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1
        The type of query is SELECT.
        FROM TABLE
            VBT
        Nested iteration.
        Using Clustered Index.
        Index : id_clu
        Ascending scan.
        Positioning by key.
        Keys are:
            id
        Using I/O Size 2 Kbytes.
        With LRU Buffer Replacement Strategy.

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

id         test        num         date
---------- ----------- ----------- --------------------------
      1000          10       22386        Jun 26 1995 12:00AM
Table: VBT  scan count 1,  logical reads: 3,  physical reads: 0
Table: VBT4  scan count 1,  logical reads: 4,  physical reads: 2
Total writes for this command: 0)

The Effects of Sorting and Reformatting on the Cost of Plans

Sorting

In this example, an order by clause has been added to the sample query:

	order by au_lname

In this case, a worktable is created to perform the necessary sort. The worktable is included in the cost estimates for the join because of the additional cost of reading the worktable. An additional new plan and final plan are printed by traceon 310 for the worktable. Because the extra plans are costed by the optimizer, traceon 310 will increase the value for "TOTAL # PLANS CONSIDERED:".

TOTAL # PERMUTATIONS: 6

TOTAL # PLANS CONSIDERED: 87

CACHE USED BY THIS PLAN:

        CacheID = 0:    (2K) 793    (4K) 0    (8K) 0    (16K) 0

FINAL PLAN (total cost = 17138):

varno=0 (authors) indexid=0 ()
path=0x2234e968 pathtype=sclause method=NESTED ITERATION
outerrows=1 rows=245 joinsel=1.000000 cpages=221 prefetch=N iosize=2 
replace=LRU
lp=221 pp=221 corder=1

varno=1 (titleauthor) indexid=1 (taind)
path=0x2234f8e0 pathtype=join method=NESTED ITERATION
outerrows=245 rows=234 joinsel=6553.500002 cpages=3 prefetch=N iosize=2
replace=LRU lp=734 pp=105 corder=1
jnvar=0 refcost=0 refpages=0 reftotpages=0 ordercol[0]=1  ordercol[1]=1

varno=2 (titles) indexid=1 (titleidind)
path=0x2234fb58 pathtype=join method=NESTED ITERATION
outerrows=234 rows=27 joinsel=5000.000000 cpages=2 prefetch=N iosize=2
replace=LRU lp=467 pp=467 corder=1
jnvar=1 refcost=0 refpages=0 reftotpages=0 ordercol[0]=1  ordercol[1]=2

QUERY IS CONNECTED

 16 -
NEW PLAN (total cost = 245):

varno=16 (Worktable1) indexid=0 ()
path=0x22351000 pathtype=sclause method=NESTED ITERATION
outerrows=1 rows=100 joinsel=1.000000 cpages=10 prefetch=S iosize=2 
replace=MRU
lp=10 pp=10 corder=1
TOTAL # PERMUTATIONS: 1

TOTAL # PLANS CONSIDERED: 1

CACHE USED BY THIS PLAN:

        CacheID = 0:    (2K) 1    (4K) 0    (8K) 0    (16K) 0

FINAL PLAN (total cost = 245):

varno=16 (Worktable1) indexid=0 ()
path=0x22351000 pathtype=sclause method=NESTED ITERATION
outerrows=1 rows=100 joinsel=1.000000 cpages=10 prefetch=S iosize=2 
replace=MRU
lp=10 pp=10 corder=1

Reformatting

Reformatting is the process of creating a temporary clustered index on a join column of an inner table, and using that index to perform the join. This temporary clustered index is call a dynamic index and is built on a worktable. You will see the term "using DYNAMIC INDEX" in showplan output when reformatting is used. If there is no index, or no useful index for the join, the optimizer will consider reformatting. The primary cost of reformatting is the I/O necessary to create the worktable from the qualifying rows and building a clustered index on it. For detailed information on reformatting, see the Performance and Tuning Guide.

In this example, reformatting was considered by the optimizer. The cost of the reformatting is added into the total cost of the join plan. This is done by performing the basic formula and then adding the "refcost" value. As this shows, reformatting is the majority of the cost of the join plan. Any sorting that is required will also be added to the cost of the join.

The basic formula used for costing the reformatting is:

• The cost of scanning the original table based on the SARG(s)
• Plus the cost of inserting all qualifying rows into the worktable
• Plus the cost of sorting the worktable and building the clustered index (dynamic index)
• Plus the added cost of an overhead designed to discourage reformatting unless absolutely necessary (the overhead amounts to 10,000 milliseconds)

  FINAL PLAN (total cost = 69324):
  

  varno=0 (titleauthor) indexid=0 ()
  path=0xebda51f0 pathtype=sclause method=NESTED ITERATION
  outerrows=1 rows=6250 joinsel=1.000000 cpages=106 prefetch=N iosize=2
  replace=LRU lp=106 pp=106 corder=0
  

  varno=1 (authors) indexid=0 ()
  path=0xebda7198 pathtype=join method=REFORMATTING
  

  outerrows=6250 rows=6250 joinsel=5000.000000 cpages=223 prefetch=N 
  iosize=2
  replace=LRU lp=12500 pp=28 corder=0
  jnvar=0 refcost=41700 refpages=2 reftotpages=28 ordercol[0]=1  
  ordercol[1]=1
  

New and Miscellaneous Optimizer Items

A Change to Non-clustered Index Reads in SQL Server 11.x

Due to a change in an internal function of SQL Server 11.x to facilitate "dirty reads" (isolation level 0), there are times when the use of a non-covering non-clustered index may be more efficient than in previous versions of Sybase SQL Server.

In SQL Server versions prior to 11.x it was expected that the cost of using a non-clustered index would be equal to the number of rows returned plus the overhead of reading through the height of the index. In SQL Server 11.x there are times when the use of a non- clustered index is cheaper in terms of logical reads (I/O). The more qualifying rows that are on the data page, or pages (data row clustering), the more this effect is seen. See the examples below.

Keep in mind that the non-clustered index must qualify for the query.

This behavior is different than the use of a non-clustered index which covers the query. See the Sybase SQL Server Performance and Tuning Guide for a discussion of covering non-clustered indexes.

There are limitations however. The first is that the optimizer is not fully aware of this change, this may change in future versions of the optimizer. The result is that in some cases if the number of qualifying rows is large enough, the optimizer may decided to perform a table scan when the non-clustered index can be shown to be more efficient by forcing it. You will need to test against your data.

What The Change Did

The basic mechanics of this is that once the buffer of a non-clustered leaf page has been "grabbed" and the first qualifying row located, it then checks to see if the next qualifying row is located on the same data page. If it is on the same data page it too will be read in the same logical I/O. This continues for all qualifying rows.

Thus, the more rows on the same data page the fewer logical I/Os are required to retrieve all qualifying rows. The buffer is not released until the whole page is checked for qualifying rows. Thus multiple rows can be read in a single logical I/O. Also, the more rows on the data page the more pronounced this behavior is.

Data Page Clustering

Data page clustering describes how physically close qualifying rows are on a data page. The higher the degree of data page clustering the closer qualifying rows are together on a page, and vice versa for a low degree of data page clustering.

The presence of a clustered index will increase the degree of data page clustering.

Examples

This section contains examples that illustrate data page clustering behavior.
• A High Degree of Data Page Clustering
• SQL Server 11.x
• SQL Server 10.x
• A Low Degree Of Data Clustering
• Queries That Return No Rows

A High Degree of Data Page Clustering

In table VBT on both versions of SQL Server (11.x and 10.x), the qualifying rows in the lead column (test2) of the non-clustered index have a very high degree of data page clustering, they are in physical order on the data pages.

Table VBT has its clustered index on an identity column, column "id". The values of the non-clustered index lead column, column "test2", were inserted to be placed on the data pages in physical order, based on the clustered index column:

	
1> update table VBT
2> set test2 = 7500
3> where id between value_1  and value_2
4> go

The identity column is sequential on this table, it begins with 1 and ends with 150,000.

The following is a sample set of rows to illustrate this. Remember, the clustered index is on column id:

	
1> select id
2> from VBT
3> where test2 = 7500
4> go

     		id        test2

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

		737        7500
     		738        7500
     		739        7500
     		740        7500
     		741        7500
     		742        7500
    		743        7500
    		744        7500
     		745        7500
     		746        7500

In SQL Server 11.x, the higher the data page clustering of a non- clustered index columns(s) the more pronounced the behavior becomes. In the example below the cost of selecting 3617 rows is 116 logical reads. Here the SQL Server is now able to find multiple qualifying rows on a single page and read them all is a single logical I/O.

In System 10 however the SQL Server performs one logical read for each qualifying row plus overhead. This results in far more reads then in System 11. This occurs with any degree of data page clustering.

SQL Server 11.x

In the first example the query is on table VBT, as mentioned earlier the values of column test2 have a high degree of data page clustering. This is the type of situation that makes this behavior most pronounced in SQL Server 11.x.

	
1> select id
2> from VBT
3> where test2=7500
4> go

*******************************

Entering q_score_index() for table 'VBT' (objectid 288004057, 
varno = 0).

The table has 150000 rows and 3261 pages.

Scoring the SEARCH CLAUSE:

	test2 EQ 

Base cost: indid: 0 rows: 150000 pages: 3261 prefetch: N 

	I/O size: 2 cacheid: 0 replace: MRU

Relop bits are: 4 

Qualifying stat page; pgno: 380 steps: 181 

Search value: 7500

Match found on statistics page 

equal to several rows in middle of page--use midseveralSC 

Estimate: indid 2, selectivity 0.022099, rows 3315 pages 3343 
index height 3 

Cheapest index is index 2, costing 3343 pages and 

	generating 3315 rows per scan, using no data prefetch (size 
2)

	on dcacheid 0 with MRU replacement

Search argument selectivity is 0.022099.

*******************************

QUERY IS CONNECTED

 0 -

WORK PLAN (total cost = 44324, order by cost = 0)

varno=0 (VBT) indexid=2 (VBT_test2_nc)

path=0x2068d8d8 pathtype=sclause method=NESTED ITERATION

outerrows=1 rows=3315 joinsel=1.000000 cpages=3343 prefetch=N 
iosize=2 replace=MRU lp=3343 pp=2091 corder=9

NEW PLAN (total cost = 44324):

varno=0 (VBT) indexid=2 (VBT_test2_nc)

path=0x2068d8d8 pathtype=sclause method=NESTED ITERATION

outerrows=1 rows=3315 joinsel=1.000000 cpages=3343 prefetch=N 
iosize=2 replace=MRU lp=3343 pp=2091 corder=9

WORK PLAN (total cost = 65220, order by cost = 0)

varno=0 (VBT) indexid=0 ()

path=0x2068d8d8 pathtype=sclause method=NESTED ITERATION

outerrows=1 rows=3315 joinsel=1.000000 cpages=3261 prefetch=N 
iosize=2 replace=MRU lp=3261 pp=3261 corder=8

TOTAL # PERMUTATIONS: 1

TOTAL # PLANS CONSIDERED: 2

CACHE USED BY THIS PLAN:

	CacheID = 0:	(2K) 1	(4K) 0	(8K) 0	(16K) 0

FINAL PLAN (total cost = 44324):

varno=0 (VBT) indexid=2 (VBT_test2_nc)

path=0x2068d8d8 pathtype=sclause method=NESTED ITERATION

outerrows=1 rows=3315 joinsel=1.000000 cpages=3343 prefetch=N 
iosize=2 replace=MRU lp=3343 pp=2091 corder=9

QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1

        The type of query is SELECT.

        FROM TABLE

            VBT

        Nested iteration.

        Index : VBT_test2_nc

        Ascending scan.

        Positioning by key.

        Keys are:

            test2

        Using I/O Size 2 Kbytes.

        With LRU Buffer Replacement Strategy.

<3617 rows removed>

Parse and Compile Time 0.

SQL Server cpu time: 0 ms.

Table: VBT  scan count 1,  logical reads: 116,  physical reads: 0

Total writes for this command: 0

Execution Time 4.

SQL Server cpu time: 400 ms.  SQL Server elapsed time: 870 ms.

(3617 rows affected)

SQL Server 10.x

In SQL Server 10.x the values of column test2 of the non-clustered index also have a high degree of data page clustering. However, the mechanism that allows multiple rows to be read in a single logical I/O is not present.

The result is that is cost 3649 logical reads to perform the query using the non-clustered index, this is expected in pre-11.x SQL Servers. See the Sybase SQL Server Performance and Tuning Guide for more information on how non-clustered indexes are structured.

	
1> select id
2> from VBT
3> where test2=7500
4> go

*******************************

Entering q_score_index() for table 'VBT' (objectid 32003145).

The table has 150000 rows and 3261 pages.

Scoring the SEARCH CLAUSE:

	test2 EQ 

Base cost: indid: 0 rows: 150000 pages: 3261 

Relop bits are: 4 

Qualifying stat page; pgno: 1521 steps: 181 

Search value: 7500

Match found on statistics page 

equal to several rows in middle of page--use midseveralSC 

Estimate: indid 2, selectivity 2.209945e-002, rows 3314 pages 
3344 

Cheapest index is index 2, costing 3344 pages and generating 
3314 rows per scan.

Search argument selectivity is 0.022099.

*******************************

QUERY IS CONNECTED

 0 -

WORK PLAN (total cost = 43444):

varno=0 indexid=2 path=0x206fb804 pathtype=sclause method=NESTED 
ITERATION

outerrows=1 rows=3314 joinsel=1 cpages=3344 lp=3344 pp=2042 
corder=9

NEW PLAN (total cost = 43444):

varno=0 indexid=2 path=0x206fb804 pathtype=sclause method=NESTED 
ITERATION

outerrows=1 rows=3314 joinsel=1 cpages=3344 lp=3344 pp=2042 
corder=9

WORK PLAN (total cost = 65220):

varno=0 indexid=1 path=0x206fb804 pathtype=sclause method=NESTED 
ITERATION

outerrows=1 rows=3314 joinsel=1 cpages=3261 lp=3261 pp=3261 
corder=8

TOTAL # PERMUTATIONS: 1

TOTAL # PLANS CONSIDERED: 2

FINAL PLAN (total cost = 43444):

varno=0 indexid=2 path=0x206fb804 pathtype=sclause method=NESTED 
ITERATION

outerrows=1 rows=3314 joinsel=1 cpages=3344 lp=3344 pp=2042 
corder=9

STEP 1

The type of query is SELECT.

FROM TABLE

VBT

Nested iteration

Index : VBT_test2_nc

Parse and Compile Time 0.

SQL Server cpu time: 0 ms.

Table: VBT  scan count 1,  logical reads: 3649,  physical reads: 
0

Total writes for this command: 0

<<Rows Removed>> 

Execution Time 0.

SQL Server cpu time: 0 ms.  SQL Server elapsed time: 5376 ms.

(3617 rows affected)

A Low Degree Of Data Clustering

In this example the same query is run on table VBT3 where the values of column "test2" have a low degree of data page clustering. The values in the column test2 where set as follows:

	
1> update VBT3
2> set test2 = 7500
3> where date = "a_date"
4> go

---

Note
Four dates were used, dates are randomly dispersed on the table

---

Since the values in column "date" are randomly placed on the table the update of "test2" to the value 7500 is also random.

The following is a sample set of rows to illustrate this.

	
1> select id
2> from VBT3
3> where test2 = 7500
4> go

		id         test2

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

        376        7500
        411        7500
      26142        7500
      37928        7500
      45266        7500
      45273        7500
      45300        7500
      45302        7500
      51898        7500
      51913        7500

In the following example the cost of the query is considerably higher than when there is a high degree of data page clustering. However, the non-clustered index is still used and the resulting logical reads are less then with SQL Server 10.x with any degree of data page clustering. In SQL Server 10.x the clustered index is chosen when the degree of data page clustering is high and low, resulting in the same cost (only one example is presented here).

	
1> select id
2> from VBT3
3> where test2=7500
4> go

*******************************

Entering q_score_index() for table 'VBT3' (objectid 544004969, 
varno = 0).

The table has 150000 rows and 3261 pages.

Scoring the SEARCH CLAUSE:

	test2 EQ 

Base cost: indid: 0 rows: 150000 pages: 3261 prefetch: N 

	I/O size: 2 cacheid: 0 replace: MRU

Relop bits are: 4 

Qualifying stat page; pgno: 10222 steps: 181 

Search value: 7500

Match found on statistics page 

equal to several rows in middle of page--use midseveralSC 

Estimate: indid 2, selectivity 0.022099, rows 3315 pages 3343 
index height 3 

Cheapest index is index 2, costing 3343 pages and 

	generating 3315 rows per scan, using no data prefetch (size 
2)

	on dcacheid 0 with MRU replacement

Search argument selectivity is 0.022099.

*******************************

QUERY IS CONNECTED

 0 -

WORK PLAN (total cost = 44324, order by cost = 0)

varno=0 (VBT3) indexid=2 (VBT3_test2_nc)

path=0x206938d8 pathtype=sclause method=NESTED ITERATION

outerrows=1 rows=3315 joinsel=1.000000 cpages=3343 prefetch=N 
iosize=2 replace=MRU lp=3343 pp=2091 corder=9

NEW PLAN (total cost = 44324):

varno=0 (VBT3) indexid=2 (VBT3_test2_nc)

path=0x206938d8 pathtype=sclause method=NESTED ITERATION

outerrows=1 rows=3315 joinsel=1.000000 cpages=3343 prefetch=N 
iosize=2 replace=MRU lp=3343 pp=2091 corder=9

WORK PLAN (total cost = 65220, order by cost = 0)

varno=0 (VBT3) indexid=0 ()

path=0x206938d8 pathtype=sclause method=NESTED ITERATION

outerrows=1 rows=3315 joinsel=1.000000 cpages=3261 prefetch=N 
iosize=2 replace=MRU lp=3261 pp=3261 corder=8

TOTAL # PERMUTATIONS: 1

TOTAL # PLANS CONSIDERED: 2

CACHE USED BY THIS PLAN:

	CacheID = 0:	(2K) 1	(4K) 0	(8K) 0	(16K) 0

FINAL PLAN (total cost = 44324):

varno=0 (VBT3) indexid=2 (VBT3_test2_nc)

path=0x206938d8 pathtype=sclause method=NESTED ITERATION

outerrows=1 rows=3315 joinsel=1.000000 cpages=3343 prefetch=N 
iosize=2 replace=MRU lp=3343 pp=2091 corder=9

QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1

        The type of query is SELECT.

        FROM TABLE

            VBT3

        Nested iteration.

        Index : VBT3_test2_nc

        Ascending scan.

        Positioning by key.

        Keys are:

            test2

        Using I/O Size 2 Kbytes.

        With LRU Buffer Replacement Strategy.

Parse and Compile Time 0.

SQL Server cpu time: 0 ms.

Table: VBT3  scan count 1,  logical reads: 1704,  physical 
reads: 0

Total writes for this command: 0

Execution Time 4.

SQL Server cpu time: 400 ms.  SQL Server elapsed time: 953 ms.

(3617 rows affected)

Queries That Return No Rows

Below are two examples of the behavior when no rows are returned. There are two rows in the table with the value 60920 for column "test", neither of which have a value of 7500 for column "test2".

Table "test2" has a high degree of data page clustering as earlier. In this case the query on table VBT requires only 116 logical reads. The same query on table VBT3, where values in "test2" have a low degree of data page clustering performs the query with 1704 logical reads. This is a further illustration of how a high degree of data page clustering may help make non-clustered index access more efficient.

Example 1: A High Degree of Data Page Clustering

	
1> select id
2> from VBT
3> where test2=7500
4> and num = 60920
5> go

*******************************

Entering q_score_index() for table 'VBT' (objectid 288004057, 
varno = 0).

The table has 150000 rows and 3261 pages.

Scoring the SEARCH CLAUSE:

	num EQ 

	test2 EQ 

Base cost: indid: 0 rows: 150000 pages: 3261 prefetch: N 

	I/O size: 2 cacheid: 0 replace: MRU

Relop bits are: 4 

Qualifying stat page; pgno: 380 steps: 181 

Search value: 7500

Match found on statistics page 

equal to several rows in middle of page--use midseveralSC 

Estimate: indid 2, selectivity 0.022099, rows 3315 pages 3343 
index height 3 

Cheapest index is index 2, costing 3343 pages and 

	generating 331 rows per scan, using no data prefetch (size 
2)

	on dcacheid 0 with MRU replacement

Search argument selectivity is 0.002210.

*******************************

QUERY IS CONNECTED

 0 -

WORK PLAN (total cost = 44324, order by cost = 0)

varno=0 (VBT) indexid=2 (VBT_test2_nc)

path=0x206908d8 pathtype=sclause method=NESTED ITERATION

outerrows=1 rows=331 joinsel=1.000000 cpages=3343 prefetch=N 
iosize=2 replace=MRU lp=3343 pp=2091 corder=9

NEW PLAN (total cost = 44324):

varno=0 (VBT) indexid=2 (VBT_test2_nc)

path=0x206908d8 pathtype=sclause method=NESTED ITERATION

outerrows=1 rows=331 joinsel=1.000000 cpages=3343 prefetch=N 
iosize=2 replace=MRU lp=3343 pp=2091 corder=9

WORK PLAN (total cost = 65220, order by cost = 0)

varno=0 (VBT) indexid=0 ()

path=0x206908d8 pathtype=sclause method=NESTED ITERATION

outerrows=1 rows=331 joinsel=1.000000 cpages=3261 prefetch=N 
iosize=2 replace=MRU lp=3261 pp=3261 corder=8

TOTAL # PERMUTATIONS: 1

TOTAL # PLANS CONSIDERED: 2

CACHE USED BY THIS PLAN:

	CacheID = 0:	(2K) 1	(4K) 0	(8K) 0	(16K) 0

FINAL PLAN (total cost = 44324):

varno=0 (VBT) indexid=2 (VBT_test2_nc)

path=0x206908d8 pathtype=sclause method=NESTED ITERATION

outerrows=1 rows=331 joinsel=1.000000 cpages=3343 prefetch=N 
iosize=2 replace=MRU lp=3343 pp=2091 corder=9

QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1

        The type of query is SELECT.

        FROM TABLE

            VBT

        Nested iteration.

        Index : VBT_test2_nc

        Ascending scan.

        Positioning by key.

        Keys are:

            test2

        Using I/O Size 2 Kbytes.

        With LRU Buffer Replacement Strategy.

Parse and Compile Time 0.

SQL Server cpu time: 0 ms.

Table: VBT  scan count 1,  logical reads: 116,  physical reads: 0

Total writes for this command: 0

Execution Time 0.

SQL Server cpu time: 0 ms.  SQL Server elapsed time: 20 ms.

 id         

 ---------- 

(0 rows affected)

Example 2: A low degree of data page clustering

1> select id

2> from VBT3

3> where test2=7500

4> and num = 60920

5> go

*******************************

Entering q_score_index() for table 'VBT3' (objectid 544004969, 
varno = 0).

The table has 150000 rows and 3261 pages.

Scoring the SEARCH CLAUSE:

	num EQ 

	test2 EQ 

Base cost: indid: 0 rows: 150000 pages: 3261 prefetch: N 

	I/O size: 2 cacheid: 0 replace: MRU

Relop bits are: 4 

Qualifying stat page; pgno: 10222 steps: 181 

Search value: 7500

Match found on statistics page 

equal to several rows in middle of page--use midseveralSC 

Estimate: indid 2, selectivity 0.022099, rows 3315 pages 3343 
index height 3 

Cheapest index is index 2, costing 3343 pages and 

	generating 331 rows per scan, using no data prefetch (size 
2)

	on dcacheid 0 with MRU replacement

Search argument selectivity is 0.002210.

*******************************

QUERY IS CONNECTED

 0 -

WORK PLAN (total cost = 44324, order by cost = 0)

varno=0 (VBT3) indexid=2 (VBT3_test2_nc)

path=0x206938d8 pathtype=sclause method=NESTED ITERATION

outerrows=1 rows=331 joinsel=1.000000 cpages=3343 prefetch=N 
iosize=2 replace=MRU lp=3343 pp=2091 corder=9

NEW PLAN (total cost = 44324):

varno=0 (VBT3) indexid=2 (VBT3_test2_nc)

path=0x206938d8 pathtype=sclause method=NESTED ITERATION

outerrows=1 rows=331 joinsel=1.000000 cpages=3343 prefetch=N 
iosize=2 replace=MRU lp=3343 pp=2091 corder=9

WORK PLAN (total cost = 65220, order by cost = 0)

varno=0 (VBT3) indexid=0 ()

path=0x206938d8 pathtype=sclause method=NESTED ITERATION

outerrows=1 rows=331 joinsel=1.000000 cpages=3261 prefetch=N 
iosize=2 replace=MRU lp=3261 pp=3261 corder=8

TOTAL # PERMUTATIONS: 1

TOTAL # PLANS CONSIDERED: 2

CACHE USED BY THIS PLAN:

	CacheID = 0:	(2K) 1	(4K) 0	(8K) 0	(16K) 0

FINAL PLAN (total cost = 44324):

varno=0 (VBT3) indexid=2 (VBT3_test2_nc)

path=0x206938d8 pathtype=sclause method=NESTED ITERATION

outerrows=1 rows=331 joinsel=1.000000 cpages=3343 prefetch=N 
iosize=2 replace=MRU lp=3343 pp=2091 corder=9

QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1

        The type of query is SELECT.

        FROM TABLE

            VBT3

        Nested iteration.

        Index : VBT3_test2_nc

        Ascending scan.

        Positioning by key.

        Keys are:

            test2

        Using I/O Size 2 Kbytes.

        With LRU Buffer Replacement Strategy.

Parse and Compile Time 0.

SQL Server cpu time: 0 ms.

Table: VBT3  scan count 1,  logical reads: 1704,  physical 
reads: 0

Total writes for this command: 0

Execution Time 1.

SQL Server cpu time: 100 ms.  SQL Server elapsed time: 90 ms.

 id

 ---------- 

(0 rows affected)

Common Optimizer Problems and Causes

The following are some, but not all, common issues that arise in optimizer investigations.

Optimizer is Choosing the "Wrong" Index

In many situations, it may appear that the optimizer has chosen the wrong index for a given query. In some cases, the index chosen may in fact be the cheapest qualifying index. In other cases, the wrong index may have been chosen; this could be do to a number of reasons (see previous sections of this writeup for analysis tools).

It may be possible to resolve such a situation without in-depth analysis, by specifying an index.

Do the following to test the efficiency of the index that is thought to be the best for the query:

	
SET SHOWPLAN ON
SET STATISTICS IO ON

Run the query without the use of forceindex.

Run it again with the use of forceindex.

Compare the output of statistics io. If the query run without forceindex is cheaper (fewer logical reads) than the query run with forceindex, that index is not efficient for the query. There may be multiple reasons for this. The Performance and Tuning Guide is a good source of information on the subject of designing indexes for performance.

If the query run with forceindex is more efficient, there may be other issues that will require further analysis.

In some cases, an index may be incorrectly forced. It the case below, there is no statistics io output, but the traceon 302 output tells a great deal about the affects of an improper index force.

The user has forced the non-clustered index indid 3. The cost of a table scan is 11487 pages:

Base cost: indid: 0 rows: 166535 pages: 11487

The cost of using the non-clustered index is 167953 pages:

Cheapest index is index 3, costing 167953 pages and generating 166535 
rows per scan

Obviously, with the choice of either using a table scan or the non- clustered index, a table scan would be far more efficient.

"How Indexes Work" and "Indexing for Performance" in the Performance and Tuning Guide give detailed information on this subject.

User forces index 3

*******************************
Entering q_score_index() for table 'table_A' (objectid 1111727063).
The table has 166535 rows and 11487 pages.
Scoring the JOIN CLAUSE:
    col_a EQ col_a
    col_b EQ col_b

User forces index 3
Base cost: indid: 0 rows: 166535 pages: 11487 
Relop bits are: 5 
Estimate: indid 3, selectivity 1.000000e+00, rows 166535 pages 167953

Cheapest index is index 3, costing 167953 pages and generating 166535 
rows per scan.
Join selectivity is 1.
*******************************

Lead Index Columns with a High Number of Duplicates

The lead, or first, column of an index should be as selective as possible. If it is a dense column (many duplicate values), its selectivity will be low. The optimizer may decide that an index in such a case is more expensive to use than a table scan.

In the example below, the table has 75,000 rows on 1786 pages. The column test has 11 unique values. This is similar to situations that may arise in a "real" database. Often an index is placed on a customer or product number. Since there are relatively few distinct values in such columns, selectivity tends to be low.

In this case, the optimizer has determined that the use of the non- clustered index with a selectivity of 0.091317 would cost more than a table scan. It is worth noting that the same type of query on a column with 250 distinct values resulted in a selectivity of 0.004000 and the non-clustered index was chosen.

---

Note
See the section of this document titled "Understanding Selectivity" on page 40" for more examples of density on selectivity.

---

The examples below are both SARGs, but the principle is the same for join clauses. In the case of joins, the greater the value for selectivity, the more selective the index is.

Column test has 11 distinct values.

	
1> select * 
2> from VBT
3> where test = 10
4> go

*******************************
Entering q_score_index() for table 'VBT' (objectid 1600008731, varno = 
0).
The table has 75000 rows and 1786 pages.
Scoring the SEARCH CLAUSE:

        test EQ 

Base cost: indid: 0 rows: 75000 pages: 1786 prefetch: N 
        I/O size: 2 cacheid: 0 replace: MRU
Relop bits are: 5 
Qualifying stat page; pgno: 39201 steps: 334 
Search value: 10
Match found on statistics page 
equal to several rows including 1st or last--use endseveralSC 
Estimate: indid 4, selectivity 0.091317, rows 6849 pages 6888 index 
height 3 

Cheapest index is index 4, costing 6888 pages and 
        generating 6849 rows per scan, using no data prefetch (size 2)
        on dcacheid 0 with LRU replacement
Search argument selectivity is 0.091317.

*******************************

QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1
        The type of query is SELECT.

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

The examples below demonstrate the affects of density on selectivity.

Column qty has 250 distinct values.

	
1> select * 
2> from VBT
3> where qty = 10
4> go

*******************************
Entering q_score_index() for table 'VBT' (objectid 1600008731, varno = 
0).
The table has 75000 rows and 1786 pages.
Scoring the SEARCH CLAUSE:
        qty EQ 

Base cost: indid: 0 rows: 75000 pages: 1786 prefetch: N 
        I/O size: 2 cacheid: 0 replace: MRU
Relop bits are: 5 
Qualifying stat page; pgno: 39545 steps: 500 
Search value: 10
Match found on statistics page 
equal to several rows in middle of page--use midseveralSC 
Estimate: indid 5, selectivity 0.004000, rows 300 pages 304 index height 
3 

Cheapest index is index 5, costing 304 pages and 
        generating 300 rows per scan, using no data prefetch (size 2)
        on dcacheid 0 with LRU replacement
Search argument selectivity is 0.004000.

*******************************

QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1
        The type of query is SELECT.

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

Creating, Populating, Indexing, and Querying a Table in the Same Batch

When a table is created, populated, indexed (optional), and queried in the same batch, the optimizer does not have full knowledge of the number of rows and pages it contains. SQL Server creates a worktable to act as the real table while the query plan is built by the optimizer. The hardcoded assumption about the number of rows and pages for such a table is 100 rows on 10 pages.

	
1> select * into VBT from pubtune..VBT
2> create clustered index clu on VBT(id)
3> select * from VBT
4> where id between 10001 and 10008
5> go

*******************************
Entering q_score_index() for table 'Worktable' (objectid 0, varno = 0).
The table has 100 rows and 10 pages.
Scoring the SEARCH CLAUSE:
        id LE 
        id GE 

    STEP 1
        The type of query is SELECT.
        FROM TABLE
            VBT
        Nested iteration.
        Table Scan.
        Ascending scan.
        Positioning at start of table.
        Using I/O Size 2 Kbytes.
        With LRU Buffer Replacement Strategy.

Table: VBT  scan count 1,  logical reads: 3262,  physical reads: 3262
Table: #VBT  scan count 0,  logical reads: 158157,  physical reads: 0
Total writes for this command: 1956
(150000 rows affected)

For the example below, the table and index have previously been created and the table populated. The optimizer now has full information about the number of rows and pages in the table and is able to fully optimize the query and use the index.

	
1> select * from #VBT
2> where id between 10001 and 10008
3> go

*******************************

Entering q_score_index() for table 'VBT' (objectid 224003829, varno = 
0).
The table has 150000 rows and 3261 pages.
Scoring the SEARCH CLAUSE:
        id LE 
        id GE 

Base cost: indid: 0 rows: 150000 pages: 3261 prefetch: N 
        I/O size: 2 cacheid: 0 replace: MRU
Relop bits are: d 
Qualifying stat page; pgno: 10825 steps: 334 
Search value: *** CAN'T INTERPRET ***
No steps for search value--qualpage for LT search value finds 
value between step K, K + 1, K = 22--use betweenSC 
Scoring SARG interval, lower bound. 
Qualifying stat page; pgno: 10825 steps: 334 
Search value: *** CAN'T INTERPRET ***
No steps for search value--qualpage for LT search value finds 
value between step K, K + 1, K = 22--use betweenSC 
Net selectivity of interval: 0.000000e+000 
Estimate: indid 1, selectivity 0.000000, rows 1 pages 3 index height 2 

Cheapest index is index 1, costing 3 pages and 
        generating 1 rows per scan, using no data prefetch (size 2)
        on dcacheid 0 with LRU replacement
Search argument selectivity is 0.000000.

*******************************

QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1
        The type of query is SELECT.

        FROM TABLE
            #VBT
        Nested iteration.
        Using Clustered Index.
        Index : VBT_clu
        Ascending scan.
        Positioning by key.
        Keys are:
            id
        Using I/O Size 2 Kbytes.
        With LRU Buffer Replacement Strategy.

Table: VBT scan count 1, logical reads 3 ,physical reads: 2
Total writes for this command: 0
(8 rows affected)

Use of Variables, Functions, and Arithmetic Expressions in a Batch Query

If a variable is declared, given a value, and then selected against in a batch query, using any relational operator other than an equity (=), the optimizer will be forced to make its cost estimates based on the magicSC. This is because the optimizer builds the query plan before it knows the value of the variable. In such situations it must rely on the magicSC. As mentioned earlier, the magicSC is a set of three hardcoded selectivity values. The use of any of these values is dependent on the operator used in the query. (See "Situations that Will Cause the MagicSC or DensitySC to be Used" on page 45 for more information.)

However, if an equity is used as an operator, the optimizer can use the densitySC.

	
1> declare @var1 numeric(7,0)
2> declare @var2 numeric(7,0)
3> select @var1 = 100
4> select @var2 = 200
5> select * 
6> from VBT
7> where id between @var1 and @var2
8> go

*******************************
Entering q_score_index() for table 'VBT' (objectid 208003772, varno = 
0).
The table has 150000 rows and 3261 pages.
Scoring the SEARCH CLAUSE:
        id LE 
        id GE 

Base cost: indid: 0 rows: 150000 pages: 3261 prefetch: N 
        I/O size: 2 cacheid: 0 replace: MRU
Relop bits are: d 
SARG is a subbed VAR or expr result or local variable (constat = 4)--use 
magicSC or densitySC
Estimate: indid 1, selectivity 0.330000, rows 49500 pages 1079 index 
height 2 

Cheapest index is index 1, costing 1079 pages and 
        generating 49500 rows per scan, using no data prefetch (size 2)
        on dcacheid 0 with LRU replacement
Search argument selectivity is 0.330000.

*******************************

	
1> select * 
2> from VBT
3> where id between 100 and 200
4> go

*******************************

Entering q_score_index() for table 'VBT' (objectid 208003772, varno = 
0).
The table has 150000 rows and 3261 pages.
Scoring the SEARCH CLAUSE:
        id LE 
        id GE 

Base cost: indid: 0 rows: 150000 pages: 3261 prefetch: N 
        I/O size: 2 cacheid: 0 replace: MRU
Relop bits are: d 
Qualifying stat page; pgno: 7433 steps: 334 
Search value: *** CAN'T INTERPRET ***
No steps for search value--qualpage for LT search value finds 
value between step K, K + 1, K = 0--use betweenSC 
Scoring SARG interval, lower bound. 
Qualifying stat page; pgno: 7433 steps: 334 
Search value: *** CAN'T INTERPRET ***
No steps for search value--qualpage for LT search value finds 
value between step K, K + 1, K = 0--use betweenSC 
Net selectivity of interval: 0.000000e+000 
Estimate: indid 1, selectivity 0.000000, rows 1 pages 3 index height 2 

Cheapest index is index 1, costing 3 pages and 
        generating 1 rows per scan, using no data prefetch (size 2)
        on dcacheid 0 with LRU replacement
Search argument selectivity is 0.000000.

*******************************

	
1> declare @var1 numeric(7,0)
2> declare @var2 numeric(7,0)
3> select @var1 = 100
4> select @var2 = 200
5> select * 
6> from VBT
7> where id between @var1 and @var2
8> and num = @var1
9> and num = @var2
10> go

*******************************
Entering q_score_index() for table 'VBT' (objectid 208003772, varno = 
0).
The table has 150000 rows and 3261 pages.
Scoring the SEARCH CLAUSE:
        id LE 
        num EQ 
        num EQ 
        id GE 

Base cost: indid: 0 rows: 150000 pages: 3261 prefetch: N 
        I/O size: 2 cacheid: 0 replace: MRU
Relop bits are: d 
SARG is a subbed VAR or expr result or local variable (constat = 4)--use 
magicSC or densitySC
Estimate: indid 1, selectivity 0.330000, rows 49500 pages 1079 index 
height 2 

Cheapest index is index 1, costing 1079 pages and 
        generating 4950 rows per scan, using no data prefetch (size 2)
        on dcacheid 0 with LRU replacement
Search argument selectivity is 0.033000.

*******************************

When the Same Query on Two "Identical" Databases and/or Servers are Performing Differently

At times, there will be situations in which a query performs differently in different databases and/or servers. There may be a few issues at work here.
• First, check that the number of rows and pages in the tables involved are they same in both databases and/or servers. If there is a difference in size, the optimizer may be affected. Keep in mind that the optimizer bases its cost estimates on page reads; if two tables have a different number of rows and pages, the optimizer's estimates are likely to be different.
• Check that the SQL Server versions are identical and that the operating systems are the same.
• Determine how the data was moved between databases. If dump and load was used, the source and target databases will be the same; if bcp was used, the target database may contain a different number of pages. If the number of pages is different, there may be a difference in optimization.
• Are you sure that the query plans are different? Is showplan the same on both?
• Are there any differences in the amount of possible contention between the servers? Is one a development server and the other a production server?

Other Resources

The Performance and Tuning Guide

The art of handling optimizer problems is in knowing when there is a problem with SQL Server, when the database design is involved, and/or when a poorly written query is being run. We need to fully understand the general concepts and workings of those parts of the SQL Server that affect performance. The Performance and Tuning Guide can help in this area; it gives very good information from the basics of design issues to details of optimizer behavior.

AnswerBase

There are many useful documents in AnswerBase to help you resolve this sort of problem, with more being added all the time. Use obvious keywords in your search. For example, if you are looking for information on the distribution page, use "distribution page" as your keyword.

Related Links