Determining When Log Records Are Committed

set delayed_commit is a performance option suitable only for certain applications.

It increases SAP ASE performance for data manipulation language (DML) operation, (for example, insert, update, delete), but increases the risk of losing your data during a system failure. Performance gains depend on the application in use.

The types of applications that benefit from set delayed_commit typically include short transactions that are sent rapidly and serially to SAP ASE. For example, a batch application that issues many insert statements, with each insert being a separate transaction.

Use the set command to enable delayed_commit for a session, or with sp_dboption for the database.

After you enable set delayed_commit, the client application is notified of a successful commit before the corresponding log records are written to disk. This improves performance because all but the last log page are written to disk, alleviating contention on the last and active log page.

Before you enable set delayed_commit, consider: