SQL Server memory optimization for server optimization case analysis

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