Help with Implementing a memory upgrade

  • Hey guys, I'm toying with the idea of adding more memory to my server.  Currently it is a Windows 2000 SP4 machine running SQL Server 2000 SP 2 Enterprise ed.  It is currently only running with 2 GB of memory.

    As my Database has grown I've been noticing some areas where it looks like I am having a bit of a memory bottleneck.  (page life expectancy will from to almost 0, pages per second goes through the roof, and such)

    I only have the ability to add another 2 GB of memory for a total of 4.  The question is this, to use AWE or not.  The server also runs the Web application that reads and writes to the database.  I know I'd prefer them to be separated out to different servers and in an ideal world (read: when I get my hands on some more cash and the time to implement this it'll be like that) it would be like that.  However in the meantime, If I enable AWE, what sort of impact should I expect to see on IIS and the asp/VB application that is running there?

    If I set the max server memory to 3GB and have AWE enabled how should I expect the server to react?  will SQL server take all 3 GB and the other 1 GB will that be able to be used by the OS?  Also any suggestions on page file sizing to accompany this would be appreciated.

    Thanks for any input.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • What edition of windows you are using??? Read the following articles to get the info...

    AWE Memory SQL Server Performance Tuning Tips

    http://www.sql-server-performance.com/awe_memory.asp

    How to configure SQL Server to use more than 2 GB of physical memory

    http://support.microsoft.com/kb/274750

     

    MohammedU
    Microsoft SQL Server MVP

  • Perfect, the first link helps a lot.  Unfortunately, I beleive that I'm running the standard ed of windows server.  and since I'm only using 4 GB of RAM I don't need to worry with AWE.  I can just set the /3GB switch and move on.  That was the answer I was looking for.

    Thanks.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • It's all very well posting links as answers, doesn't really give "real world" experience !

    The article referenced is not complete or accurate.

    With 4Gb of ram you have two options, use the 3Gb switch OR set AWE ( which works with 4Gb of ram ) Either option may give you problems depending on what else is running on your server and/or your use of dts/maint plans/xml/extended procs/oledb etc. etc.

    I've used both methods, the 3gb switch gives sql server about 2.9gb ram to use and this option increases the size of some of the other memory pools, awe only increases the data cache, this can be useful. You don't need to change any memory settings and you can leave memory set to dynamic

    AWE increases your data cache and I've run a server with up to 3.5Gb allocated to sql server ( absolute dedicated box ). With awe you must set the maximum memory to what you want to use - awe disables auto memory management ( which most people don't seem to realise )

    Don't try setting 3gb and awe ( with 4gb ) , bad idea.

    Taking lower memory away may cause problems with external processes, dts and sysmaint.exe are the two which are most likely to cause problems as these run out of process, ms log shipping is a prime example. You may have problems with the mem to leave memory area too, so be careful and test.

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

  • Hey Colin, thanks for the response.  So far I have it configured with just the /3GB switch and I'm not really seeing what I was expecting to.  Originally the max mem was set to 1.5 GB which I changed to 2940 MB after I set the /3GB switch.  I have since rebooted and the SQL Server Process is being reported as only taking  approx 1636 MB.  I was expecting as users logged in this morning that it memory numbers would increase as needed, but I have not noticed this.

    Any ideas on what I may be missing? 

    thanks

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

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