SQL Server lazy writer helps produce free buffers, which are 8-KB data cache pages without any data contained in them. As lazy writer flushes each 8-KB cache buffer out to disk, it initializes the cache page identity so that other data can be written into the free buffer. Lazy writer produces free buffers during periods of low disk I/O, so disk I/O resources are readily available for use and there is minimal impact on other SQL Server operations.
SQL Server 7.0 automatically configures and manages the level of free buffers. Monitor the SQL Server: Buffer Manager - Free Buffers object to ensure that the free buffer level remains steady. Lazy writer ensures that the level of free buffers keeps up with the user demand for free buffers. The SQL Server: Buffer Manager - Free Buffers object should not drop to 0 because this indicates there were times the user load demanded a higher level of free buffers than the SQL Server lazy writer was able to provide.
If the lazy writer cannot keep the free buffer steady, or at least above 0, it might mean the disk subsystem cannot provide lazy writer with the disk I/O performance that it needs to maintain the free buffer level (compare drops in free buffer level to any disk queuing to confirm there is a disk subsystem problem). One solution to the disk queuing problem is to add more physical disk drives (also called spindles) to the database server disk subsystem to provide more disk I/O processing power. The SQL Server: Buffer Manager - Lazy Writes/sec object indicates the number of 8-KB pages written to disk by lazy writer.
Monitor the current level of disk queuing in Performance Monitor by looking at the counters for (logical or physical) Disk: Average Disk Queue or Current Disk Queue and ensure the disk queue is at a level less than 2 for each physical drive associated with any SQL Server activity. For database servers that employ hardware RAID controllers and disk arrays, divide the number reported by (logical or physical) disk counters by the number of actual hard disk drives associated with that logical drive letter or physical hard disk drive number reported by the Windows NT Disk Administrator program. Windows and SQL Server are unaware of the actual number of physical hard disk drives attached to a RAID controller. You should know the number of drives associated with RAID array controller to interpret the disk queue numbers Performance Monitor reports.
Adjust lazy writer disk I/O request behavior with the max async IO option, which controls the number of 8-KB disk write requests that SQL Server (including requests coming in from lazy writer, checkpoint, and the worker threads) can simultaneously submit to the Windows operating system and in turn, to the disk I/O subsystem. If disk queuing occurs at unacceptable levels, decrease the level of the max async IO option. If the currently configured level of the max async IO option must be maintained, add more disks to the disk subsystem until disk queuing reaches acceptable levels.
For more information, see SQL Server Books Online.