Q

How to increase server memory used in SQL Server 2000

Change SQL Server memory settings by starting with sp_configugure to view configurations. Next follow these steps to take with the operating system (OS).

I'm using SQL Server 2000 Enterprise Edition and finding the total server memory is 1670976. The target server memory is also 1670976. My question is, if the server has 3GB of RAM, why is it that this value doesn't change much when the server is under I/O or CPU pressure? Why does it seem like the amount of memory used stays constant?
The target server memory is the maximum amount of memory that the server is configured to use. The total server memory is the amount of memory the server is using. Based on the numbers you've provided, your server is set up to use about 1.6 Gigs of memory. In order to increase this closer to the 3 Gigs of memory you have installed, you will want to make sure sp_configure shows SQL Server is set up to use more than 2 Gigs of memory. You'll also need to enable AWE to go past 2 Gigs of memory.

In addition, your operating system will need to be Windows 2000 Enterprise Edition or Windows 2003 Standard or

Enterprise edition. If your OS is one of these, enable PAE using the /PAE switch in the boot.ini file. Changes to the boot.ini file will require that the server be rebooted to take effect. You should read Microsoft article, How to edit the Boot.ini file in Windows XP before editing the boot.ini file.

This was first published in December 2007

Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close