Database Reference
In-Depth Information
set to prevent the other applications from starving SQL Server of memory. On a system where SQL Server is running on its
own, I prefer to set the minimum server memory equal to the max value and simply dispatch with dynamic management.
On a server with multiple SQL Server instances, you'll need to adjust these memory settings to ensure each instance has an
adequate value. Just make sure you've left enough memory for the operating system and external processes.
Memory within SQL Server can be roughly divided into buffer pool memory, which represents data pages and free
pages, and nonbuffer memory, which consists of threads, DLLs, linked servers, and others. Most of the memory used by
SQL Server goes into the buffer pool. But you can get allocations beyond the buffer pool, known as private bytes, which
can cause memory pressure not evident in the normal process of monitoring the buffer pool. Check Process: sqlservr:
Private Bytes in comparison to SQL Server: Buffer Manager: Total pages if you suspect this issue on your system.
You can also manage the configuration values for min server memory and max server memory by using the
sp_configure system stored procedure. To see the configuration values for these parameters, execute the sp_configure
stored procedure as follows:
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'min server memory';
EXEC sp_configure 'max server memory';
Figure
2-4
shows the result of running these commands.
Figure 2-4.
SQL Server memory configuration properties
Note that the default value for the min server memory setting is 0MB and for the max server memory setting
is 2147483647MB.
You can also modify these configuration values using the sp_configure stored procedure. For example, to set max
server memory to 10GB and min server memory to 5GB, execute the following set of statements (
setmemory.sql
in
the download):
USE master;
EXEC sp_configure 'show advanced option', 1;
RECONFIGURE;
exec sp_configure 'min server memory (MB)', 5120;
exec sp_configure 'max server memory (MB)', 10240;
RECONFIGURE WITH OVERRIDE;
The min server memory and max server memory configurations are classified as advanced options. By default,
the sp_configure stored procedure does not affect/display the advanced options. Setting show advanced option to 1 as
shown previously enables the sp_configure stored procedure to affect/display the advanced options.
The RECONFIGURE statement updates the memory configuration values set by sp_configure. Since ad hoc
updates to the system catalog containing the memory configuration values are not recommended, the OVERRIDE flag
is used with the RECONFIGURE statement to force the memory configuration. If you do the memory configuration
through Management Studio, Management Studio automatically executes the RECONFIGURE WITH OVERRIDE
statement after the configuration setting.
