Several configuration parameters impact truncation performance.
- To improve the performance of delete statements, start the RAPCache database with the trace flag 4816.
- Setting the parameter enable housekeeper GC to 5 helps to effectively reclaim space occupied by the index and data pages that are affiliated with the data deleted by truncation rules. Set this value only if the RAPCache database has been configured to use multiple engines, and if a user-defined execution class with dedicated engines has been created and associated with the truncation activity. Otherwise, doing so may have an unfavorable impact on the overall performance of the RAPCache database.
See Adaptive Server Enterprise Performance and Tuning Series: Basics for additional information about these parameters.