Memory Allocation:
When we are talking about memory allocation for SQL
Server, we usually mean memory allocation for the buffer pool. The two well-known
parameters - the “max server memory” and the “min server memory” - always refer
to the data buffer only.
These options do not include the memory that is allocated
for other components within the SQL Server process. These components include
the following:
(a)
The SQL Server worker threads.
(b)
The Multipage Allocator of SQL Server Memory
Manager.
(c)
Various DLLs and components that the SQL Server
process loads within the address space of the SQL Server process.
(d)
The backup and restore operations.
(e)
SQL Server Agent process.
Windows 2003 vs.
Windows 2008/2008R2/2012:
In Windows 2003, OS manages its memory aggressively and
always tries to free up physical memory; this generally introduces “paging out”
issues in SQL Server. Windows 2008 and later versions are more educated and can
manage memory dynamically and non-intrusively. Therefore a SQL Server running on
a Windows 2008 or later edition has better experience and needs extra
consideration for configuring SQL Server memory.
Windows 32-bit vs.
64-bit:
Windows 32-bit edition can’t use more than 4GB of
physical memory while 64-bit Windows has varying limitations based on the
edition.
(a)
In a 32-bit Windows Server, the /PAE switch is
required to access more than 4GB of physical memory if installed. Enabling AWE
in SQL Server is also required.
(b)
In a 64-bit environment, /PAE and /AWE are not
required to be configured.
PAE and AWE Switch
(physical memory <= 4GB):
If a 32-bit environment has less than or equal to 4GB of memory,
the /3GB switch can be used to allocate more memory to the SQL Server. Once a /3GB
switch is used, the total virtual memory will be splitting since1GB is for
kernel mode and 3GB is for user mode usages.
PAE and AWE Switch
(physical memory > 4GB):
In a 32-bit Windows Server and SQL Server implementation,
if more than 4GB of memory is installed then both the PAE in Windows and AWE in
SQL Server need to be turned on. In a 32-bit physical environment, the
following configurations must be configured should the physical memory be used
beyond 4GB.
(a)
LPIM – Assign this local right to the SQL Server
service account.
(b)
Max Server Memory – use a fixed amount of memory
for SQL Server.
(c)
/PAE – Enable switch in Windows if it is not
done yet.
(d)
/AWE – Enable this switch in SQL Server.
If this is a Virtual implementation, then the provisioned
Guest Memory must be reserved in VMWare ESXi or in MS Hyper-V to prevent memory
swapping (balloon driver effect).
Memory configuration in 32-bit environment:
Follow the guidelines below when configuring memory for
32-bit SQL Server in a 32-bit environment.
Physical
RAM
|
AWE
|
Boot.ini
|
Maximum
“Max Server Memory”
|
4 GB
|
0 (Disabled)
|
/3GB
|
Dynamic (default)
|
8 GB
|
1 (Enabled)
|
/3GB, /PAE
|
6.5 GB
|
16 GB
|
1 (Enabled)
|
/3GB, /PAE
|
14.5 GB
|
32 GB
|
1 (Enabled)
|
/PAE
|
29.5 GB
|
Max Server Memory Allocation
Guideline:
Although Microsoft recommends using dynamic memory
configuration in a 64-bit environment, this recommendation does not consider suboptimal
SQL database design, and other executing applications and processes. So the
“Max Server Memory” setting is strongly recommended to limit the buffer pool of
SQL Server. Following is the guideline for configuring Max server memory in a
64-bit SQL Server. Always consider leaving some additional memory for Windows
and other applications based on workload.
Physical
Memory
|
Minimum
Memory for Windows
|
Maximum
“Max SQL Server Memory”
|
<16
|
2
|
Dynamic
/ best judgment
|
16
|
4
|
12
|
32
|
6
|
26
|
64
|
8
|
56
|
128
|
16
|
112
|
256
|
16
|
240
|
512
|
32
|
480
|
1TB
|
48
|
976
|
Max and Min Server
memory:
Consider allocating an equal amount of memory to the “Min
Server memory” and “Max Server Memory” option. This will eliminate internal
page allocation and de-allocation overhead.
NUMA and Memory Configuration:
When SQL Server detects NUMA in a physical or virtual
server, it calculates memory allocation from “Max Server Memory” and assigns an
equal amount of memory to each NUMA node. In case of dynamic memory
configuration, SQL Server Engine assumes an arbitrary value which may not be
sufficient for the current workload or over estimation may introduce OS memory
issues.
Please note that SQL Server 2000 SP4 and later editions support
NUMA memory.
Lock Pages in
Memory (LPIM) and SQL Server Editions:
By default, Enterprise and Developer 64-bit editions of
SQL Server 2005 and later versions support Locked Pages.
(a)
The standard edition of SQL 2005, SQL 2008 and
SQL 2008 R2 support Lock Pages in Memory if the startup trace flag 845 is set.
(b)
The standard edition of SQL 2012 and SQL 2014 supports
Lock Pages in Memory natively and does not require a 845 startup trace flag.
(c)
All 32-bit versions support Lock Pages in Memory
with /PAE and /AWE switch.
Lock Pages in
Memory (LPIM):
In 64-bit Windows 2008 or later editions, LPIM is not
required as Windows manages memory dynamically in a non-intrusive way. However,
certain workloads may need a generous allocation of guaranteed fixed memory to
operate smoothly. In this case, LPIM can be assigned explicitly to the SQL
Server Service account.
Following is the guideline for LPIM:
(a)
When Windows sits on a physical Machine:
(1) Make
sure that the “Max Server Memory” has been assigned.
(2) For
standard editions of SQL Server, add startup trace flag 845.
(b)
When Windows sits on a virtual Machine:
(1) Make
sure that the “Max Server Memory” has been assigned.
(2) For
standard editions, add startup trace flag 845.
(3) Make
sure that the provisioned guest memory has reservation in VMWare or in Hyper-V.
The trace flag 845 has no effect on the SQL Server
Enterprise edition.
Index Creation
Memory:
The ‘index creation memory’ option is self-configuring
and usually works without requiring adjustment. A larger value might be helpful
to improve index creation performance but there is no specific recommended
value. Please note that the run value for this parameter will not exceed the
actual amount of memory that can be used for the operating system and hardware
platform on which SQL Server is running. On 32-bit operating systems, the run
value will be less than 3 GB.
The run value needs to be equal or greater than the “Min
memory per query” when set.
Min memory per
query:
The default value of 1024 KB works well for almost all cases.
If some queries experience excessive sort and hash operation, and if optimizing
the query and refactoring associated indexes out of scope then this value can
be bumped up gradually to improve performance. If changing the setting helps
then consider adding more physical memory and allocating more memory to the data
buffer pool. The maximum limit is 2GB for this setting.
-g Switch (mostly applicable for SQL 2000 and 2005):
The –g
switch reserves additional memory outside SQL Server’s buffer pool in the SQL
Server process for extended stored procedures, executing
distributed queries, loading DLLs of extended stored procedures and calling OLE
automation objects from Transact-SQL. Consider using this switch if the following
message is logged in the Error Log.
WARNING:
Failed to reserve <n> bytes of contiguous memory
WARNING:
Failed to reserve contiguous memory of Size= <allocation_size>
More reading:
No comments:
Post a Comment