Checking I/O size for queries

You can examine query plans and I/O statistics to determine which queries are likely to perform large I/O, and the amount of I/O those queries perform. This information can form the basis for estimating the amount of 16K I/O the queries should perform with a 16K memory pool. I/Os are done in terms of logical page sizes; if large I/O uses 2K pages, it retrieves in 2K sizes, if 8K pages, it retrieves in the 8K size, as shown:

Logical page size

Memory pool

2K

16K

4K

32K

8K

64K

16K

128K

For another example, consider that a query that scans a table and performs 800 physical I/Os using a 2K pool should perform about 100 8K I/Os.

See “Large I/O and performance” for a list of query types.

To test your estimates, configure the pools and run individual queries and your target mix of queries to determine optimum pool sizes. Choosing a good initial size for your first test using 16K I/O depends on a good sense of the types of queries in your application mix.

This estimate is especially important if you are configuring a 16K pool for the first time on an active production server. Make the best possible estimate of simultaneous uses of the cache.

These guidelines provide some points of reference: