SQL Server 2005 Memory Configuration

Question

SQL Server 2005 Memory Configuration

ramses2nd

Hall of Fame

Points: 3720
More actions
November 2, 2007 at 10:44 am

#180910

I have a server(SQL Server 2005 Standard Edition) running Windows Server 2003 Enterprise edition with 2 quad core CPU's and 20 GB of RAM. I have been playing around with the various memory configurations and am looking for suggestions.
When the server was setup the Windows admin set a 30GB page file size and what I'm finding is SQL Server seems to excessivly using the Page file (Virtual Memory) as opposed to the physical RAM.
Any suggestions would be great.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 1

how much RAM is the server set for maximum memory? i would also run a trace to see how efficient the table/index structure is. nothing like a bad schema or execution plan to hog up memory

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 2

Sounds like you using Windows 2003 32-bit? If so, here's the recommened settings:

1. Ensure /PAE switch is in the boot.ini

2. Ensure /3GB switch is in the boot.ini

3. Ensure paging file is 30GB on non-data or log disk

4. Ensure paging file initial and maximum size setting are sam (e.g. 30GB\30GB)

5. Enable AWE on SQL Server

6. Set Max SQL Server memory up to 16GB

If you want to allocate more than 16GB, make sure you do not include step 2.

Thanks,

Phillip Cox

Pam Brisjar SSChampion Points: 12094 More actions · Answer 3

Try out all the different options to see what works best for you as it will seriously vary from site to site / server to server / DB to DB. You'll also get conflicting opinions. As a case in point, my research/experience (which includes among other things reading and working through the Microsoft performance whitepapers and working on multi-terabyte DB's) has shown that you should enable the /3GB (on systems with 4 GB RAM) OR the /PAE switch (on systems over 4 GB RAM) but not the two together.

Also, if/when you enable AWE, make sure you set the "Lock Pages in Memory" option.

here's a few refs:

http://blogs.msdn.com/slavao/default.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

Bart Noels SSC Enthusiast Points: 138 More actions · Answer 4

Can someone post the contents of a boot.ini please.

How exactly do i put in the parameters ?

Just starting at a new row with /3GB ?

Thanks

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 5

just add /3GB to the long row of crap

i think /fastdetect is in the row you need to add it to. or go to the knowledge base and run a search. there is an article explaining how to do it

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 6

here's a boot.ini file without the /3GB as an example to start with:

[boot loader]

timeout=30

default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS

[operating systems]

multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /PAE /NoExecute=OptOut

As it was stated eariler, add it to the end of the line with /fastdetect .

Also, on most servers you will have to forst take off the 'read only' attribute before you edit it.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 7

unless something has changed or unless you're using 64bit sql std you can't use extra memory.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

1) with 20GB RAM, do not use /3GB, just /PAE and enable AWE. Also consider upgrading to 64 bit sql server.

2) Colin, can't sql 2005 std use more than 2GB RAM? Per this link in BOL it can access the OS maximum, which is way up there with win2k3EE 32 bit isn't it? ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/95e5e902-66f9-4ef0-a7c6-61f42d81362e.htm

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Steven Kwong-473201 SSC-Addicted Points: 497 More actions · Answer 9

Can I add another question to this?

What are the benefits when you turn this on?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

Turn what on Steven?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Steven Kwong-473201 SSC-Addicted Points: 497 More actions · Answer 11

Steven Kwong-473201

SSC-Addicted

Points: 497

November 14, 2007 at 8:18 am

#751105

The "Lock Pages in Memory" policy.

Thanks

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 12

In Enterprise Edition only Lock Pages in Memory can keep the OS from flushing SQL Server data pages out of RAM. This can be a good thing, obviously, since RAM access is so much faster than disk I/O. It can also be disasterous (as in non-functional) if you allocate too much RAM to SQL Server and the OS or some other application runs out of memory and has to hit the disk.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service