MORE INFORMATION
SQL Server automatically issues checkpoints to reduce the
SQL Server recovery time and to permit log space reuse (truncation). In
extremely rare situations, you must adjust the default checkpoint interval. You
can set the default checkpoint interval by using the
sp_configure stored procedure setting named
recovery interval. You can use trace flag 3505 to adjust the interval by forcing
the automatic checkpoint processing to be completely disabled.
Microsoft recommends that you retain the default setting for the recovery
interval and leave the trace flag disabled unless you test extensively and
initiate appropriate recovery planning. For high availability systems, such as
clusters, Microsoft recommends that you do not change the recovery interval
because it may affect data safety and availability.
If you use I/O
mirroring and similar facilities, Microsoft recommends that you do not change
the recovery interval. These systems are designed to work around the physical
I/O layers. Increasing the checkpoint reduces the effectiveness of these
designs.
Improvements
Microsoft continues to make scalability improvements to the
checkpoint process.
For additional information about one
scalability improvement that Microsoft has instituted, click the following
article number to view the article in the Microsoft Knowledge Base:
815056
FIX: The Checkpoint Process Can Delay SQL Server Database Activity and Does Not Yield Scheduler Correctly Causing Error: 17883 to Occur
Because of these improvements, recovery
interval adjustments and the use of trace flag 3505 are typically not
necessary. Make sure that you have installed the latest version of SQL Server
before you make any changes to the recovery interval.
When to Use Trace Flag 3505
You can use trace flag 3505 to make sure that a checkpoint does
not occur at a critical time for an application. You may want to control when
the checkpoint occurs because the checkpoint may cause more I/O load on the
system. SQL Server automatic checkpoints are designed to speed up disk writes
and not to increase the I/O usage. However, in certain exceptional situations,
the automatic checkpoints that SQL Server issues may affect the application
performance because of the increased I/O load. Some applications may have
strict response time requirements at specific times, and the impact of the
checkpoint is significant. In these situations, you may want to control when
the checkpoints occur.
If you experience these effects because of
when the checkpoints occur, follow these steps:
- Make sure that you have installed SQL Server 2000 Service
Pack 3 (SP3) and any additional hotfixes.
- Review the I/O path to ensure maximum throughput.
- Review the database layout as it relates to the I/O
paths.
Usage Considerations
Setting trace flag 3505 disables automatic checkpoints. Setting
trace flag 3505 may increase recovery time and can prevent log space reuse
until the next checkpoint is issued. Make sure to issue manual checkpoints on
all read/write databases at appropriate time intervals.
Note Setting trace flag 3505 disables automatic checkpoints across the
server for all databases. After you set trace flag 3505, you must issue
checkpoint commands for all the databases where updates must occur. Trace flag
3505 does not prevent the internal checkpoints that are issued by certain
commands, such as
BACKUP.
Warning If you increase the checkpoint interval, the recovery work load
and associated time increases. For some query patterns, the recovery time may
be longer than the original transaction time. For example, if you set the
recovery interval to 5 minutes, some query patterns can cause recovery times
that are up to 15 minutes. This result may not be conducive to your overall
system goals.
Like all other trace flags, trace flag 3505 may not be
supported in future releases of SQL Server. Although you can set trace flag
3505 in later versions of SQL Server, it may not disable automatic checkpoints.
Use Trace Flag 3505
You can set trace flag 3505 as a startup parameter or by using the
isql command-line utility, the
osql command-line utility, or SQL Query Analyzer:
- To set the trace flag as a startup parameter, run the
following command from a command prompt:
sqlservr.exe -T3505
- To enable the trace flag from isql, osql, or SQL Query Analyzer, run the following code:
DBCC TRACEON( 3505 )
- To disable the trace flag from isql, osql, or SQL Query Analyzer, run the following code:
DBCC TRACEOFF( 3505 )
Note You can set trace flag 3502 to include a message in the log at
the beginning and ending of each checkpoint. This trace flag may be useful if
you use trace flag 3505.