Reducing Logging by Increasing Page Allocations

If you are using fast or fast-logged bcp, consider that each bcp in batch requires the page manager to allocate one or more extents. Each such allocation generates a single log record.

Use the number of pre-allocated extents configuration parameter to specify how many extents the SAP ASE server is to allocate through the page manager:
  • Valid values for the number of pre-allocated extents configuration parameter are from 1 to 32; the default value is 2.

  • The number of pre-allocated extents parameter is dynamic, not static. For this reason, you need not restart the SAP ASE server after you change its value.

  • An object may be allocated more pages than actually needed, so the value of number of pre-allocated extents should be low if you are using bcp for small batches. If you are using bcp for large batches, increase the value of number of pre-allocated extents to reduce the amount of overhead required to allocate pages and to reduce the number of log records.

The SAP ASE server may allocate more pages than are actually needed, so keep the value small when space is limited. These pages are deallocated at the end of the batch.

In SAP ASE version 15.5 and later, the maximum values of preallocated extents has been increased increased from 31 to 32.

Using a value of 32 for the number of pre-allocated extents parameter has a special significance for configuration, and impacts the space allocations the SAP ASE server performs internally. If you set the number of preallocated extents to 32, the SAP ASE server attempts to reserve an entire allocation unit of extents for utility operations that use a large-scale allocation scheme of space reservation, such as bcp in and select into.

Using the maximum number of preallocated extents can greatly improve the performance of these utilities, particularly when you run them in parallel. Using a value of 32 greatly increases the likelihood that each engine running the utility can work independently on its own allocation unit without interference from other engines.

For more information, see Setting Configuration Parameters in the System Administration Guide: Volume 1.