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:
update statistics
dbcc checkdb (for all tables in a database) or dbcc checktable (for a single table)
sp_flushstats
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:
Before dumping a database
Before an orderly shutdown
After rebooting, following a failure or orderly shutdown; in these cases, you cannot use sp_flushstats, you must use update statistics or dbcc commands
After any significant changes to a table, such as a large bulk copy operation, altering the locking scheme, deleting or inserting large numbers of rows, or a truncate table command
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.
