AUTO_READAHEAD session environment option

Use the AUTO_READAHEAD environment option to change the automatic read-ahead mode, the batch size, the threshold, or to disable automatic read-ahead operations for the current session.

The AUTO_READAHEAD session environment option has this syntax:

|--SET ENVIRONMENT AUTO_READAHEAD -'--+0+--+------+--+----------+--'|
                                      +1+  ',pages'  ',threshold'
                                      '2'

Element	Description	Restrictions	Syntax
`pages`	Number of data pages to read ahead	Must be an integer in the range `4` ≤ pages ≤ `4096`	Literal Number
threshold	Percentage of unprocessed pages that will trigger next read	Must be an integer in the range `1` ≤ threshold ≤ `100`	Literal Number

Usage

The SET ENVIROMENT AUTO_READAHEAD statement of SQL accepts up to three values as its automatic read-ahead setting:

A required mode setting, encoded as a digit in the range 0 ≤ mode ≤ 2
An optional pages setting, encoded as an integer in the range 4 ≤ pages ≤ 4096.
An optional threshold setting, encoded as an integer in the range 1 ≤ threshold ≤ 100.

Setting the mode to aggressive, standard, or disabled

You can change the automatic read-ahead mode for the current session by specifying one of the following values as the first AUTO_READAHEAD parameter:

0: Disable automatic read-ahead requests.
1: Enable automatic read-ahead requests in the standard mode. The server will automatically process read-ahead requests only when a query waits on I/O.
2: Enable automatic read-ahead requests in the aggressive mode. The server will automatically process read-ahead requests at the start of the query, and continuously through the duration of the query.

The value that you specify for the mode overrides the setting of the AUTO_READAHEAD configuration parameter for the session.

This is the descending order of precedence (highest to lowest) among methods for setting automatic read-ahead:

The SET ENVIRONMENT AUTO_READAHEAD statement (for a session)
The AUTO_READAHEAD configuration parameter value of 1 or 2.
If AUTO_READAHEAD has no setting in the onconfig file, but the AUTO_TUNE configuration parameter is set to 1, the server performs automatic read-ahead on 128 data pages, equivalent to the default pages value in the standard mode.

Setting the number of pages to read ahead

Besides setting the automatic read-ahead mode, you can also optionally specify a pages value as the second AUTO_READAHEAD parameter:

pages: Specifies the batch size in pages (as an integer in the range 4 ≤ pages ≤ 4096) read by the database server when it receives an automatic read-ahead request.

Use a comma ( , ) as the separator between the values of read-ahead mode and read-ahead pages.

The specified pages value overrides the explicit or default batch size setting of the AUTO_READAHEAD configuration parameter for the current session.

If the SET ENVIRONMENT AUTO_READAHEAD statement includes no second parameter, the batch size value defaults to the explicit AUTO_READAHEAD configuration parameter setting, or to 128 pages, if that configuration parameter is not set.

Setting the threshold

Besides setting the automatic read-ahead mode and the batch size, you can also optionally specify a threshold value as the third AUTO_READAHEAD parameter:

threshold: Specifies the percentage (as an integer in the range 1 ≤ threshold ≤ 100) of the current batch that will remain unprocessed when the next batch of pages is requested.

Use a comma ( , ) as the separator between the values of read-ahead pages and read-ahead threshold.

The specified threshold value overrides the explicit or default threshold setting of the AUTO_READAHEAD configuration parameter for the current session.

If the SET ENVIRONMENT AUTO_READAHEAD statement includes no third parameter, the threshold value defaults to the explicit AUTO_READAHEAD configuration parameter setting, or to 50, if that configuration parameter is not set.

Examples of setting AUTO_READAHEAD

Note that the value in the statement must be quoted, either with single or double quotes.

This statement disables automatic read-ahead operations during the current session:

SET ENVIRONMENT AUTO_READAHEAD '0';

After a session completes the work in which you wanted automatic read-ahead disabled, the following statement restores automatic read-ahead in standard mode for subsequent operations that scan tables:

SET ENVIRONMENT AUTO_READAHEAD '1';

If a larger AUTO_READAHEAD page setting seems more efficient, the next example maintains the standard mode, but instructs the server to increase the batch size to 1024 for subsequent read-ahead requests:

SET ENVIRONMENT AUTO_READAHEAD '1,1024';

If a different threshold setting seems more efficient, the next example instructs the server to trigger the next read-ahead request when 30% of the current batch remains unprocessed. Since our batch size is 1000 in this case, the next batch will be requested when the reader has 300 pages to process:

SET ENVIRONMENT AUTO_READAHEAD '1,1000,30';

Generally, the standard mode (AUTO_READAHEAD = 1) is appropriate in typical production environments, even for cached environments, but the SET ENVIRONMENT AUTO_READAHEAD statement enables you to take action in contexts where the efficiency of specific scans might benefit from modifying the read-ahead behavior.

The following example enables automatic read-ahead in aggressive mode, but accepts the default number of read-ahead pages and the threshold:

SET ENVIRONMENT AUTO_READAHEAD '2';

Use aggressive read-ahead operations only in situations in which you tested both settings and know that aggressive read-ahead operations are more effective. Do not use aggressive read-ahead operations if you are not sure that they are more effective.

For scans that might turn read-ahead operations off and on because the scan encounters pockets of cached data, aggressive mode read-ahead operations do not turn off read-ahead operations.