Detailed analysis of the memory configuration of the SQL server

In the past, due to the high Memory price, the general configuration of memory is not much, more than 4 GB of course not much. currently, there are many servers with more than 4 GB configurations. After being configured as an SQL database server, many users only select the default settings, although they can be used normally, however, it is a pity that a large amount of memory is wasted (the memory used by the SQL service will not exceed 1.8 GB), and the system performance cannot be improved because of the large memory.

 

In this article, I will outline the configuration process. (If the server memory is less than 4 GB, no configuration is required)

 

1. enable support for large memory in the system (windows ):

To enable the support for Windows 2000 Advanced Server or Windows 2000 Datacenter Server, you must add the/pae parameter to the boot. ini file for physical memory larger than 4 GB.

 

[boot loader] timeout=0 default=multi(0)disk(0)rdisk(0)partition(1)WINNT [operating systems] multi(0)disk(0)rdisk(0)partition(1)WINNT="Microsoft Windows 2000 Advanced Server" /fastdetect

Changed:

 

[boot loader] timeout=0 default=multi(0)disk(0)rdisk(0)partition(1)WINNT [operating systems] multi(0)disk(0)rdisk(0)partition(1)WINNT="Microsoft Windows 2000 Advanced Server" /fastdetect /pae

After this change, restart the system.

 

2. Enable the lock Memory Page option (Windows ):

 

1. Enable the lock Memory Page Option

 

2. Click the "run" sub-menu on the "Start" menu, and type "gpedit. msc" in the "open" box ".

 

3. On the "Group Policy" console, expand "Computer Configuration", and then expand "Windows Settings ".

 

4. Expand "Security Settings", and then expand "local policies ".

 

5. Select the "user permission assignment" check box.

6. The details pane displays the policy.

 

7. In the details pane, double-click "lock Memory Page ".

 

8. In the "Local Security Policy Settings" dialog box, click "add.

 

9. In the "select user or group" dialog box, add an account that has the permission to run sqlservr.exe.

 

3. enable SQL awe:

 

To enable awe, set awe enabled to 1. Unless the max server memory value is specified, no

 

SQL Server retains almost all available memory, leaving only 128 MB or less.

 

If this option is enabled successfully, when the SQL Server 2000 instance is started

 

The message "address window extension enabled" appears.

 

Awe enabled is an advanced option. If you are changing this setting using the sp_configure system stored procedure, only

 

Awe enabled can be changed only when show advanced options is set to 1.

 

Set the SQL statement to use 6 GB memory as follows:

 

sp_configure show advanced options， 1 RECONFIGURE GO sp_configure awe enabled， 1 RECONFIGURE GO sp_configure max server memory， 6144 RECONFIGURE GO

You must restart the SQL Server 2000 instance to make the change take effect:

 

net stop mssqlserver net start mssqlserver