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.
update statistics
dbcc checkdb (for all tables in a database) or dbcc checktable (for a single table)
sp_flushstats
Before dumping a database
Before an orderly shutdown
After restarting, 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 performing a truncate table command
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.