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, of course, more than 4 GB is not enough. The current server, Configuration More than 4 GB, after being configured as an SQL database server, many people only select the default settings, although it 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. Open System Large Memory support in (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.

The following is a reference clip: [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:

The following is a reference clip: [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 Configure ", 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. Select User Or add an account that has the permission to run sqlservr.exe in the dialog box.

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:

The following is a reference clip: 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:

The following is a reference clip: Net stop MSSQLServer Net start MSSQLServer

In this way, after the SQL service is started, the 6 GB memory will be locked for SQL use.