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 it uses the 2K page it retrieves in 2K sizes, if 8K it retrieves in the 8K size, as shown:
Logical page size |
Memory pool |
|---|---|
2K |
16K |
4K |
64K |
8K |
128K |
16K |
256K |
Another example, 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, you need to actually configure the pools and run the 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.
Some guidelines:
If most I/O occurs in point queries using indexes to access a small number of rows, make the 16K pool relatively small, say about 10 to 20% of the cache size.
If you estimate that a large percentage of the I/Os will use the 16K pool, configure 50 to 75% of the cache for 16K I/O.
Queries that use 16K I/O include any query that scans a table, uses the clustered index for range searches and order by, and queries that perform matching or nonmatching scans on covering nonclustered indexes.
If you are not sure about the I/O size that will be used by your queries, configure about 20% of your cache space in a 16K pool, and use showplan and statistics i/o while you run your queries.
Examine the showplan output for the “Using 16K I/O” message. Check statistics i/o output to see how much I/O is performed.
If you think that your typical application mix uses both 16K I/O and 2K I/O simultaneously, configure 30 to 40% of your cache space for 16K I/O.
Your optimum may be higher or lower, depending on the actual mix and the I/O sizes chosen by the query.
If many tables are accessed by both 2K I/O and 16K I/O, Adaptive Server cannot use 16K I/O, if any page from the extent is in the 2K cache. It performs 2K I/O on the other pages in the extent that are needed by the query. This adds to the I/O in the 2K cache.
After configuring for 16K I/O, check cache usage and monitor the I/O for the affected devices, using sp_sysmon or Adaptive Server Monitor. Also, use showplan and statistics io to observe your queries.
Look for nested-loop join queries where an inner table would use 16K I/O, and the table is repeatedly scanned using the fetch-and-discard (MRU) strategy.
This can occur when neither table fits completely in cache. If increasing the size of the 16K pool allows the inner table to fit completely in cache, I/O can be significantly reduced. You might also consider binding the two tables to separate caches.
Look for excessive 16K I/O, when compared to table size in pages.
For example, if you have an 8000-page table, and a 16K I/O table scan performs significantly more than 1000 I/Os to read this table, you may see improvement by re-creating the clustered index on this table.
Look for times when large I/O is denied. Many times, this is because pages are already in the 2K pool, so the 2K pool will be used for the rest of the I/O for the query.
For a complete list of the reasons that large I/O cannot be used, see “When prefetch specification is not followed” on page 44 in the book Performance and Tuning: Optimizer.
