Analyzing and Resolving Optimizer
Problems/Symptoms
Problems/Symptoms
Last revised: January 1997
Send comments to
eminer@sybase.com.
Send comments to eminer@sybase.com.
Contents
-
What's New in This Version?
Optimizer Problem Checklist
-
"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.
-
Sample Script for Obtaining Output: Batch Query Via isql
Sample Script for Obtaining Output: Stored Procedure Via isql
-
Run update statistics
-
update statistics Syntax
Example of the Effects of Running update statistics
Some Basic Questions to Start With
-
The Text of the Query, Stored Procedure(s), View Definition(s) or
Trigger(s)
sp_help for All Tables Involved showplan
-
Reading showplan Output
-
Reading set statistics io Output
set rowcount and statistics io Output
-
Reading statistics time Output
-
noexec, set fmtonly on and Stored Procedure Analysis
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
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
-
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
-
What is a Work Plan?
Differences Between traceon 310 and 317 Output in Pre-System 11 and System 11 SQL Servers
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
The Effects of Sorting and Reformatting on the Cost of Plans
-
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
-
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:
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.
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.
Note
Detailed information about the items in this checklist is provided after the checklist.
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).
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.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
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.
<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
join plan the combination of the order of the tables in a join and the method of data access chosen by the optimizer.
Note
relop is a representation of any valid relational operator, such as =, >, <=, etc.
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).
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.
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.
Note
For more information on selectivity, go to "Understanding Selectivity" on page 40 of this document.
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.
isql -Usa -P -e -ifile_nameThe 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
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.
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.
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.sp_help is a system stored procedure. The syntax is:
sp_help object_nameSee 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.
Look for Mismatched Datatypes
Using the query and sp_help, look for mismatched datatypes in join clauses. For example:
where tableA.col1=tableB.col1A 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.
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.
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.
showplan
showplan is an option of the set command. The syntax is:
set showplan onshowplan 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.
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...".
statistics io
statistics io is an option of the set command. The syntax is:
set statistics io onstatistics 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.
See the Performance and Tuning Guide for more information about set statistics io.
-
Run the query with statistics io; save the output.
-
Force the "right" index with statistics io still on.
-
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: 0The line:
Table: table_name scan count numberindicates 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).
logical reads: numberindicates 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.
physical reads: numberindicates 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).
Table: titles scan count 20, logical reads: 46, physical reads: 0The line
total writes for this command: numberindicates 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.
Table: titles scan count 1, logical reads: 75, physical reads: 75 Total writes for this command: 0In 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: 0It 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 onstatistics 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.
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.
- 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.
set fmtonly onThis 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.
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.
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.
forceindex/Specifying an Index
Note
While forceindex and forceplan are useful analysis tools and as a temporary fix, it is inadvisable to become dependent on them.
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.
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.
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.
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.)
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:
-
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).
-
Note the values for statistics io.
-
Run the query again with the same output on using forceindex
and/or forceplan.
-
Note the value of statistics io.
-
Verify that the query plan you expect to be used is being used;
check via showplan.
-
Determine which plan is cheaper in terms of logical I/O.
- 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
The Effect of noexec on Output Necessary for Analysis
noexec is an option of the set command. The syntax is:
set noexec onThe 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.
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.
dbcc traceon(3604,302)
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.
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.
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.
Base cost: indid: 0 rows: 5000 pages: 621The 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: 5You 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: 132Next 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.
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.
Search value:10Order 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.
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).
1> select * 2> from VBT,VBT2 3> where VBT.test=VBT2.test2 4> and VBT.id between 100 and 200 5> goq_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.
SARG is a subbed VAR or expr result or local variable (constat = 4)--use magicSC or densitySCThe 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:
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.
Situations that Will Cause the MagicSC or DensitySC to be Used
There is No Qualifying Index on the SARG and/or Join Column(s)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
*******************************
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.
The steps to take to ensure that index statistics are up to date in this situation is to:
-
Truncate the table
-
Repopulate the table
-
Run update statistics
Using a Variable, Function, or an Arithmetic Expression in a Batch Query or Stored Procedure
(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
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
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> goHowever, 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> goUsing a Function in a Batch Query
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 = Xor
where col = NULL
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 (=).
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
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.
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?
Only Partial q_score_index Information Returned
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.
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.
Note
If you use 310 and 317 in conjunction, a work plan is printed for each new plan.
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.
- 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
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
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.
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
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
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
- 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)
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.
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.
(2 ms * total logical reads lp) + (18 ms * total physical reads pp)
2 * lp + 18 * pp = total costIn 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 =
indexid=
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> goTo 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> goThe 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 ITERATIONpath= 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 is useful when there is a need to identify the type of operation being performed.
method=
outerrows=1 rows=500 joinsel=1.000000 cpages=204 prefetch=N iosize=2 replace=LRUouterrows=
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.
Note
"current table" is the table that is costed in the block of traceon 310/317 output you are currently reading.
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: 0rows=
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=
prefetch=
iosize=
Note
This feature is only available in SQL Server 11.0 and later releases.
replace=
Note
This feature is only available in SQL Server 11.0 and later releases.
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]=1The 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.
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: 0The 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=
jnvar=
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.
refcost=
This is the cost of reformatting. If it is chosen, it is given in milliseconds.
refpages=
reftotpages=
ordercol[0]=
select * from T1, T2 where T1.a = T2.bordercol[1]=
TOTAL # PERMUTATIONS:
TOTAL # PLANS CONSIDERED:
CACHE USED BY THIS PLAN:
CacheID = 0: (2K) 551 (4K) 0 (8K) 0 (16K) 0This 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.
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.
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.
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.
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.
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.
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.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.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.
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: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 ITERATIONIn 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.
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.
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.
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
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.
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.
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.
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
SortingIn this example, an order by clause has been added to the sample query:
order by au_lnameIn 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=1Reformatting
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.Keep in mind that the non-clustered index must qualify for the query.
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.
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
1> update table VBT 2> set test2 = 7500 3> where id between value_1 and value_2 4> goThe identity column is sequential on this table, it begins with 1 and ends with 150,000.
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.
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.
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
Since the values in column "date" are randomly placed on the table the update of "test2" to the value 7500 is also random.
Note
Four dates were used, dates are randomly dispersed on the table
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".
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 ONRun the query without the use of forceindex.
Run it again with the use of forceindex.
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: 11487The cost of using the non-clustered index is 167953 pages:
Cheapest index is index 3, costing 167953 pages and generating 166535 rows per scanObviously, with the choice of either using a table scan or the non- clustered index, a table scan would be far more efficient.
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.
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.
Note
See the section of this document titled "Understanding Selectivity" on page 40" for more examples of density on selectivity.
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.

Back to Top