SQL Server 2000 - memory management

[purefreshair]

Hello,

Our company has a Dell 2600 Poweredge data server. It is running an instance of SQL server 2000, Standard edition using Windows 2003 server standard edition OS. The server currently has 2GB of ram. It is running one production database app, accessed by several end users. We're considering a memory upgrade. If upgrade the ram to either 4GB or 6GB, can I allocate more memory to SQL server, or am I limited since we're using the standard editions? From what I seem to read everywhere, buying more memory using standard editions won't help much, if at all.

Any comments from experts on this topic would be appreciated.

Thanks!

 

[mutley1]

Try this - most helpful!

http://support.microsoft.com/default.aspx?scid=kb;en-us;274750

BR,

M.

 

[mrdenny]

That article would be more useful is purefreshair was running Enterprise edition.

As SQL only supports 2 GB of RAM buying more than about 1 GB of additional RAM won't do you much good.

If you buy another Gig of RAM, SQL can use 2, which would leave 1 GB for the OS. This will give you a little bit of performance gain.

In order to configure SQL 2000 to use more than 2 GB you'll need Enterprise Edition.

If you are looking to upgrade to SQL 2005, standard edition supports up to 4 GB of ram.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

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

http://www.mrdenny.com

 

[mutley1]

Good spot Denny, apologies

Sorry purefreshair. I didn't read the question in it's entirity, just a skim read.

M.

 

[purefreshair]

Thank you mrdenny.

If I look at the memory tab under the SQL server properties, I see that it is currently set at the defaults; 1) Dynamically configure SQL server memory is selected (dynamically adjust) and 2) minimum query memory is set at the default of 1024KB
So if I upgrade the ram on the server from 2 to 4GB, should I up the minimum query memory to say 2048KB and leave the other value as is (dynamic)? Will that buy me any improvement in performance?

 

[mrdenny]

I don't think that I've ever changed the query memory.

Leave the server memory at dynamic. SQL will take what it needs when it needs it. There's no point in having SQL Server manage memory if it isn't going to be using it.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

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

http://www.mrdenny.com

 

[purefreshair]

I've always seem to notice that when SQL takes what it needs when it needs to, it never "lets go" when it's finished. sqlsvr.exe always starts out low after a server re-boot; then over time it takes more and more (gradually keeps growing and growing). Why is that?

 

[mrdenny]

That's the way SQL is designed. As SQL runs it loads often used data from the disk to memory. As newer data is needed it loads that data from disk to memory. Once the allocated memory is full or cashed data if new data needs to be cashed SQL picks some data which it hasn't accessed in a while and flushes it from memory to make room for the new data.

If no one access the SQL server for 3 hours the data that was last in memory is left in memory because the SQL Server doesn't know when the next person will be by and what they will want to look at.

Loading data into memory is all a time saver. If it's in memory it can be accessed faster than if it's on disk.

This is why if you run a query that takes 20 seconds to run, if you run it again, right away it will only take a second to run. That's because the execution plan and data is all sitting in memory waiting to be reused.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

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

http://www.mrdenny.com

 

[purefreshair]

OK Thanks for your help.

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

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

http://www.mrdenny.com