17th-Configuring SQL Server (2)-memory configuration in 32-bit and 64-bit systems

Original: 17th-Configuring SQL Server (2)-memory configuration in 32-bit and 64-bit systems

Objective:

This article covers memory configurations in 32-bit and 64-bit systems, where DBAs often try to turn on AWE to limit memory in SQL Server 2005/2008. However, after SQLServer2012, this option will be deprecated, so you cannot use this method to control the virtual address space of a 32-bit instance. If you have a lot of memory on your server, you can only upgrade to a 64-bit system. Here is the memory limit given by Microsoft:

Although AWE has been deprecated since 2012, learn how the 32-bit system uses AWE (Address windowingextensions) to enable the operating system to access more memory. If the available physical memory is more than the maximum server memory option, SQL Server locks the maximum server memory option if the maximum server If the memory option is not set or the physical memory is set less than this option, the SQL Server instance consumes all available memory and retains only 256MB.

Preparatory work:

Remember the values of the current maximum server memory and minimum server memory, which can be viewed in the View Sys.configurations table, which contains configuration information at the system level.

Steps:

1. Set the minimum memory for SQL Server with the following statement:

EXEC sp_configure ' min server memory (MB) ', 1024GORECONFIGURE with OVERRIDE; GO

2. Set the maximum memory with the following script:

EXEC sp_configure ' max server memory (MB) ', 3000GORECONFIGURE with OVERRIDE; GO

Analysis:

Step 1 Configure the minimum memory to SQL Server, the default value is 0, you can set the minimum value less than or equal to the max server memory value.

Step 2 configures the maximum allocated memory for SQL Server, with a default value of 2TB, which cannot be less than 64M.

All settings are stored in the buffer pool of SQL Server. If you use a 32-bit system, your SQL Server cannot use more than 3G of memory. No matter how many servers you have available within the server. Unless AWE and PAE are turned on.

If the system service in the system only runs SQL Server, set the maximum memory, but if there are multiple services running at the same time. And the domain controller uses the maximum memory, SQL Server will be out of memory, at a different angle, if SQL Server uses the maximum memory, then the domain controller will be out of memory, resulting in, should be reasonable configuration of memory to avoid memory overflow.

17th-Configuring SQL Server (2)-memory configuration in 32-bit and 64-bit systems