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
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” on page 790.
Table 37-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 37-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.
Table 37-2 shows the access methods that correspond to the pathtype information in the dbcc traceon(310) output.
Table 37-2: pathtypes in dbcc traceon(310) output