If your SQL Server is configured for large I/Os in the default data cache or in named data caches, the optimizer can decide to use large I/O for:
Queries that scan entire tables
Range queries using clustered indexes, such as queries using >, <, > x and < y, between, and like "charstring%"
Queries that use covering nonclustered indexes
In these cases, disk I/O can access up to eight pages simultaneously, if the cache used by the table or index is configured for it.
Each named data cache can have several pools, each with a different I/O size. Specifying the I/O size in a query causes the I/O for that query to take place in the pool that is configured for that size. See Chapter 9, "Configuring Data Caches" in the System Administration Guide for information on configuring named data caches.
To specify a particular I/O size, add the prefetch specification to the index clause of a select, delete, or update statement. The syntax is:
select select_list
from table_name
(index index_name prefetch size)
[, table_name ...]
where ...
delete from table_name (index index_name
prefetch size) ...
update table_name set col_name = value
from table_name (index index_name
prefetch size) ...
Valid values for size are 2, 4, 8, and 16. If no pool of the specified size exists in the data cache used by the object, the optimizer chooses the best available size.
If there is a clustered index on au_lname, this query performs 16K I/O while it scans the data pages:
select *
from authors (index au_names prefetch 16)
where au_lname like "Sm%"
If a query normally performs prefetch, and you want to check its I/O statistics with 2K I/O, you can specify a size of 2K:
select type, avg(price)
from titles (index type_price prefetch 2)
group by type
Note: If you are experimenting with prefetch sizes and checking statistics i/o for physical reads, you may need to clear pages from the cache so that SQL Server will perform physical I/O on the second execution of a query. If the table or index, or its database, is bound to a named data cache, you can unbind and rebind the object. If the query uses the default cache, or if other tables or indexes are bound to the object's cache, you can run queries on other tables that perform enough I/O to push the pages out of the memory pools.
To perform prefetching on the data pages, specify either the clustered index name, or the table name. To perform prefetching on the leaf level pages of a nonclustered index (for covered queries, for example), specify the nonclustered index name.
| Index Name Parameter | Prefetching Performed On |
|---|---|
| Table name | Data pages |
| Clustered index name | Data pages |
| Nonclustered index name | Leaf pages of nonclustered index |
Normally, when you specify an I/O size in a query, the optimizer incorporates the I/O size into the query's plan. However, the specification cannot be followed:
If the cache is not configured for I/O of the specified size, the optimizer substitutes the "best" size available.
If any of the pages included in that I/O request are in cache. If the I/O size specified is eight data pages, but one of the pages is already in the 2K pool, SQL Server performs 2K I/O on the rest of the pages for that I/O request.
If the page is on the first extent in an allocation unit. This extent holds the allocation page for the allocation unit, and only 7 data pages.
If there are no buffers available in the pool for that I/O size, SQL Server uses the next lowest available size.
If prefetching has been turned off for the table or index with sp_cachestrategy.
The system procedure sp_sysmon reports on prefetches requested and denied for each cache. See "Data Cache Management".
By default, SQL Server checks whether prefetching is useful for all queries. To disable prefetching during a session, use the command:
set prefetch off
To re-enable prefetching, use the command:
set prefetch on
If prefetching is turned off for an object with sp_cachestrategy, this command does not override that setting.
If prefetching is turned off for a session with set prefetch off, you cannot override it by specifying a prefetch size in a select, delete, or insert command.
The set prefetch command takes effect in the same batch in which it is run, so it can be included in stored procedures to affect the execution of the queries in the procedure.