Databases Reference
In-Depth Information
Getting ready
Keep the current list of values for Minimum server memory (MB) and Maximum Server
Memory (MB) by querying
sys.configurations
, which contains a row for each server-wide
configuration option value in the system. So, if you wish, you can set the current value in these
parameters after completing the exercise given in this recipe.
How to do it...
Follow the steps given here to perform this recipe:
1.
Set the minimum memory for SQL Server and execute the following query:
--setting 1024 MB as a minimum memory for SQL Server
EXEC sp_configure 'min server memory (MB)',1024
GO
RECONFIGURE WITH OVERRIDE;
GO
2.
Set the maximum memory for SQL Server and execute the following query:
--setting 3000 MB as a maximum memory for SQL Server
EXEC sp_configure 'max server memory (MB)',3000
GO
RECONFIGURE WITH OVERRIDE;
GO
How it works...
Step 1 configures minimum memory allocation for the SQL Server. The default value of
min
server
memory
is
0
(zero). You can set any value to
min
server
memory
that is less than or
equal to the value of
max
server
memory
.
Step 2 configures maximum memory allocation for the SQL Server. The default value of
max
server
memory
is
2147483647
, which is 2 TB. You cannot configure
max
server
memory
value as less than
64
.
Both of the memory settings reserve the memory for the SQL Server buffer pool. By
ensuring this, if you are dealing with 32-bit systems, you can't use more than 3 GB for your
SQL Server instance, no matter how much available memory you have on the server; this is
possible if enabling AWE and PAE, in older versions of SQL Server. But these features are
deprecated in SQL Server 2012, so if possible, go in for the 64-bit version of SQL Server
2012, which allows you to utilize as much memory as supported by your OS or by the SQL
Server edition you are using.
Search WWH ::
Custom Search