Increasing the Housekeeper Batch Limit

The housekeeper wash task has a built-in batch limit to avoid overloading disk I/O for individual devices. By default, the batch size for housekeeper writes is 3. As soon as the housekeeper detects that it has issued 3 I/Os to a single device, it stops processing in the current buffer pool and begins checking for dirty pages in another pool. If the writes from the next pool are assigned to the same device, it moves on to another pool. Once the housekeeper has checked all of the pools, the housekeeper waits until the last I/O it has issued has completed, and then begins the cycle again.

The default batch limit is designed to provide good device I/O characteristics for slow disks. You may get better performance by increasing the batch size for fast disk drives. Set this limit globally for all devices on the server or to different values for disks with different speeds. You must reset the limits each time Adaptive Server is restarted.

This command sets the batch size to 10 for a single device, using the virtual device number from sysdevices:

dbcc tune(deviochar, 8, "10")

To see the device number, use sp_helpdevice, or:

select name, vdevno
from sysdevices

To change the housekeeper’s batch size for all devices on the server, use -1 in place of a device number:

dbcc tune(deviochar, -1, "5")

For very fast drives, setting the batch size as high as 50 has yielded performance improvements during testing.

You may want to try setting the batch size higher if: