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.