SQL Server memory optimization for server optimization case analysis

Source: Internet
Author: User
Tags mssqlserver server memory

Condition analysis


The environment is as follows:

Hardware: IBM3610 Server

System: Windows2003 x32

Application: Internal logistics System software C/s architecture

Database: SQL Server2000


Problem:

    1. Because the Logistics system architecture problem (development compared to 05 years development architecture) server and client can only run in 32-bit environment

    2. This causes the system memory to use not up, has been around 3.25G

    3. SQL Run memory once you get up and down

    4. When the user is connected to a large number of cards, and not to go


Finally, a number of methods were collected to optimize memory in 32-bit environments, as follows:


1.Windows 2003 Enterprise version open PAE better utilize more than 4G of memory


Modify the boot. ini file as follows:


[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 advanced Server"

/fastdetect/pae



2. Enable the Lock memory page option (Windows)

Enable the Lock memory pages option

On the Start menu, click the Run submenu, and then 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 Rights Assignment check box.

The policy appears in the details pane.

In the details pane, double-click Lock Memory Pages.

In the Local Security Policy Settings dialog box, click the Add button.

In the Select Users or Groups dialog box, add an account that has permission to run Sqlservr.exe.


3. Enable SQL-enabled AWE

Code below, set SQL to use 6G of memory

sp_configure ' show advanced options ', 1

RECONFIGURE

GO

sp_configure ' awe enabled ', 1

RECONFIGURE

GO

sp_configure ' max server memory ', 6144

RECONFIGURE

GO


4. You must restart the SQL Server 2000 instance for the changes to take effect.

net stop MSSQLServer

NET start MSSQLServer


SQL Server memory optimization for server optimization case analysis

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.