FIX: SQL Server 2000 may be more aggressive with Lazy Writes than SQL Server 7.0

Under the following conditions Lazy Writes activity in SQL Server 2000 may be much higher than previous versions, which may cause other side effects:

• Client activity repopulates the buffer cache with pages that need to be written to disk.
• The layout of the pages that need to be written from the buffer cache to disk is in a contiguous manner on the disk subsystem.
• The Lazy Write activity occurs close to the time that a checkpoint starts on a database.
• The checkpoint occurs on a database that contains the pages that need to be written to disk.

The side effects that may occur are:

• A short-term increase in the amount of the Pool NonPaged bytes counter under the Memory Object from normal levels as seen in the System Monitor utility, which you can attribute to the SQL Server process.
• A short-term increase in the amount of the Lazy Writes per second counter under the SQL Server:Buffer Manager from normal levels as seen in the System Monitor utility.
• A short-term increase in the amount of the Page Write per second counter under the SQL Server:Buffer Manager from normal levels as seen in the System Monitor utility.
• A delay in the physical disk read and write IO requests to hardware connected to the same disk IO controller.

Note The System Monitor utility is a component of Microsoft Windows 2000 and you may detect similar side effects with Microsoft Windows NT 4.0 with the Performance Monitor utility. Additional counters may also see short term increases at the same time.

To resolve this problem, obtain the latest service pack for SQL Server 2000. For more information, click the following article number to view the article in the Microsoft Knowledge Base: NoteThe following hotfix was created before the release of Microsoft SQL Server 2000 Service Pack 3.

The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.Note Because of file dependencies, the most recent hotfix or feature that contains the files may also contain additional files.

Note This fix alone does not limit the amount of Lazy Write activity. Microsoft has added a new trace flag, 809, to limit the amount of Lazy Write activity. To add trace flag 809 as a SQL Server startup parameter:

1. In SQL Server Enterprise Manager, right-click the server name, and then click Properties on the shortcut menu.
2. On the General tab, click Startup Parameters.
3. Add a new parameter as -T809.

This trace can also be dynamically enabled by using the DBCC TRACEON(-1,809) command.

To work around this problem you can either:

• Break down large updates and large deletes into smaller updates and deletes, and then add delays between subsequent pieces.
• Increase the disk IO performance by increasing the number of disks in the RAID set, if applicable.
• If possible, separate the database into file groups and/or files to allow SQL Server to spread the disk IO across multiple disk IO controllers.

Microsoft has confirmed that this is a problem in Microsoft SQL Server 2000.
This problem was first corrected in Microsoft SQL Server 2000 Service Pack 3.

The removal of the sp_configure stored procedure option max async io in SQL Server 2000, allows much greater levels of Lazy Write activity. The new trace flag 809 limits the amount of Lazy Writes that can occur in a short period of time.