Memory Management in SQL Server 7.0

Microsoft SQL Server 7.0 has dramatically improved the way memory is allocated and accessed. Unlike SQL Server 6.5 in which memory is managed by the database administrator with configuration settings, SQL Server 7.0 has a memory manager to eliminate manual memory management.

SQL Server 6.5 has a memory configuration option that allocates a fixed amount of memory on startup. If the parameter is set too high, SQL Server cannot start. When SQL Server 7.0 starts, its dynamic memory allocation determines how much memory to allocate based on how much memory Windows NT and Windows NT applications are using. For example, assume that Windows NT has a total of 512 MB of memory. When SQL Server starts up, Windows NT and the applications running on Windows NT are using 72 MB of memory. SQL Server uses available memory, leaving 5 MB free. Therefore, SQL Server uses 435 MB of memory (512 MB total - 72 MB for active Windows NT - 5 MB of free memory = 435 SQL Server MB memory). If another Windows NT application is started and uses the 5 MB of free space, SQL Server proactively releases memory to ensure that 5 MB of free space always remains free. Conversely, if Windows NT releases memory so that the free memory is more than 5 MB, SQL Server uses that memory and uses it for database operations.

This dynamic memory algorithm has many advantages. You no longer need to guess the correct memory percentages for Windows NT, Windows NT applications, and SQL Server. You can also avoid Windows NT paging during times of heavy Windows NT usage, and you can use Windows NT free memory during times of light Windows NT usage.

The memory algorithm for SQL Server 7.0 Desktop Edition works differently. Rather than taking memory when it is free, it gives memory back to the operating system when it is not needed. This is because it is more likely that the Desktop Edition is running other applications.

min server memory Option

In SQL Server 7.0, the min server memory configuration option ensures that SQL Server starts with a minimum value and does not release memory below this value. SQL Server never goes below the min server memory value. If the value is set at 0, SQL Server memory manager manages this for you. The value of this option should be set based on the size and activity of the computer running SQL Server.

max server memory Option

The SQL Server 7.0 max server memory configuration option determines the maximum amount of memory SQL Server can allocate at startup and during execution. A max server memory value of 0 indicates that there is no maximum and enables dynamic memory to determine the value. If max server memory is set to a value, SQL Server does not allocate memory above that value. Set the max server memory option if multiple applications are running on Windows NT and you want to guarantee these applications the appropriate amount of memory.

Recommendations:

• Do not set the min server memory and max server memory options to the same value in order to revert to SQL Server 6.5 behavior. SQL Server 7.0 dynamic memory allocation gives you the best overall performance over time. If despite this recommendation, you still want to fix the memory allocation of SQL Server 7.0 as you did in SQL Server 6.5, set the min server memory and the max server memory parameters to the same value.
• When running multiple applications on the same Windows NT server with SQL Server, set the min server memory option to a reasonable value to ensure that Windows NT does not request too much memory, thus degrading SQL Server performance.
• Set the max server memory option only if you want to guarantee that Windows NT application running on the same server gets a specified amount of memory.

The SQL Server 7.0 data access method uses a clock algorithm. Unlike the MRU/LRU link list in which pages are constantly linked and relinked, SQL Server 7.0 sweeps through the buffers and keeps two values in each buffer: Last Touched and Last Checked. Last Checked is the last time the clock has checked on this buffer. Last Touched is the last time this buffer page was active. Pages are marked as part of the free buffer list when Last Touched has not changed in some time. If Last Touched has changed (for example, data was read or written to this buffer page), this page stays out of the free buffer list for a longer period of time. The reason the clock algorithm is superior to the SQL Server 6.5 LRU/MRU link list is that links do not have to be updated, only the Last Touched and Last Checked values.

Changes to sp_configure

Because SQL Server 6.5 does not have a true memory manager like SQL Server 7.0, you can set several sp_configure options in SQL Server 6.5 to improve memory performance. These configuration options include: free buffers, hash buckets, locks, logLRU buffers, max lazywrite IO, max worker threads, memory, open databases, open objects, procedure cache, recovery interval, set working set size, sort pages, and tempdb in RAM. SQL Server 7.0 minimizes the amount of tuning needed by the database administrator for system configurations. Most of the system configuration parameters in SQL Server 7.0 are self-tuning.

In SQL Server 6.5, the free buffers option determines the size of the free buffer list, which is 5 percent of SQL Server memory by default. The free buffers option is no longer tunable in SQL Server 7.0. With dynamic memory set on and memory size changing dynamically, it would be difficult for any database administrator to estimate the right size for free buffers.

The value of the hash buckets option is a prime number used as input into a memory-hashing algorithm. The larger the memory allocation, the larger the hash bucket value should be. Because dynamic memory changes the size of SQL Server memory, this option is now set automatically by the memory manager and no longer appears as a configurable option in SQL Server 7.0.

