SQL Server server large memory configuration [network collection]

Source: Internet
Author: User
Tags mssqlserver server error log

A new server with 4 GB memory is configured and 4 GB is correctly displayed at startup. However, after win2003sp2 is installed, the system only displays 3.37 GB. It was originally thought that there was a hardware fault in the memory, which was then checked

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 lock Memory Page option (Windows)
enable lock Memory Page option
click the "run" sub-menu on the "Start" menu, in the open box, type "gpedit. MSC ".
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 successfully enabled, the message "address window extension enabled" appears in the SQL Server Error Log when the SQL Server 2000 instance is started.
awe enabled is an advanced option. If you are changing this setting using the sp_configure system stored procedure, you can change awe enabled 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

-------------------------
The configuration option 'show advanced options' has been changed from 1 to 1. Run the reconfigure statement for installation.
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.
The configuration option 'awe enabled' has been changed from 0 to 1. Run the reconfigure statement for installation.
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.
You have changed the configuration option 'max server memory (MB) 'from 2147483647 to 6144. Run the reconfigure statement for installation.
--------------------------

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.