sql2005 performance Optimization (a way to break 2G of memory usage on 32-bit systems).

Reprinted from http://blog.csdn.net/soldierluo/article/details/6589743

The server disk is composed of (SAS) IBM RAID0+1,SQL2K5 only recognizes 4G of memory, actually consumes only 2G of memory. With AWE, the application can leave the maximum amount of physical memory allowed by the operating system directly as non-paged. Using AWE enables SQL Server to cache details without reading the details from the system paging file on disk. Improves performance and reduces the frequency of disk access with faster data access. It is decided to open the AWE parameter of SQL2K5, lock the 6G memory, and use it completely for SQL.
The approximate configuration steps are as follows:
1, because the server has opened the/PAE parameter, otherwise, you need to modify the boot. ini file (remove the read-only attribute) under the System packing directory, and add the/PAE parameter after =optin/fastdetect (there should be spaces in front). (If there is no boot.) INI file can be copied from other similar machines)
2. Enable the Lock memory pages option in Group Policy for Windows:
A) on the Start menu, click the Run submenu, and then type gpedit.msc in the Open box.
b) on the Group Policy console, expand Computer Configuration, and then expand Windows settings.
c) Expand Security Settings, and then expand Local Policies, and select the User Rights Assignment check box.
d) In the details pane, double-click Lock Memory Pages.
e) In the Local Security Policy Settings dialog box, click the Add button.
f) In the Select Users or Groups dialog box, add an account that has permission to run Sqlservr.exe (administor account).
3. Enable AWE support for SQLSERVER2K5.
A) In Object Explorer, right-click the server and select Properties.
b) Click the Memory node.
c) Under Server memory options, select Allocate memory using AWE.
d) In Object Explorer, right-click the server and select Properties.
e) Click the Memory node.
f) Under Server memory options, select Allocate memory using AWE.
g) Configure the corresponding minimum memory count and maximum memory count.
It can also be manipulated in the same way as script execution:
--change AWE when show advanced options is set to ' 1 '
sp_configure ' show advanced options ', 1
RECONFIGURE
GO
--Enable AWE option
sp_configure ' awe enabled ', 1
RECONFIGURE
GO
--Set maximum memory to 6G

EXEC sp_configure ' max server memory (MB) ', 4800; --This value is referenced below
GO
RECONFIGURE;
GO

physical RAM maxservermem Setting
2GB 1500
4GB 3200
6GB 4800
8GB 6400
12GB 10000
16GB 13500
24GB 21500
32GB 29000
48GB 44000
64GB 60000
72GB 68000
96GB 92000
128GB 124000

4. Note that the SQL service needs to be restarted.
When the AWE parameter is enabled, the client response time will be 1 minutes from the original to 5 seconds