Locks are a perfect example of the power of the SQL Server 7.0 memory manager. In SQL Server 6.5, you must specify enough locks to prevent running out. If you run out of locks, the active thread requesting locks terminates. If you specify too many locks, the memory is preallocated and wasted. Each lock requires approximately 30 to 60 bytes of memory. In SQL Server 7.0, you can specify the configured value as 0 to allow SQL Server to manage it. In the case of locks set to 0, SQL Server 7.0 memory manager ensures you never run out. It adds more locks as you need them.

Recommendation:

• Set the locks option to 0.

The max lazywrite option is set by the database administrator to help the SQL Server 6.5 memory determine how often to flush the cache. The SQL Server 7.0 memory manager manages the lazywrite process automatically, and the option has been removed from the sp_configure stored procedure.

The max worker threads option indicates the amount of internal SQL Server threads used by SQL Server connected users to get SQL Server resources. The default setting for this option is 255, slightly too high. Each worker thread that is allocated, but not active, uses unnecessary resources such as memory that can be used better by other operations. This configuration value should be similar to the concurrent user connections to SQL Server, but cannot exceed 1024.

In SQL Server 6.5, the memory option indicates a fixed allocation of memory for SQL Server. SQL Server 7.0 memory allocation is dynamic at start time. SQL Server allocates as much as memory as is available, reserving 5 MB as free memory.

The open databases option, specifying the number of active databases SQL Server allows at any one time, has been removed for SQL Server 7.0. There is no longer a limit.

The open objects option indicates the number of actively open objects SQL Server allows at any point in time. Open objects include all tables, views, stored procedures, extended stored procedures, triggers, rules, defaults, and constraints. If you run out of open objects, the active thread requesting an object terminates. In SQL Server 6.5, this option should be set high enough to avoid running out, but not so high as to waste preallocated memory for objects. Each object in SQL Server 6.5 uses 240 bytes of memory. In SQL Server 7.0, you can set open objects to 0 so that SQL Server manages objects for you and dynamically adds them when you need them.

Recommendation:

• Set the open objects option to 0.

In SQL Server 6.5, the procedure cache option indicates the amount of SQL Server memory that is reserved to store the active running stored procedures. You must ensure that you reserve enough memory for the stored procedure usage, but at the same time not so much as to waste memory. This option is no longer valid in SQL Server 7.0. Stored procedure memory shares the same buffer pool as data and is managed by the SQL Server memory manager.

Use the set working set size option to reserve physical memory space for SQL Server that is equal to the memory setting. The memory setting is automatically configured by SQL Server based on the workload and available resources. It dynamically varies between min server memory and max server memory. Setting the set working set size option means that Windows NT does not swap out SQL Server pages, even if they can be used more readily by another process when SQL Server is idle.

Do not set set working set size if you allow SQL Server to use memory dynamically. Before setting set working set size to 1, set both min server memory and max server memory to the same value, which is the amount of memory you want SQL Server to use. Dynamic memory is a powerful feature in SQL Server 7.0 and it is recommended that you use it in almost all cases.

Recommendation:

• Leave set working set size set to 0 to enable SQL Server to dynamically manage the memory allocation.

In SQL Server 6.5, the sort pages option indicates the amount of memory allocated per user for sorting operations. The larger the allocation, the faster the sort performs. This option was used for all sorting including query sorts and create index sorts. The query processor now manages queries, and index sorts are managed by the new index create memory configuration option. In SQL Server 7.0, the sort pages option is no longer valid .

All join and sort operations that are not performed in memory take place in the tempdb database. In SQL Server 6.5, you can move the entire tempdb into memory. Having the tempdb database in RAM is not a benefit because it takes away memory from other data access uses. Because SQL Server 6.5 memory allows only minimal size for sorting (64 2-KB pages to 511 2-KB pages), using the tempdb in RAM option is the only way to sort at RAM speeds. SQL Server 7.0 allows a much higher allocation of memory for sorting and the tempdb in RAM option is no longer a valid option.

New sp_configure Options

These new sp_configure options allow values to be set as memory configurations to determine minimum and maximum values:

• extended memory size
• index create memory
• max server memory
• min server memory
• min memory per query

The extended memory size option is only available for Microsoft SQL Server 7.0, Enterprise Edition running under future versions of Microsoft Windows NT, Enterprise Edition, on an Alpha platform. This option indicates the number of megabytes of memory to use as a disk cache in addition to the conventional buffer pool. For example, on a computer with 8 GB of memory, a reasonable value for extended memory size might be in the range of 5,000 to 6,000. This allows 2 GB of conventional memory usage for SQL Server and most of the rest for use as an extended memory cache.

