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:
- SQL Server 2000 I/O Basics:
- SQL Server I/O Basics Chapter 2:
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.
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.
Enhanced caching controller systems disable on-disk cache and provide a functional battery-backed caching solution. 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 multiple gigabytes of cache. These can significantly improve database performance.
I/O transfers that are performed without the use of a cache can be significantly longer because of hard drive spin rates, the mechanical time that is needed to move the drive heads, and other limiting factors. SQL Server installations are targeted at systems that provide caching controllers. These controllers disable the on-disk caches and provide stable media caches to satisfy SQL Server I/O requirements. They avoid performance issues related to disk seek and write times by using the various optimizations of the caching controller.
There are many types of subsystem implementations. RAID and SAN are two examples of these types of subsystem implementations. These systems are typically built with SCSI-based drives. There are several reasons for this. The following section generically describes high level drive considerations.
- Are typically manufactured for heavy duty use.
- Are typically targeted at multiuser, server-based implementations.
- Typically have better meantime to failure rates than other implementations.
- Contain sophisticated heuristics to help predict imminent failures.
- Are typically manufactured for light and medium duty use.
- Are typically targeted at single user-based applications.
- Some newer implementations contain sophisticated heuristics to help predict imminent failures.
There are situations in which a desktop-based drive or array is an appropriate low cost solution. For example, if you set up a read-only database for reporting, you should not encounter many of the performance factors of an OLTP database when disk caching is disabled.
Drive sizes continue to increase. Low cost, high capacity drives can be very appealing. But when you configure the drive for SQL Server and your business response time needs, you should carefully consider the following issues:
- Access path design
- The requirement to disable the on-disk cache
|Drive type System base||Comments|
|IDE and ATA|
Your SQL Server installation depends on the controller's ability to disable the on-disk cache and to provide a stable I/O cache. Writing data out of order to various disks is not a hindrance to SQL Server as long as the controller provides the correct stable media caching capabilities. The complexity of the controller design increases with advanced data security techniques such as mirroring.
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 SQLIOSim 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 details about SQLIOSim, see the following article in the Microsoft Knowledge Base:
Note If you have any question about the caching status of your disk drive, contact the manufacturer and obtain the appropriate utility or jumper settings to disable write caching operations.
Article ID: 234656 - Last Review: May 12, 2015 - Revision: 1