I/O size messages

The I/O messages are:

Using I/O size N Kbtyes for data pages.

Using I/O size N Kbtyes for index leaf pages.

These messages report the I/O sizes used in the query. Possible I/O sizes are 2, 4, 8, and 16 kilobytes.

If the table, index, or database used in the query uses a data cache with large I/O pools, the optimizer can choose large I/O. It can choose to use one I/O size for reading index leaf pages, and a different size for data pages. The choice depends on the pool size available in the cache, the number of pages to be read, the cache bindings for the objects, and the cluster ratio for the table or index pages.

Either (or both) of these messages can appear in the showplan output for a SCAN operator. For a table scan, only the first message is printed; for a covered index scan, only the second message is printed. For an Index Scan that requires base table access, both messages are printed.

After each I/O size message, a cache strategy message is printed:

With <LRU/MRU> Buffer Replacement Strategy for data pages.

With <LRU/MRU> Buffer Replacement Strategy for index leaf pages.

In an LRU replacement strategy, the most recently accessed pages are positioned in the cache to be retained as long as possible. In an MRU Replacement Strategy, the most recently accessed pages are positioned in the cache for quick replacement.

Sample I/O and cache messages are shown in the following query:

use pubs2
go
set showplan on
go
select au_fname, au_lname, au_id from authors
where au_lname = "Williams"
go

QUERY PLAN FOR STATEMENT 1 (at line 1).

1 operator(s) under root
The type of query is SELECT.

ROOT:EMIT Operator (VA = 1)

    |SCAN Operator (VA = 0)
    |  FROM TABLE
    |  authors
    |  Index : aunmind
    |  Forward Scan.
    |  Positioning by key.
    |  Keys are:
    |    au_lname ASC
    |  Using I/O Size 2 Kbytes for index leaf pages.
    |  With LRU Buffer Replacement Strategy for index leaf pages.
    |  Using I/O Size 2 Kbytes for data pages.
    |  With LRU Buffer Replacement Strategy for data pages.

The SCAN operator of the authors table uses the index aunmind, but must also read the base table pages to get all of the required columns from authors. In this example, there are two I/O size messages, each followed by the corresponding buffer replacement message.

There are two kinds of table SCAN operators that have their own messages—the RID SCAN and the LOG SCAN.