By default, SQL Server adjusts the total amount of the memory
resources it will use.
However, you can use the Min Server Memory and Max
Server Memory configuration options to take manual control. The default setting
for Min Server Memory is 0 MB, and the default setting for Max Server Memory is
2147483647.
If you use the sp_configure stored procedure to change both of these options to the same
value, you basically take full control and tell SQL Server to use a fixed memory
size. The absolute maximum of 2147483647 MB is actually the largest value that
can be stored in the integer field of the underlying system table. It is not related
to the actual resources of your system. The Min Server Memory option does not
force SQL Server to acquire a minimum amount of memory at startup.
Memory is
allocated on demand based on the database workload. However, once the Min
Server Memory threshold is reached, SQL Server does not release memory if it
would be left with less than that amount. To ensure that each instance has
allocated memory at least equal to the Min Server Memory value, therefore, we
recommend that you execute a database server load shortly after startup. During
normal server activity, the memory available per instance varies, but there is
never less than the Min Server Memory value available for each instance.
