INF: LAN Manager Caching and SQL Server

  - Microsoft SQL Server version 4.2 for OS/2

SUMMARY

This article addresses some issues regarding software-based caching with LAN Manager and SQL Server.

SQL Server requires maximum available memory for its cache buffer system. For best results, a machine should be dedicated to SQL Server use, and any OS/2 file system caching intended for use by LAN Manager should be disabled. If this is not possible, make sure the machine has 16 MB of memory, and adjust the OS/2 cache size based on whether you want to favor LAN Manager or SQL Server performance. Periodically inspect the DISKCACHE or IFS lines in CONFIG.SYS to ensure the OS/2 cache size has not been altered by LAN Manager Setup. There is no need to be concerned about OS/2 software write caching because SQL Server writes though this cache.

MORE INFORMATION

OS/2 LAN Manager is essentially an application running on OS/2. It does not perform any caching of its own, but relies on the capabilities of the underlying file system. There are basically three file system environments on which an OS/2 application may find itself: FAT, HPFS-16, and HPFS386. Both HPFS and FAT have their own unique and separate caching subsystems.

FAT

On a FAT file system, caching is achieved by specifying the DISKCACHE= parameter in CONFIG.SYS. There is no write caching with this program, and therefore, none of the attendant data-integrity problems.

HPFS-16 and HPFS386

On an HPFS file system, caching is achieved by a combination of the CACHE.EXE program and the IFS line in CONFIG.SYS. HPFS has several sophisticated techniques designed to optimize the file I/O operations often associated with an I/O intensive program. A LAN Manager file server generally will benefit from HPFS; SQL Server generally will not. HPFS-16 and HPFS386 use the same disk format; however, with HPFS386, significant sections of file system code have been rewritten for a 32-bit, ring 0 environment to achieve higher performance. This performance is most useful for a file server, not a SQL Server. Write caching, or lazy writing, is available with HPFS.

SQL Server

SQL Server has its own built-in caching subsystem that is entirely separate from the FAT or HPFS file system caches. It is specially designed to work efficiently with SQL Server. It caches both reads and writes to the data device; however, for reasons of integrity, it does not cache writes to the log device. It is commonly referred to as the SQL Server buffer system. Buffers cannot be controlled as a separate resource, but are allocated from memory remaining after other SQL Server resources are serviced. On a 16 MB OS/2 machine with a minimal number of user connections, it can be expected that about 7 MB will be available for buffer space.

Possible Conflicts Between SQL Server and LAN Manager Caching

The first conflict that may arise is one of memory. Under OS/2 versions 1.x, the maximum physical memory available is 16 MB, which must be shared between LAN Manager and SQL Server. The LAN Manager Setup program will automatically alter CONFIG.SYS to allocate a substantial amount of cache for LAN Manager use. If SQL Server is to be run on the same machine, the DISKCACHE (FAT) or IFS (HPFS) line in the CONFIG.SYS file should be adjusted downward to a compatible value. The exact amount allocated to LAN Manager versus SQL Server will depend on an individual estimation of the priority of LAN Manager versus SQL Server performance. The more memory given to file system caching, the less is available for SQL Server. For best SQL Server performance, a dedicated machine should be used, with any OS/2 file system cache disabled.

Even after the cache parameters in CONFIG.SYS are adjusted to an acceptable value, this must be monitored periodically. If the LAN Manager Setup program is ever run to change any file server parameters, it will often alter the cache setting to a higher value.

The second apparent conflict that may arise is one of write caching. This can only occur with HPFS. To provide best performance, HPFS can optionally perform lazy writing, or write caching. For a large class of programs, reads will typically outnumber writes by about 5 to 1. For this reason, the bulk of performance improvement is usually seen with a read cache, and a write cache provides only incremental improvement beyond this. For a few programs, there may be a genuine advantage to be gained from write caching.

For integrity reasons, SQL Server must be able to reliably flush cached writes to disk. For this reason, SQL Server uses the OPEN_FLAGS_WRITE_THROUGH parameter on DosOpen() to ensure that any possible write caching is circumvented. HPFS provides a special second entry point to honor this parameter. Because of this, it is not essential to manually disable lazy writes in CONFIG.SYS, although in a production environment it is generally viewed as a prudent practice.