By default, a query uses large I/O whenever a large I/O pool is configured and the optimizer determines that large I/O would reduce the query cost. To disable large I/O during a session, use:
set prefetch off
To reenable large I/O, use:
set prefetch on
If large I/O is turned off for an object using sp_cachestrategy, set prefetch on does not override that setting.
If large I/O is turned off for a session using set prefetch off, you cannot override the setting by specifying a prefetch size as part of a select, delete, or insert statement.
The set prefetch command takes effect in the same batch in which it is run, so you can include it in a stored procedure to affect the execution of the queries in the procedure.
