AUTO_READAHEAD session environment option

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

The AUTO_READAHEAD session environment option has this syntax:


Element	Description	Restrictions	Syntax
`pages`	Number of data pages to read ahead	Must be an integer in the range `'4'` < 'page' < `'4096'`	Quoted String

Usage

The SET ENVIROMENT AUTO_READAHEAD statement of SQL accepts up to two 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 < page < 4096.

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' or "0": Disable automatic read-ahead requests.
'1' or "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' or "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' or "pages": Specifies the number of data 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 page-count setting of the AUTO_READAHEAD configuration parameter for the current session.

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

Examples of setting AUTO_READAHEAD

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 page count to 1024 for subsequent read-ahead requests:

SET ENVIRONMENT AUTO_READAHEAD '1','1924';

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:

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.