Memory Management

In SQL Server releases before SQL Server 7.0, a systems administrator (sa) has to configure a fixed amount of memory for SQL Server to use and can't change that amount of memory without stopping and restarting SQL Server. If you configure too little memory, performance can suffer drastically because SQL Server won't have enough available memory to store frequently accessed data. However, if you configure too much memory (e.g., more than the OS has available), SQL Server fails to even start. And if SQL Server can't start, you can't run the procedure to reconfigure SQL Server to use less memory. With the early releases of SQL Server, administrators have to be real wizards to handle resource usage effectively.

Also in those early releases, not only can SQL Server use only a fixed amount of memory, but that fixed memory contains fixed sections reserved for specific purposes. One of these sections, the procedure cache, is for stored procedure query plans. The procedure cache size is fixed when the SQL Server service starts, and like total memory size, you can't change the cache size by restarting SQL Server.

Starting with SQL Server 7.0, the whole memory-management picture changed dramatically. Not only can an sa configure SQL Server to dynamically adjust its total memory, letting memory grow and shrink as necessary, but the purpose of any part of SQL Server's memory can change as necessary to allocate memory to different internal resources. If SQL Server needs more memory for query plans, it can release some data pages and use that space for plans, or if it needs more space for data that it is reading from disk, it can remove from memory any plans for infrequently executed queries.

Because SQL Server has a dynamic memory-management policy, I was surprised when a colleague asked a question about procedure cache at a seminar I gave recently. He remembered reading about a new switch that you could use when starting SQL Server 7.0 to limit the amount of procedure cache to a fixed size. I had heard nothing about this switch, but I knew he wasn't just imagining the feature. However, SQL Server 7.0 doesn't have anything called the procedure cache, so I did some research after the seminar. In the readme_txt file for SQL Server 7.0 Service Pack 2 (SP2), I found what my colleague was referring to. Before I tell you about this switch, let's explore the background of memory management in SQL Server 2000 and 7.0.

SQL Server's Address Space

In any Microsoft OS, the total amount of virtual memory available to an application is that application's address space. For SQL Server, the maximum amount of virtual memory is 2GB, unless you're using a special option. In the boot.ini file of Windows NT Enterprise Edition or Windows 2000 Advanced Server, you can use the /3GB switch to increase the amount of address space to 3GB.

Each instance of SQL Server has an address space with two main components. The main component is the buffer pool, which manages memory in 8KB chunks (or buffers). SQL Server 2000 and 7.0 use this area for data and index pages that SQL Server reads in from disk, for transaction log caches, for query and stored procedure plans, for system structures such as the lock table, and for the user process information, some of which you can view by using the sp_who and sp_who2 stored procedures. Usually, the address space in this area is almost the total amount of memory on the machine. However, if you choose to configure SQL Server's max server memory parameter, you limit the amount of memory that SQL Server can use for buffer caching.

The second component of SQL Server's address space, an area I call the non-buffer pool, is reserved primarily for executable code components or for large memory allocations that need space in chunks larger than 8KB. These components include executable files and DLLs that Open Data Services and the server Network-Libraries use, OLE DB provider DLLs on the server running SQL Server, and extended stored procedures that DLLs and OLE Automation system stored procedures use to create instances of OLE Automation objects. This area can also include procedure and query plans that need large amounts of memory.

When the SQL Server service starts, the OS first loads into memory the SQL Server executable and any static DLLs that SQL Server needs. Then, a section of the address space is reserved for SQL Server's non-buffer pool use. By default, SQL Server reserves 128MB of the total address space plus enough memory to allocate stacks for the total number of threads, which is based on your max worker threads configuration. The reserved amount is about 0.5MB per worker thread.

The buffer pool takes what is left of the address space. As long as SQL Server is running, this address space will be unavailable for anything except the buffer pool (and related memory objects that also use 8KB pages and can borrow memory from the buffer pool).

For SQL Server 7.0, the readme_txt file that accompanies SP2 discusses a /g memory_to_reserve startup switch that you can use to change the amount of address space reserved for the non-buffer pool component. Here's what it says:

[This switch] specifies an integer number of megabytes of memory SQL Server will leave available for memory allocations within the SQL Server process, but outside the SQL Server memory pool. The memory pool is the area used by SQL Server for loading items such as extended stored procedure .dll files, the OLE DB providers referenced by distributed queries, and OLE Automation objects referenced in Transact-SQL statements.

I found the description in the readme_txt file a little confusing because of its terminology, particularly its unclear use of the term memory pool. The people on the SQL Server development team at Microsoft told me that they never use the term memory pool internally, so I use the terms buffer pool and non-buffer pool.

When Would You Use This Switch?

If you have a large amount of memory and a SQL Server process has used all of its virtual address space, you might need to change the amount of memory reserved for the non-buffer pool area. You might consider using the /g switch if the following message starts appearing regularly in your SQL Server error log:

Warning: Clearing procedure cache to free contiguous memory.

This message comes from memory objects, such as procedure and query plans, that usually borrow memory from the buffer pool. When these objects have to allocate memory in chunks larger than the 8KB buffer size, they have to get the memory from the non-buffer pool area. If the non-buffer pool area doesn't have enough contiguous memory, SQL Server starts deleting existing procedure plans in an effort to create enough contiguous space before retrying the operation.

However, pay attention to the note in the SP2 readme file that warns "incorrect use of this option (/g) can lead to conditions under which SQL Server may not start or may encounter run-time errors." You need to make sure you leave enough memory for SQL Server's internal structures and a minimum number of data pages and query plans.

In some cases, you might want to use the /g switch to decrease the amount of extra reserved memory space. Decreasing the amount of reserved memory space can increase the amount of memory available to data and index pages, which can provide increased performance for memory-intensive workloads.

My Recommendations

I suggest that you perform thorough testing of the /g switch, and that you don't use it unless you really need to. Although the readme file I've been referring to and the description of the /g switch were written for SQL Server 7.0, the same switch is available in SQL Server 2000. Keep in mind that in SQL Server 2000, the default size of the memory space outside the buffer pool is 256MB, up from the 128MB in SQL Server 7.0. In addition, this switch will be obsolete when the 64-bit version of SQL Server becomes available and the address space for any application extends far beyond the current 2GB or 3GB limit.

For those of you who avoid readme files because you think they are only for wimps, I urge you to reconsider. Many, if not most, of SQL Server's readme files for service packs as well as for the original product include important information about features and behavior. For SQL Server 6.5, for example, Microsoft added several new configuration options in one of the service packs, and because Microsoft updated neither the online documentation nor the print documentation, the only way to know about these new options was by reading the readme.txt file.