A: SQL Server 2008 will dynamically adjust how much memory it uses, but you can configure minimum and maximum amounts. The easiest way to set the minimum and maximum values is to use the SQL Server Management Studio (which is part of the SQL Server 2008 management tools installation option).
Right-click the database server and select Properties. Select the Memory page to expose the options to set minimum and maximum server memory (in MB). 16MB is the smallest number that can be entered and largest, which is set as default, is 2,147,483,647MB (2,048TB, or 2PB). You can set this to a more reasonable value for your environment based on sizing calculations.
To change this setting using the SQL shell, you need to enable advanced options then run the command
use master
EXEC sp_configure 'show advanced options', 1
reconfigure
EXEC sp_configure 'max server memory (MB)', 2048
To view the current value, just remove the ", 2048" as shown in the example below.
To set the minimum, set the 'min server memory (MB)' value.
Changes take effect within a few seconds of setting the value and are reflected in the SQL Server memory performance counters.
