Memory SQL Server and Page File Utilization 1

[lameid]

I did not see an SQL Administration forum so I guessed this would be the most appropriate...

Obviously moving from 1 GB of Ram to 4 GB made a huge performance improvement of my SQL server.

However the Page File utilization increased from 1GB under the old configuration to over 2GB now. Does this mean we really should have closer to 6 GB of RAM or does it simply mean Windows made the swap file bigger to improve performance? Client performance is acceptable but I am trying to gauge if I will need another upgrade.

Is perhaps a better metric Page File utilization over Physical Ram? As long as it does not reach X then it is ok? If so what is X? The only urgency here is the Server is running 32 bit Standard Windows server and 4GB of RAM maxed it out. If I need more RAM in the forseeable future, I need to plan for the Software upgrade and keep a timetable in mind.

If I do have to move away from 32 bit standard Windows will the 64 bit version allow for more memory?

 

[klewis10367]

This forum could help.....Microsoft SQl Server: Setup and Administration. It is under programmers

 

[lameid]

Dolt! I must have been looking only at My forums list instead of all of them.

Either way I got the opinion that a number of services specifically use the page file and based on my suspicion that unless the Pagefile is at least 75% physical memory I can not worry unless performance suffers. I am probably good for a while. Also I usderstand the Pagefile typically gets bigger with the more RAM added.

That leaves my less important questions about will 64 bit Server allow me to use more memory?

 

[mrdenny]

I didn't see you repost this in the SQL Admin forum, so here is some info until you do.

First, what version of SQL Server are you running? SQL 2000 Standard Edition will only use up to 2 Gigs of RAM, no matter how much you have installed. If you are using SQL 2000 Enterprise Edition or SQL 2005 you will need to tell SQL to use AWE to use above the 2 Gig limit.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[lameid]

SQL 2005.

I didn't repost because I thought it was a closed issue on the SQL side... Looks like I should be looking into the AWE switch. Also SQL is not the only thing on the box, just the important piece that was suffering. It is a small company so the servers multi-task a lot. This one has SQL 2005, Backupexec 10d (When is Symantec going to fix verification of production data?), File serves Jet (Access) Databases and software installs, has a small intranet, an Issue tracking IIS application on it and serves Antivirus updates. The IIS pieces seemed to push it to performance dogging for Client Applications using SQL Server.

I would move file serving off of it but I am locked into a less than ideal partiontion setup on the other server (predominately Exchange and file server). Also I am not sure the file locking settings required to get Jet databases to play nice would play nice with the more traditional file server function.

 

[mrdenny]

You will definetly want to enable AWE within the SQL Server, and the /PAE and /3GB switches in the boot.ini (If you are Windows 2003 SP1 or above the boot.ini switches aren't needed, Windows will do that for you.)

Be sure after you enable AWE that you set the max amount of memory that SQL can use. If you don't SQL Server will end up taking all the memory leaving nothing for the other apps installed.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[lameid]

Thanks and yes it is SP1 so I am good. Checking Taskmanager sqlsevr.exe using 1,717,116K so I am good for a little while. I am thinking I will limit it to around 3GB when I get around to it next week. Thanks for the advice.

 

[mrdenny]

If you don't enable AWE within SQL Server it will not use any more memory that it's already using (it might get to like 1,8??,???K, but that's about it).

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com