The housekeeper process has a built-in batch limit to avoid overloading disk I/O for individual devices. By default, the batch size for housekeeper writes is set to 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 go to the same device, it moves on to another pool. Once the housekeeper has checked all of the pools, it 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. This limit can be set 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 this query:
select name, low/16777216 from sysdevices where status&2=2
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:
The average time for normal checkpoints is high
There are no problems with exceeding I/O configuration limits or contention on the semaphores for the devices
