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.
Issuing shudown with nowait can cause data durability issues unless you issue a checkpoint that finishes before the server shuts down.
Enabling set delayed_commit for a session affects only that session. All other sessions’ transactions have all their properties enforced, including their ACID properties. This also means other sessions’ physical logs write the last log page and the log records corresponding to a session with set delayed_commit enabled.
set delayed_commit is redundant on temporary databases and does not provide a performance improvement.
Use set delayed_commit only after careful consideration of both your application and operational requirements and your environment. While the risk to data durability may be very low, the options for recovery may be time-consuming if your database is large and your tolerance for missing data is low.