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:

AUTO_READAHEAD session environment option

2.1 '0'
2.1 '1'
2.1 '2'?  , 'pages'
Element Description Restrictions Syntax
pages Number of data pages to read ahead Must be an integer in the range '4' < 'page' < '4096' Quoted String


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:
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:
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:

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:
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.