INF: Tuning Memory for SQL Server on Non-LAN Manager Platforms

ID: Q100946

The information in this article applies to:

Microsoft SQL Server for OS/2, version 4.2

SUMMARY

Microsoft SQL Server for OS/2 can be installed on several different network operating systems and on several versions of OS/2. However, the installation guide deals primarily with SQL Server configuration parameters on LAN Manager networks. While each user's SQL Server setup will be affected by their own application environment, there are some general guidelines for the configuration of memory on networks other than Microsoft LAN Manager and on OS/2 versions other than Microsoft version 1.3.

MORE INFORMATION

It is very difficult with the OS/2 memory architecture for even the author of a program to determine his program's consumption of physical random access memory (RAM). This is because all OS/2 programs exist in a 512-megabyte (MB) virtual address space, and there is not an operating system application programming interface (API) that exists for querying either the amount of total RAM in the computer, or the amount of RAM consumed by a program. The only way to come close to determining the physical memory consumption of a program is to keep a record of all memory allocation requests. This article addresses ways to maximize SQL Server's use of memory in two ways:

Freeing up as much memory on the OS/2 system as can be made available

Guidelines for determining a safe level to set the SQL Server memory parameter.

A safe level is one where SQL Server gets the most out of available memory without causing OS/2 to get into a situation where it is frequently swapping chunks of memory to the hard disk and thereby causing a decrease in performance.

Freeing Available Memory

Chapter 2 of the SQL Server "Installation Guide" gives several pointers for freeing available memory. You should refer to this for tips on how to disable read/write caching, turn off the MS-DOS session, and to set OS/2 swapping options.

The preferred file system for SQL Server on OS/2 is FAT (File Allocation Table) instead of HPFS (High-Performance File System). The two greatest advantages to FAT are that it allows SQL Server to have access to more of the system's memory because FAT uses less than HPFS; and in the event of disk problems, you can restart from a MS-DOS disk and use any of the available MS-DOS utilities available to attempt hard disk repairs in the event of physical corruption on a device. Such utilities are much more widely available for FAT than for HPFS.

All of the following recommendations assume the FAT file system is being used. If you are using HPFS, or HPFS and FAT together, you should subtract an additional 1 to 2 megabytes or RAM from these recommendations.

The standard installation of MS OS/2 version 1.3 will include several device drivers for CD-ROM players that most SQL Server users will not need to use. These lines can be commented out of the CONFIG.SYS file.

This includes the following programs:
```
      DENON.VSD
      STDCDROM.VSD
      CDROM.TSD
      CDFS.IFS
 
```

Configuring SQL Server's Memory

Each user's particular environment determines how much memory they can configure SQL Server to use. If you configure SQL Server to use too little, then you may see decreased client performance. If you configure SQL Server to use too much, then you may experience connectivity problems, or have the operating system go into a thrashing mode as large parts of the network and SQL Server software end up being swapped to disk.

A good way to determine if you have over-configured your SQL Server memory is to monitor the size of the SWAPPER.DAT file. The directory where this file is located is pointed to by the SWAPPATH= entry in the OS/2 CONFIG.SYS file. If this file is consistently getting larger than 11 or 12 MB, then you are probably doing too much swapping and it could be affecting your performance.

On Novell networks running the Netware requester for OS/2 version 1.3, with Microsoft or IBM OS/2 version 1.3, with 16 MB of RAM on the computer, with the maximum available memory freed as described above, and running FAT with no MS-DOS compatibility box enabled, the suggested maximum amount of memory to configure SQL Server to use is about 12 MB. If using the Microsoft's "SQL Server Network Integration Kit for Novell Netware Networks," then about 11 MB is the suggested maximum.

On Banyan networks running VINES version 4.10 or later, with Microsoft or IBM OS/2 version 1.3, with 16 MB of RAM on the computer, with the maximum available memory freed as described above, and running FAT with no MS-DOS compatibility box enabled, the suggested maximum amount of memory to configure SQL Server to use is about 12 MB. If using the Microsoft's "SQL Server Network Integration Kit for Banyan VINES Networks," then about 11 MB is the suggested maximum.

Microsoft is currently supporting SQL Server version 4.2 on IBM OS/2 version 2.0 on Novell and IBM LAN Server networks. At this time, we are still testing on IBM OS/2 version 2.1, but have clients who use it, and the memory configuration settings will probably be similar to OS/2 version 2.0.

OS/2 version 2.0 can physically address more than 16 MB of physical RAM. Microsoft SQL Server version 4.2 can address up to 64 MB of memory when running on OS/2 version 2.0. However, Microsoft testing indicates substantially more RAM may be required on OS/2 version 2.0 to provide the same level of performance as OS/2 1.3. Furthermore, not all hardware platforms may be capable of using greater than 16 MB under OS/2 2.0. Contact IBM and your hardware vendor for more details.

For example, on a Novell network with the OS/2 version 2.0 requester, with 16 MB of RAM, and on a dedicated SQL Server machine, the suggested maximum amount of memory to configure SQL Server to use is about 8 MB. If this computer will also be used as a user's workstation, you will need to configure SQL Server to use less memory.

In a LAN Server environment with 16 MB of RAM on the computer, SQL Server can be configured to use about 8 MB of memory. As more RAM is added to the machine, SQL Server can be configured to use parts of this, but in many cases best performance can be gained by also leaving more RAM for the operating system. Experimentation will be required to determine the most effective setting at various physical memory capacity. The overall goal will be to increase SQL Server's memory allocation up to the point where excessive OS/2 version 2.0 paging file growth occurs.


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: March 15, 1999