Configuring the housekeeper wash task

System administrators can use the housekeeper free write percent configuration parameter to control the side effects of the housekeeper wash task. This parameter specifies the maximum percentage by which the housekeeper wash task can increase database writes. Valid values are 0 – 100.

By default, housekeeper free write percent is set to 1, which allows the housekeeper wash task to continue to wash buffers as long as database writes do not increase by more than 1 percent. On most systems, work done by the housekeeper wash task at the default setting results in improved performance and recovery speed. Setting housekeeper free write percent too high can degrade performance. If you want to increase the value, increase by only 1 or 2 percent each time.

A dbcc tune option, deviochar, controls the size of batches that the housekeeper can write to disk at one time.

See “Increasing the Housekeeper Batch Limit” in Chapter 2, “Monitoring Performance with sp_sysmon,” in Performance and Tuning Series: Monitoring Adaptive Server with sp_sysmon.