housekeeper free write percent

Summary information

Default value

1

Range of values

0–100

Status

Dynamic

Display level

Intermediate

Required role

System Administrator

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

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:

sp_configure "housekeeper free write percent", 5

When Adaptive Server has no user tasks to process, the housekeeper 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 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 task periodically flushes statistics to disk.

WARNING! Setting housekeeper free write percent to 0 disables flushing statistics to the systabstats table. This can seriously impair performance if statistics change significantly.

The default value allows the housekeeper 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 task, set the value of housekeeper free write percent to 0:

sp_configure "housekeeper free write percent", 0

You should 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.

If you disable the housekeeper tasks, be certain that statistics are kept current. Commands that write statistics to disk are:

You should 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 task to work continuously, regardless of the percentage of additional database writes, set housekeeper free write percent to 100:

sp_configure "housekeeper free write percent", 100

Use sp_sysmon to monitor housekeeper performance. See the Performance and Tuning Guide for more information.

It might also be helpful to look at the number of free checkpoints initiated by the housekeeper task. The Performance and Tuning Guide describes this output.