Final plan information

The plan chosen by the optimizer is displayed in the final plan block. Information about the cost of each table is printed; the output starts from the outermost table in the join order.

select pub_name, au_lname, price
from titles t, authors a, titleauthor ta,
            publishers p
where t.title_id = ta.title_id
    and a.au_id = ta.au_id
    and p.pub_id = t.pub_id
    and type = ’business’
    and price < $25
FINAL PLAN (total cost = 3909)

varno=0 (titles) indexid=1 (title_id_ix)
path=0xd6b25148 pathtype=pll-mrgscan-outer
method=NESTED ITERATION 
scanthreads=3
outerrows=1 outer_wktable_pgs=0 rows=164 joinsel=1.000000
jnpgs_per_scan=3 scanpgs=623
data_prefetch=YES data_iosize=16 data_bufreplace=LRU
scanlio_perthrd=211 tot_scanlio=633 scanpio_perthrd=116 tot_scanpio=346
outer_srtmrglio=0 inner_srtmrglio=0
corder=1

varno=2 (titleauthor) indexid=3 (ta_ix)
path=0xd6b20000 pathtype=pll-mrgscan-inner
method=FULL MERGE JOIN 
scanthreads=3 mergethreads=3
outerrows=164 outer_wktable_pgs=0 rows=243 joinsel=0.000237
jnpgs_per_scan=2 scanpgs=87
index_prefetch=YES index_iosize=16 index_bufreplace=LRU
scanlio_perthrd=29 total_scanlio=87 scanpio_perthrd=29 tot_scanpio=87
outer_srtmrglio_perthrd=0 tot_outer_srtmrglio=0
inner_srtmrglio_perthrd=0 tot_inner_srtmrglio=0
corder=2

varno=1 (authors) indexid=3 (au_id_ix)
path=0xd6b20318 pathtype=join
method=NESTED ITERATION 
scanthreads=1
outerrows=243 rows=243 joinsel=0.000200 jnpgs_per_scan=3
index_prefetch=NO index_iosize=2 index_bufreplace=LRU
data_prefetch=NO data_iosize=2 data_bufreplace=LRU
scanlio=82 scanpio=9
corder=1

jnvar=2 refcost=0 refpages=0 reftotpages=0 ordercol[0]=1  ordercol[1]=1

varno=3 (publishers) indexid=0 ()
path=0xd6b1f150 pathtype=sclause
method=SORT MERGE JOIN 
scanthreads=1
outerrows=243 outer_wktable_pgs=7 rows=243 joinsel=0.033333
jnpgs_per_scan=1 scanpgs=3
data_prefetch=NO data_iosize=2 data_bufreplace=LRU
scanlio=3 scanpio=3
outer_srtmrglio_perthrd=88 tot_outer_srtmrglio=250
inner_srtmrglio_perthrd=31 tot_inner_srtmrglio=30
corder=0

Sort-Merge Cost of Inner = 98 
Sort-Merge Cost of Outer = 344

For the showplan output for the same query, see “Merge join messages”.

Table 7-1 shows the meaning of the values in the output.

Table 7-1: dbcc traceon(310) output

Label

Information provided

varno

Indicates the table order in the from clause, starting with 0 for the first table. The table name is provided in parentheses.

indexid

The index ID, followed by the index name, or 0 for a table scan.

pathtype

The access method for this table. See Table 7-2.

method

The method used for the scan or join:

  • NESTED ITERATION

  • NESTED ITERATION with Tuple Filtering

  • REFORMATTING

  • REFORMATTING with Unique Reformatting

  • OR OPTIMIZATION

  • SORT MERGE JOIN

  • RIGHT MERGE JOIN

  • LEFT MERGE JOIN

  • FULL MERGE JOIN

scanthreads

Number of worker processes to be used for the scan of this table.

merge threads

Number of threads to use for a parallel data merge, for a sort-merge join.

outerrows

Number of rows that qualify from the outer tables in the query or 1, for the first table in the join order.

outer_wktable_pgs

For a merge join, the number of pages in the worktable that is outer to this table, or tables in a full-merge join.

rows

Number of rows estimated to qualify in this table or as a result of this join. For a parallel query, this is the maximum number of rows per worker process.

joinsel

The join selectivity.

jnpgs_per_scan

Number of index and data pages to be read for each scan.

scanpgs

The total number of index and data pages to be read for the table.

index_prefetch

YES if large I/O will be used on index leaf pages (not printed for table scans and allpages-locked table clustered index scans).

index_iosize

The I/O size to be used on the index leaf pages (not printed for table scans and allpages-locked table clustered index scans).

index_bufreplace

The buffer replacement strategy to be used on the index leaf pages (not printed for table scans and allpages-locked table clustered index scans).

data_prefetch

YES if large I/O will be used on the data pages; NO if large I/O will not be used (not printed for covered scans).

data_iosize

The I/O size to be used on the data pages (not printed for covered scans).

data_bufreplace

The buffer replacement strategy to be used on the data pages (not printed for covered scans).

scanlio

Estimated total logical I/O for a serial query.

scanpio

Estimated total physical I/O for a serial query.

scanlio_perthrd

Estimated logical I/O per thread, for a parallel query.

tot_scanlio

Estimated total logical I/O, for a parallel query.

scanpio_perthrd

Estimated physical I/O per thread, for a parallel query.

tot_scanpio

Estimated total physical I/O, for a parallel query.

outer_srtmrglio_perthrd

Estimated logical I/O on the outer table to perform the sort-merge, per thread.

tot_outer_srtmrglio

Estimated total logical I/O on the outer table to perform a sort-merge.

inner_srtmrglio_perthrd

Estimated logical I/O on the inner table to perform a sort-merge join, per thread.

tot_inner_srtmrglio

Estimated total logical I/O on the inner table to perform a sort-merge join.

corder

The order of the column used as a search argument or join key.

jnvar

The varno of the table to which this table is being joined, for second and subsequent tables in a join.

refcost

The total cost of reformatting, when reformatting is considered as an access method.

refpages

The number of pages read in each scan of the table created for formatting. Included for the second and subsequent tables in the join order.

reftotpages

The number of pages in the table created for formatting. Included for the second and subsequent tables in the join order.

ordercol[0]

The order of the join column from the inner table.

ordercol[1]

The order of the join column from the outer table.

Table 7-2 shows the access methods that correspond to the pathtype information in the dbcc traceon(310) output.

Table 7-2: pathtypes in dbcc traceon(310) output

pathtype

Access method

sclause

Search clause

join

Join

orstruct

or clause

join-sort

Join, using a sort-avert index

sclause-sort

Search clause, using a sort-avert index

pll-sarg-nc

Parallel index hash scan on a search clause

pll-join-nc

Parallel index hash scan on a join clause

pll-sarg-cl

Parallel clustered index scan on a search clause

pll-join-cl

Parallel clustered index scan on a join

pll-sarg-cp

Parallel partitioned clustered index scan on a search clause

pll-join-cp

Parallel partitioned clustered index scan on a join clause

pll-partition

Parallel partitioned table scan

pll-nonpart

Parallel nonpartitioned table scan

pll-mrg-scan-inner

Parallel sort-merge join, with this table as the inner table

pll-mrg-scan-outer

Parallel sort-merge join, with this table as the outer table