ID Number: Q78941
1.11 4.20
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.
Additional reference words: LAN Manager OS/2