ID Number: Q46091
1.00 1.10 1.11 4.20
OS/2
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:
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.
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, OS/2 GP
faults in the kernel, 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.
3. 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
OS/2 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 reference words: GP-fault 1.00 1.10 1.11 4.20