485
Chapter 19: Confi guring SQL Server
19
allocate the minimum amount of memory at startup. The minimum simply means that when
SQL Server memory has reached that point, it will not reduce memory below it.The maximum-memory setting prevents SQL Server from growing to the point where it con-
tends with the operating system, or other applications, for memory. If the maximum is set
too low, then performance suffers.In SQL Server 2012, all memory allocations by SQL Server components will now observe the “max server memory”
confi guration option. In previous versions only the 8K allocations were limited by the “max server memory” confi gura-
tion option. Allocations larger than 8K weren’t constrained.Multiple SQL Server instances do not cooperate when requiring memory. In servers with
multiple instances, it’s highly possible for two busy instances to contend for memory and
for one to become memory-starved. Reducing the maximum-memory property for each
instance can prevent this from happening.From T-SQL code, the minimum- and maximum-memory properties are set by means of the
sp_configure system stored procedure. It’s an advanced option, so it can be changed only
if the show advanced options property is on:EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;show advanced options needs to be turned on only if it is not already turned on. After it is turned on, you can
change the advanced options and then reset it to the default value of 0.The following code sets the min-memory confi guration to 1GB:EXEC sp_configure 'min server memory', 1024;
RECONFIGURE;
Result:Configuration option 'min server memory (MB)'
changed from 0 to 1024.
Run the RECONFIGURE statement to install.
The following code sets the max-memory confi guration to 4GB:EXEC sp_configure 'max server memory', 4096;
RECONFIGURE;
Result:Configuration option 'max server memory (MB)'
changed from 2147483647 to 4096.
Run the RECONFIGURE statement to install.c19.indd 485c19.indd 485 7/30/2012 5:42:52 PM7/30/2012 5:42:52 PM
http://www.it-ebooks.info