Can a readme file contain extra memory?
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).
Prev. page
[1]
2
next page 
