Large memory configuration of SQL Server

In the past, due to the high Memory price, the general configuration of memory is not much, of course, more than 4G is not enough. The current server, the configuration of more than 4G is much, in the configuration of SQL

After the database server, many people only choose the default settings. Although it can be used normally, a large amount of memory is wasted (the memory used by SQL service will not exceed 1.8 GB ), it is a pity that the system performance cannot be improved because of the large memory.

In this article, I will give a general introduction to the configuration process (if the server memory is less than 4 GB, you do not need to configure it) (I have never seen this on the internet, just hand it over)

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

To enable Windows 2000 Advanced Server or Windows 2000 datacenter server support

For a physical memory larger than 4 GB, you must add the parameter/PAE to the boot. ini file.

[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

Change

[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)

Enable lock Memory Page Option

On the "Start" menu, click the "run" sub-menu, and then type "gpedit. msc" in the "open" box ".

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

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

Select the "user permission assignment" check box.

The details pane displays the policy.

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

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

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.

The Code is as follows. Set SQL to use 6 GB memory

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 an SQL Server 2000 instance to make the change take effect.

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.

Refer to the SQL Help File