Index type and large I/O

When you specify an I/O size with prefetch, the specification can affect both the data pages and the leaf-level index pages. Table 20-1 shows the effects.

Table 20-1: Access methods and prefetching

Access method

Large I/O performed on

Table scan

Data pages

Clustered index

Data pages only, for allpages-locked tables

Data pages and leaf-level index pages for data-only-locked tables

Nonclustered index

Data pages and leaf pages of nonclustered index

showplan reports the I/O size used for both data and leaf-level pages.

See “I/O Size Messages” for more information.