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:

• PAE Switch
• NOEXECUTE Switch
• 3GB Switch
• USERVA Switch
• Recommendations for Windows Memory Switches

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.

---

PAE Switch

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.

Top

---

NOEXECUTE Switch

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.

Top

---

3GB Switch

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. Windows does not normally need all 2GB memory, so it can run other programs on computers with less than 2GB memory. However, those programs are restricted to using a maximum total of 2GB memory.

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

Top

---

USERVA Switch

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 http://support.microsoft.com/kb/316739.

Top

---

Recommendations for Windows Memory Switches

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 and 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