INF: Using Hard Disk Controller Caching with SQL Server

  - Microsoft SQL Server version 4.2 for 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.