Article ID: 46091
Article Last Modified on 9/7/2004
APPLIES TO
- Microsoft SQL Server 4.21a Standard Edition
- Microsoft SQL Server 6.0 Standard Edition
- Microsoft SQL Server 6.5 Standard Edition
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 2000 Standard Edition
This article was previously published under Q46091
SUMMARY
The following information describes the effects of using SQL Server with a hard-disk controller card with caching memory on the controller card (up to 16 MB). Using this type of card provides the benefit of disk caching without requiring the memory tradeoffs normally associated with disk caching. The caching algorithm includes write caching and "elevator sorting" during write-back, when the data to be written is sorted to minimize head movement.
MORE INFORMATION
Question 1
Q. Are there any problems when using this type of controller caused by SQL Server assuming the server is on a UPS to avoid data corruption due to a power failure?
A. If the disk controller ever fails to write out data intended for the SQL Server transaction log, SQL Server recovery will not work correctly.
Question 2
Q. What effect does this type of caching controller have on the performance of SQL Server?
A. If the cache controller never fails to write to disk what has been entrusted to it (even if the keyboard sticks, the operating system crashes, or the hard disk crashes), there will be no problem. On the other hand, if the disk controller delays the writing of some SQL Server log data and chooses to physically apply other log data (because of the "elevator" sorting) and fails to write the rest of it for some reason, SQL Server has no way of knowing that some of the log is missing. A warm start or even rolling forward from a snapshot backup might not restore the database correctly. In the worst case, there would be no failure during recovery and data corruption would be discovered much later.
If the disk controller is a robust design, it will allow selective write-through. The device containing the SQL Server transaction logs must always be write-through. In addition, if automatic warm start is to work properly, all SQL Server devices must be flushed at checkpoint time. If the disk controller does not allow selective write-through, the only alternative is to make frequent backups and never rely on warm start or rolling the transaction log forward.
Question3
Q. Should SQL Server do the caching, or should the disk controller do the caching?
A. The answer depends on which method is faster. Our experiments have shown that the SQL Server cache is more efficient than the operating system disk cache. However, we have no way of knowing whether or not it is more efficient than the caching used by a particular type of disk controller. The SQL Server cache probably does not work as fast as a hardware cache; however, it has "inside knowledge" and can work smarter.
Run a representative workload with the SQL Server memory parameter set to the minimum value that will support the number of users required (with the disk controller cache active) for your installation. Then try it with the memory parameter set to that value plus the amount of RAM in the disk controller cache (with the disk controller cache deactivated). For a truly accurate comparison, the number of pages in the procedure cache should be equal in both cases. This takes some juggling because the size of the procedure cache is specified in percent of the total cache size, while the size of the total cache is specified by the memory parameter and the number of user connections parameter. The total cache size is whatever is left after the 42K per user connection has been carved out of the space specified by the memory parameter. The remainder is divided between the procedure and data page cache according to the procedure cache parameter.
Additional query words: GP-fault
Keywords: kbhardware KB46091