When SQL Server Enterprise Edition is used on future versions of Windows NT, Enterprise Edition, if the system administrator has configured the 3-GB switch in Boot.ini (referred to in Windows NT as 4GT tuning), the conventional memory can be a maximum of 3 GB instead of 2 GB. In this situation, the default value of 0 (self-configuring) for max server memory would cause the server to use up to 3 GB of conventional memory. Therefore, the absolute upper end of the extended memory range (for the hypothetical 8-GB system) would be 5,000.

The index create memory option controls the amount of memory used by index creation sorts. If you build large indexes, you may want to experiment with increasing the value of this setting from its default. Memory sort allocations are controlled through the min memory per query option. Therefore, if you request 2,000 KB for index create memory and min memory per query is set to 2,000 KB, the create index operation waits to execute until it can get that much memory. On a large scale production server, creating indexes is not a frequent task. Most often it is a scheduled job at off-peak times because of the intrusiveness of the create index process. Therefore, if you are running create index operations infrequently and at off-peak hours, increase this number significantly. Remember to keep min memory per query at a lower number so the job will activate even if all the requested memory is not available.

Recommendations:

• Increase the index create memory option if you are creating infrequent large indexes.
• Keep min memory per query (KB) low enough to allow the CREATE INDEX statement to run even if all memory for index creation is not available at execution.

Use the min memory per query option to specify the minimum memory (in kilobytes) to be allocated for the execution of a query. For example, if min memory per query is set to 2,048 KB, the query is guaranteed to receive at least that much total memory. You can set the min memory per query option to any value from 0 to 2,147,483,647KB (2 GB). The default is 1,024 KB.

Increasing the value of query memory generally improves the performance of queries that use hashing or sorting operations, particularly when there is significant memory available and few concurrent queries. min memory per query includes memory allocated for sorting and replaces the sort pages option in earlier versions of Microsoft SQL Server. Do not set min memory per query too high, especially on very busy systems, because the query must wait until it can get the minimum memory requested or until the query wait setting is reached. (query wait is another sp_configure option that specifies how long to wait for resources before canceling the query altogether.)

Recommendations:

• Increase min memory per query if you are performing sorts and hash joins and you have enough memory to support these operations with the expected number of concurrent users.
• Do not set min memory per query too high on busy systems (systems with many active users) or your query must wait until the min memory per query value can be allocated.
• Consider setting query wait. The query wait value should be specific to user requirements.

SQL Server 7.0 Stored Procedure Cache

In SQL Server 6.5, stored procedure cache is a fixed segment of memory based on the procedure cache option of the sp_configure stored procedure. In SQL Server 7.0, this option has been eliminated. SQL Server 7.0 memory manager dynamically manages how much memory stored procedures require and allocates it to them.

How stored procedures are used in memory has also changed. In SQL Server 6.5, if 100 users want to use a precompiled stored procedure at the same time, SQL Server must bring up 100 copies of that stored procedure in memory. In SQL Server 7.0, only one instance of that stored procedure is in memory and is used by all 100 users. Stored procedures often get passed variables and these variables may affect the query plan. The SQL Server 7.0 memory manager determines when to recompile that stored procedure and bring in a new plan based on the input variables.

In this example, stored procedure A has two variables passed to it that are inserted into a SQL statement as part of an equal clause:

"SELECT * FROM Table WHERE field1=&variable1 AND field2=&variable2"

The plan remains the same because it follows an equal predicate and the cached stored procedure is used.

In this example, stored procedure A has two variables passed to it that are inserted into a BETWEEN clause:

"SELECT * FROM Table WHERE field1 BETWEEN &variable1 AND &variable2"

The stored procedure may have to be recompiled to get a new plan because the variables may dictate a new plan.

Dynamic Query Caching

Microsoft SQL Server 7.0 now caches the plan for dynamic Transact-SQL statements as well as static statements. This can be advantageous in an ad hoc environment in which many users are executing the same dynamic Transact-SQL statement. The memory manager gives a lower priority to dynamically cached Transact-SQL than it does to static Transact-SQL, such as stored procedures; therefore, the stored procedure stays in the buffer cache longer.

Monitoring Memory Efficiency

The SQL Server: Buffer Manager object in Windows NT Performance Monitor provides counters to monitor how SQL Server uses memory to store data pages and internal data structures. Monitoring the memory used by SQL Server can help determine whether bottlenecks exist due to a lack of available physical memory for storing frequently accessed data in cache, in which case SQL Server must retrieve the data from disk. By monitoring memory, you can determine if query performance can be improved by adding more memory, or making more memory available, to the data cache or SQL Server internal structures.

The table shows some of the SQL Server: Buffer Manager counters.