INF: Using Disk Drive Caching with SQL Server

ID: Q234656

The information in this article applies to:

SUMMARY

A database system is first and foremost responsible for the accurate storage and retrieval of data, even in the event of unexpected system failures.

The system must guarantee the atomicity and durability of transactions, while accounting for current execution, multiple transactions, and various failure points. This is often referred to as the ACID (Atomicity, Consistency, Isolation, and Durability) properties.

This article addresses the implications of a disk drive caches. It is recommended that you read the following articles in the Microsoft Knowledge Base for further clarification on caching and alternate failure mode discussions:

Q86903 INF: SQL Server and Caching Disk Controllers
Q46091 INF: Using Hard Disk Controller Caching with SQL Server
Q230785 INF: SQL Server 7.0 Logging and Data Storage Algorithms Extend Data Reliability


MORE INFORMATION

SQL Server 7.0, earlier versions of SQL Server, and many mainstream database products on the market today use the Write-Ahead Logging (WAL) protocol.

Write-Ahead Logging (WAL) Protocol
The term protocol is an excellent way to describe WAL. It is a specific and defined set of implementation steps necessary to ensure that data is stored and exchanged properly and can be recovered to a known state in the event of a failure. Just as a network contains a defined protocol to exchange data in a consistent and protected manner, so too does the WAL describe the protocol to protect data.
All versions of SQL Server open the log and data files using the Win32 CreateFile function. The dwFlagsAndAttributes member includes the FILE_FLAG_WRITE_THROUGH option when opened by SQL Server.
FILE_FLAG_WRITE_THROUGH
This option instructs the system to write through any intermediate cache and go directly to disk. The system can still cache write operations, but cannot lazily flush them.

The FILE_FLAG_WRITE_THROUGH option ensures that when a write operation returns successful completion the data is correctly stored in stable storage. This aligns with the Write Ahead Logging (WAL) protocol specification to ensure the data.
Many disk drives (SCSI and IDE) contain onboard caches of 512 KB, 1 MB, or larger. However, the drive caches usually rely on a capacitor and not a battery-backed solution. These caching mechanisms cannot guarantee writes across a power cycle or similar failure point. They only guarantee the completion of the sector write operations. As the drives continue to grow in size, the caches become larger, and they can expose larger amounts of data during a failure.

Many vendors provide functional battery-backed caching solutions. These caches can maintain the data in the cache for several days and even allow the caching card to be placed in a second computer. When power is properly restored, the unwritten data is completely flushed before any further data access is allowed. Many of them allow percentage of read versus write cache to be established for optimal performance. Some contain large memory storage areas. In fact, for a very specific segment of the market, some hardware vendors provide high-end battery-backed disk caching systems with 6 GB or more of cache. These can significantly improve database performance.

I/O transfers without the use of a cache can be significantly longer due to the mechanical time needed to move the drive heads, spin rates, and other limiting factors.

To fully secure your data, you should ensure that all data caching is properly handled. In many situations, this means you must disable the write caching of the disk drive.

NOTE: Ensure that any alternate caching mechanism can properly handle multiple types of failure.

Microsoft has performed testing on several SCSI and IDE drives using the SQL70IOStress utility. This utility simulates heavy asynchronous read/write activity to a simulated data device and log device. Test performance statistics show the average write operations per second between 50 and 70 for a drive with disabled write caching and an RPM range between 5,200 and 7,200.

For additional information and complete details on SQL70IOStress, please see the following article in the Microsoft Knowledge Base:
Q231619 INF: SQL70IOStress Utility to Stress Disk Subsystem
Many PC manufactures (for example, Compaq, Dell, Gateway, or HP) order the drives with the write cache disabled. However, testing shows that this may not always be the case so you should always test it completely.

NOTE: If there is any question as to the caching status of your disk drive, please contact the manufacturer and obtain the proper utility or jumper settings to disable write caching operations.

Additional query words: cacheing cach cache 


Keywords          : 
Version           : winnt:7.0
Platform          : winnt 
Issue type        : kbinfo

Last Reviewed: July 7, 1999