enable housekeeper GC configures the housekeeper task.
|
Summary Information |
|
|---|---|
|
Default value |
1 (on) |
|
Range of values |
0 – 5 |
|
Status |
Dynamic |
|
Display level |
Intermediate |
|
Required role |
System administrator |
|
Configuration group |
SQL Server Administration |
The housekeeper garbage collection task performs space reclamation on data-only-locked tables. When a user task deletes a row from a data-only-locked table, a task is queued to the housekeeper to check the data and index pages for committed deletes.
See Performance and Tuning Series: Basics > Using Engines and CPUs.
0 – disables the housekeeper garbage collection task, but enables the delete command’s lazy garbage collection. You must use reorg reclaim_space to deallocate empty pages. This is the cheapest option with the lowest performance impact, but it may cause performance problems if many empty pages accumulate. SAP recommends that you do not use this value.
1 – enables lazy garbage collection for the housekeeper garbage collection task and the delete command. If more empty pages accumulate than your application allows, consider options 4 or 5. You can use the optdiag utility to obtain statistics of empty pages.
2 – reserved for future.
3 – reserved for future.
4 – enables aggressive garbage collection for the housekeeper garbage collection task and the delete command. This option is the most effective, but the delete command is expensive. This option is ideal if the deletes on your DOL tables are in a batch.
5 – enables aggressive garbage collection for the housekeeper, and lazy garbage collection for the delete command. This option is less expensive for deletes than option 4. This option is suitable when deletes are caused by concurrent transactions
sp_sysmon reports on how often the housekeeper garbage collection task performed space reclamation and how many pages were reclaimed. See the Performance and Tuning Series: Monitoring SAP Adaptive Server with sp_sysmon.