November 2, 2007 at 10:44 am
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.
November 2, 2007 at 12:01 pm
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
November 3, 2007 at 12:43 pm
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
November 5, 2007 at 6:46 pm
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
November 6, 2007 at 3:53 am
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
November 6, 2007 at 6:59 am
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
November 7, 2007 at 3:50 pm
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."
November 8, 2007 at 6:49 am
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/
November 9, 2007 at 3:53 pm
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
November 14, 2007 at 6:35 am
Can I add another question to this?
What are the benefits when you turn this on?
November 14, 2007 at 8:00 am
Turn what on Steven?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 14, 2007 at 8:18 am
The "Lock Pages in Memory" policy.
Thanks
November 14, 2007 at 8:28 am
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply