How SQL Server Uses Memory

SQL Server uses configuration options to control various aspects of memory allocation and performance. When you install SQL Server, the setup program assigns 8 MB of memory to SQL Server if the computer has less than 32 MB of RAM, and 16 MB of memory if the computer has 32 MB or more of RAM. After installation, you can and often should manually configure memory to a greater amount. After installation you can manually configure memory to a greater amount by running sp_configure. Note, however, that as you increase the SQL Server memory configuration, you must be sure that there is sufficient disk space to grow the virtual memory support file (PAGEFILE.SYS) sufficiently to accommodate the additional memory. For additional information, see your manuals for Windows NT.

When SQL Server starts, it requests the amount of configured memory from the operating system. The operating system allocates as much as it can from physical memory and the rest as virtual memory. As memory availability on the system changes, the operating system replaces physical memory with virtual memory (and virtual memory with physical memory). The operating system allocates memory for the server executable code, the static memory used by the server, and the non-cache server data structures.

An additional 5 percent of the remaining memory is allocated for miscellaneous overhead. The other 95 percent is divided between the data and procedure caches based on the value of the procedure cache configuration option.

You can determine the current value of the memory configuration option (and other configuration options) by running sp_configure. If you run sp_configure without any parameters, the output includes the current settings for all configuration options.

You can determine the current settings for a specific configuration option by running sp_configure with the option name. For example, to find the current value of the memory option:

sp_configure 'memory'
name        minimum    maximum     config_value   run_value
----------  ---------  ----------  -------------  -----------
memory      1000       1048576     4096           4096

The output includes five columns:

The name column indicates the name of the configuration option.
The minimum and maximum columns indicate the SQL Server minimum and maximum values for the option.
The config_value column of the sp_configure output indicates the current setting of the option.
The run_value column indicates the actual amount of memory SQL Server is using.

For the memory option, the values in these columns indicate memory settings in terms of 2K pages.

You can also adjust the amount of memory available to SQL Server by running sp_configure as follows:

sp_configure 'memory', config_value

where

config.value: Is the new memory configuration @value.

Important When configuring memory using sp_configure, be careful not to set the memory option to an amount greater than the amount of available memory. If SQL Server cannot obtain the memory it needs, it cannot start. If this occurs, start SQL Server in minimum configuration using the -f option. For details, see Microsoft SQL Server Setup.

To view memory usage information and determine if more should be added or allocated to the procedure cache, use DBCC MEMUSAGE, as described in "Using DBCC MEMUSAGE," later in this chapter. Under Windows NT, you can also examine the Cache Hit Ratio by using the Windows NT Performance Monitor. For information about viewing SQL Server statistics using the Windows NT Performance Monitor, see Chapter 19, Monitoring Server Activity and Performance.