-
Notifications
You must be signed in to change notification settings - Fork 10
Windows Memory Switches
| Previous SQL Server Memory Management | Windows Memory Rights Next |
|---|
This section describes the Windows boot-time switches that affect SQL Server memory management.
This section covers the following items:
A number of other Operating System Switches exist that are not relevant for SQL Server memory management. Most of these switches are documented at https://docs.microsoft.com/en-US/troubleshoot/windows-server/performance/switch-options-for-boot-files.
SQL FineBuild does not set the value of any Operating System Switch. Any Switch that is required must be set manually.
The Program Addressing Extensions (PAE) switch turns on the Windows facilities that allow use of memory metadata
If the NOEXECUTE Switch is enabled, then the functionality provided by the PAE Switch is enabled regardless of if the PAE switch is present.
When the PAE Switch was first introduced (in Windows 2000) all it did was to enable use of extended memory. The next generation of CPU chips allowed portions of memory to be marked as Not Executable, and the PAE switch in effect changed its meaning from use extended memory to use memory metadata.
From Windows 2008 R2 onwards, the PAE functionality is always enabled, regardless of what switches are present.
This switch controls the Windows facilities that allow memory to be marked as Not Executable.
Memory that is marked Not Executable will not allow program code to run in these locations. This prevented a large number of viruses from working that were active when this feature was introduced. The NOEXECUTE switch remains an important part of protecting Windows from both virus attacks and bad coding practices in normal programs.
The NOEXECUTE Switch requires that memory metadata functionality is active in order to work. Therefore, if the NOEXECUTE switch is present, the functionality controlled by the PAE automatically enabled, regardless of if the PAE switch is present.
The NOEXECUTE Switch has a number of possible values. For more details see https://docs.microsoft.com/en-US/troubleshoot/windows-server/performance/switch-options-for-boot-files.
This switch restricts a 32-bit Windows operating system to using 1GB memory, making 3GB memory available to applications instead of the normal 2GB. It is not used on a 64-bit operating system.
On a 32-bit system, by default the Windows operating system can use a maximum of 2GB memory for its own use, leaving a further 2GB memory for use by all other applications, including SQL Server. The 3GB Switch allows more memory to be used by applications, at the expense of restricting what Windows can itself use to no more than 1GB. However, to achieve best performance all programs and Windows itself need to fit within the memory available to the computer.
For applications that can fit entirely within 2GB memory, the 3GB Switch makes no difference to how they perform. However, SQL Server will generally perform better if it can use more memory, so the 3GB switch can help SQL Server performance.
Because the 3GB switch restricts how much memory that Windows can use, there are some situations where it is safe to use and others where caution is needed or it is dangerous. These are given in the table below.
A 32-bit version of Windows can only access memory above the 4GB line if it uses Extended Memory. See SQL Server AWE Property for more details of how this works.
| Amount of Memory | 3GB Use | Comment |
|---|---|---|
| Under 3GB | Safe | It is safe to use the 3GB Switch on a computer with a small amount of memory, but there is nothing to be gained by doing this |
| 3GB to 4GB | Safe | The 3GB Switch can be safely used, and more memory will be available to programs such as SQL Server |
| 4GB to 12GB | Safe | The 3GB Switch can be safely used, and more memory will be available to programs such as SQL Server. Extended memory can be used by SQL Server if the PAE Switch and the SQL Server AWE Property are enabled |
| 12GB to 16GB | Caution | Depending on the workload, Windows may become unstable if the 3GB Switch is enabled without also using the USERVA Switch |
| Above 16GB | Danger | Windows may fail to boot or crash without warning if the 3GB Switch is used on a computer with more than 16GB memory |
The User Variable Area (USERVA) switch can be used with the 3GB Switch to reduce the amount of memory available to programs from 3GB to any value above 2GB.
The USERVA Switch can help maintain system stability and allow more than 2GB memory to be used by programs on a system with between 12GB and 16GB. Details of how to use the USERVA Switch are given at KB316739.
The recommended settings for the Windows Memory Switches described on this page are given below:
- The PAE Switch can be set on any computer, but it is not needed if the NOEXECUTE Switch is also present
- The NOEXECUTE Switch should always be present and be set to OPTOUT
- The 3GB Switch should be set on any 32-bit version of Windows with less than 12GB memory
- The 3GB Switch should not be used on any 64-bit version of Windows
- The USERVA Switch should not be required if these recommendations are followed
Copyright FineBuild Team © 2013 - 2021. License and Acknowledgements
| Previous SQL Server Memory Management | Top | Windows Memory Rights Next |
|---|
Key SQL FineBuild Links:
SQL FineBuild supports:
- All SQL Server versions from SQL 2019 through to SQL 2005
- Clustered, Non-Clustered and Core implementations of server operating systems
- Availability and Distributed Availability Groups
- 64-bit and (where relevant) 32-bit versions of Windows
The following Windows versions are supported:
- Windows 2022
- Windows 11
- Windows 2019
- Windows 2016
- Windows 10
- Windows 2012 R2
- Windows 8.1
- Windows 2012
- Windows 8
- Windows 2008 R2
- Windows 7
- Windows 2008
- Windows Vista
- Windows 2003
- Windows XP