Memory Management in SQL Server 6.5

In Microsoft SQL Server 6.5, memory is segmented and manually managed. The database administrator must first determine how much memory SQL Server should use versus the operating system. For example, with 256 MB of memory, SQL Server may get 200 MB and leave 56 MB for the operating system. This in itself is an art, not a science. It is very difficult to plan how much the database alone needs, much less plan what the operating system and other applications, such as Web servers, running on the same computer might need. Use of memory is not stagnant; it is possible that SQL Server may need more memory from 8 A.M. to 5 P.M., and the operating system may need more memory from 5 P.M. to 8 A.M. to run nightly batch work. Changing the memory configuration requires a shutdown and startup of SQL Server 6.5.

After memory is allocated to SQL Server 6.5, it is segmented into four sections: static structures, data cache, stored procedure cache, and dynamic allocations (a subset of data cache).

Static structures are the preallocation of SQL Server memory to SQL Server components such as user connections, locks, open objects, and worker thread. The static structure memory is allocated upon SQL Server startup. The dynamic structures are allocations of memory for users, locks, and objects added above and beyond the static structure allocation and take memory away from the data cache. The rest of the memory is divided between the data cache and the stored procedure cache. The SQL Server 6.5 procedure cache configuration option sets the percentage of available memory for the procedure cache, and the remainder is assigned to data cache.

As an example of SQL Server 6.5 memory allocation, assume that the system has 256 MB. The SQL Server memory option allocates 200 MB to SQL Server, leaving 56 MB for Windows NT. Of the 200 MB of SQL Server memory, static structures require 10 MB, leaving 190 MB. The procedure cache is set to 30 percent; therefore, stored procedure memory receives 57 MB (190 * .3) and data cache receives 133 MB (190 - 57). Any further dynamic allocations are taken from the data cache.

SQL Server 6.5 memory manages data with a LRU (least recently used) and MRU (most recently used) link list. Data in memory that is active (being read or written to) links to the MRU part of the link list. Data in memory buffers that has not been accessed for a while is shuffled to the LRU part of the link list. The MRU/LRU link list is constantly being modified.