Large I/O and performance

You can configure the default cache and any named caches you create for large I/O by splitting a cache into pools. The default I/O size is 2K, one Adaptive Server data page.

Reference to Large I/Os are on a 2K logical page size server. If you have an 8K page size server, the basic unit for the I/O is 8K. If you have a 16K page size server, the basic unit for the I/O is 16K.

For queries where pages are stored and accessed sequentially, Adaptive Server reads up to eight data pages in a single I/O. Since the majority of I/O time is spent doing physical positioning and seeking on the disk, large I/O can greatly reduce disk access time. In most cases, you want to configure a 16K pool in the default data cache.

Certain types of Adaptive Server queries are likely to benefit from large I/O. Identifying these types of queries can help you determine the correct size for data caches and memory pools.

In the following examples, either the database or the specific table, index or LOB page change (used for, text, image, and Java off-row columns) must be bound to a named data cache that has large memory pools, or the default data cache must have large I/O pools. Types of queries that can benefit from large I/O include:

Queries that scan entire tables. For example:

select title_id, price from titles

select count(*) from authors
    where state = "CA"   /* no index on state */

Range queries on tables with clustered indexes. For example:

where indexed_colname >= value

Queries that scan the leaf level of an index, both matching and non-matching scans. If there is a nonclustered index on type, price, this query could use large I/O on the leaf level of the index, since all the columns used in the query are contained in the index:

select type, sum(price)
    from titles
    group by type

Queries that join entire tables, or large portions of tables. Different I/O sizes may be used on different tables in a join.

Queries that select text or image or Java off-row columns. For example:

select au_id, copy from blurbs

Queries that generate Cartesian products. For example:

select title, au_lname
     from titles, authors

This query needs to scan all of one table, and scan the other table completely for each row from the first table. Caching strategies for these queries follow the same principles as for joins.

Queries such as select into that allocate large numbers of pages.

Adaptive Server version 12.5.03 or later enables large-page allocation in select into. It allocates pages by extent rather than by individual page, thus issuing fewer logging requests for the target table.

If you configure Adaptive Server with large buffer pools, it uses large I/O buffer pools when writing the target table pages to disk.

create index commands.

Bulk copy operations on heaps—both copy in and copy out.

The update statistics, dbcc checktable, and dbcc checkdb commands.