housekeeper free write percent

housekeeper free write percent specifies the maximum percentage by which the housekeeper wash task can increase database writes.

Summary Information

Default value

1

Range of values

0–100

Status

Dynamic

Display level

Intermediate

Required role

System administrator

Configuration group

SQL Server Administration

For example, to stop the housekeeper task from working when the frequency of database writes reaches 5 percent above normal, set housekeeper free write percent to 5.

When SAP ASE has no user tasks to process, the housekeeper wash task automatically begins writing changed pages from cache to disk. These writes result in improved CPU utilization, decreased need for buffer washing during transaction processing, and shorter checkpoints.

In applications that repeatedly update the same database page, the housekeeper wash may initiate some unnecessary database writes. Although these writes occur only during the server’s idle cycles, they may be unacceptable on systems with overloaded disks.

The table and index statistics that are used to optimize queries are maintained in memory structures during query processing. When these statistics change, the changes are not written to the systabstats table immediately, to reduce I/O contention and improve performance. Instead, the housekeeper chores task periodically flushes statistics to disk.

The default value allows the housekeeper wash task to increase disk I/O by a maximum of 1 percent. This results in improved performance and recovery speed on most systems.

To disable the housekeeper wash task, set the value of housekeeper free write percent to 0.

Set this value to 0 only if disk contention on your system is high, and it cannot tolerate the extra I/O generated by the housekeeper wash task.

If you disable the housekeeper tasks, keep statistics current. Commands that write statistics to disk are:
Run one of these commands on any tables that have been updated since the last time statistics were written to disk, at the following times:

To allow the housekeeper wash task to work continuously, regardless of the percentage of additional database writes, set housekeeper free write percent to 100.

Use sp_sysmon to monitor housekeeper performance. See the Performance and Tuning Series: Monitoring SAP Adaptive Server with sp_sysmon.

You might also want to look at the number of free checkpoints initiated by the housekeeper task. The Performance and Tuning Series: Basics describes this output.