The following sections describe performance issues when using Adaptive Server with larger logical page sizes.
The default logical page size for Adaptive Server is 2K. If you are using larger logical pages, the memory for data caches and buffer pools is allocated in terms of logical pages. For example, if you are using a server with a 2K page size and the following cache configuration:
Memory allocated |
Number of buffers |
|
|---|---|---|
Default data cache |
20MB |
10240 |
2K buffer pool |
10MB |
5120 |
8K buffer pool |
5MB |
640 |
16K buffer pool |
5MB |
320 |
The number of buffers in the 8K pool (640) is in terms of the number of 8K masses. Each mass in the 8K pool consists of four 2K buffers. The number of buffers in the 16K pool (320) gives the number of 16K masses.For a server with page size of 8K, the basic buffer size is 8K. The larger-sized buffer pools are 8, 16, 32, or 16K.
The cache configuration above results in the following numbers of buffers available in each buffer pool.
Memory allocated |
Number of buffers |
|
|---|---|---|
Default data cache |
20MB |
2560 |
8K buffer pool |
10MB |
1280 |
32K buffer pool |
5MB |
160 |
64K buffer pool |
5MB |
80 |
For this example, the 5MB memory allocated to the 32K and 64K buffer pool, the number of masses available are too few to set up the server’s buffer pool.
When you are sizing the buffer pool, you should size
according to the number of logical pages in a single buffer, rather
than in terms of Kilobytes.
You must size the individual buffer pools in various caches according to the server’s logical page size. If you do not size the caches and buffer pools properly, the buffer pool may not have enough buffers, causing excessive buffer washing, which negatively affects the performance of your queries.
Because of Adaptive Server version 12.5 larger logical pages, the term Large I/O implies the use of a mass of 8 logical pages, and is the amount of I/O to read an extent’s worth of data. Depending on your server’s logical page size, large I/O is 16, 32, 64, or 128K.
If your configuration file for a server using a 2K page size Adaptive Server specifies a 16K buffer pool, and you want to use large I/O on another server running with 16K logical page size, you must specify a buffer pool of size 128K in the latter server.
Specifying a 16K buffer pool in the 16K logical page size server creates a buffer pool of the basic page size, which will not support large I/Os.
Consider the following when configuring for large I/Os:
The set prefetch on option allows Adaptive Server to prefetch data using large I/O only if large I/O buffer pools are available.
If you are using larger logical pages, the prefetch specifier for select, delete, and update statements should specify the larger prefetch size based on the server’s logical page size. Prefetch sizes that are less than the logical page size are ignored, and all I/O is performed using the buffers the size of the logical page.
For data-only-locked (DOL) tables, the table-level property concurrency_opt_threshold directs the optimizer to favor index scans on small tables. The default value for this is 15 logical pages. However, the data for some medium-sized tables may require more than 15 logical pages, although the same data would fit into far fewer pages on a server that uses larger logical pages.
This situation may cause the optimizer to favor an index scan on small tables in servers that use larger logical pages, whereas the same query on a server that uses 2K logical pages can result in the optimizer selecting a more efficient access method.
This potential change in behavior is data-dependent and must be analyzed on an individual query basis. You can use sp_chgattribute to lower the concurrency_opt_threshold value for affected tables to achieve the optimizer characteristics that are seen on servers that use 2K logical pages.
All optimizer costing for physical and logical I/O is done in terms of page sizes. The cost estimates reported under various trace flags (such as 302, 310) are in terms of logical pages. Data clustering ratios like data page cluster ratio and Index page cluster ratio are all in terms of logical pages. It is important that you think in terms of logical pages as well as 2K pages when you configure the server.
For larger pages, the index height tends to be smaller than the index height for smaller logical pages. You may find differences in cost estimates, and choices of indexes on larger pages. Refer to the Performance and Tuning Guide for techniques to analyze these differences in query plan choices. In some instances, references to 2K logical pages in this document should be treated in terms of the current logical page size for your server.
