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 38-1 shows the meaning of the values in the output.

**Table 38-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 38-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 38-2 shows the access methods that correspond to the pathtype information in the dbcc traceon(310) output.

**Table 38-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