Total Pageviews

Wednesday, May 7, 2014

Memory allocation for SQL Server - points to consider

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
Maximum “Max Server Memory”
4 GB
0 (Disabled)
Dynamic (default)
8 GB
1 (Enabled)
/3GB, /PAE
6.5 GB
16 GB
1 (Enabled)
/3GB, /PAE
14.5 GB
32 GB
1 (Enabled)
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”
Dynamic / best judgment

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

Note: Only a member of this blog may post a comment.