In SQL Server, the database-level TARGET_RECOVERY_TIME setting controls how often checkpoints occur and therefore how SQL Server flushes dirty pages from memory to disk. The default value is 0, which means “use traditional automatic (indirect) checkpoints”. Changing it to 60 seconds (or any value greater than 0) switches to frequent, timed checkpoints.
Key Differences
| Aspect | Target Recovery Time = 0 (default) | Target Recovery Time = 60 seconds |
|---|---|---|
| Checkpoint behavior | Automatic checkpoints based on log growth and server-level recovery interval | Timed checkpoints roughly every minute |
| Frequency | Infrequent (minutes apart) | Very frequent (approximately once per minute) |
| Write I/O pattern | Bursty with occasional large spikes | Smooth and continuous |
| Crash recovery time | Depends on server recovery interval (default approximately 1 minute of redo work) | Guaranteed = 60 seconds |
| Log truncation | Only at checkpoint | Approximately every minute |
Real-World Performance Impact
| Workload Type | Typical Effect After Setting to 60 Seconds |
|---|---|
| High-concurrency OLTP | P99 latency often drops 30–80 %; average latency may rise slightly |
| Heavy batch/update jobs | Throughput can decrease 5–20 % due to less write batching |
| Slow or inconsistent storage | Overall performance frequently improves by avoiding checkpoint storms |
| Read-mostly or well-tuned systems | Little change or small throughput penalty (5–10 %) |
How to Change It
-- Set to 60 secondsALTER DATABASE YourDatabase SET TARGET_RECOVERY_TIME = 60 SECONDS;-- Verify the settingSELECT name, target_recovery_time_in_seconds FROM sys.databases WHERE name = 'YourDatabase';
When to Use Each Setting
- Use 30–90 seconds when you need predictable low P99 latency and fast guaranteed recovery (most modern OLTP systems).
- Keep at 0 only when maximizing raw throughput is the top priority and your storage can handle large I/O bursts (rare today).
- Strongly consider 60 seconds on systems with slower disks, high memory, or any latency-sensitive application.
Bottom line: On almost all current production workloads, setting TARGET_RECOVERY_TIME = 60 SECONDS (or similar) delivers more consistent performance and much faster recovery with only a minor throughput trade-off. The old default of 0 was reasonable twenty years ago on small servers, but rarely is the best choice on today’s hardware and workloads.
Need help with this or anything relating to SQL Server? The team at Stedman Solutions can help. Find out how with a free no risk 30 minute consultation with Steve Stedman.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!