How to tell how much memory?

[katbear]

Hi,

I am confused about SQL Server and memory.

On both 2000 and 2005, how can I tell how much memory is available for SQL Server to use?

SQL Server 2000:
When I look at server properties -> memory, I see that the 2000 server is set to use memory dynamically. Min is 0, Max is 2047, or 2GB.

However, the 2000 server only HAS 2GB memory to begin with. I know a large portion of that goes to the OS, so how much is REALLY left over for SQL Server 2000 to use?

When I look at perfmon, I see that Total Server Memory is 1.6 MB, that is, that is how much is assigned to SQL Server. Is that right?

SQL Server 2005:
Then, on SQL Server 2005, there is 4GB of ram on the server. Again, when I look at server properties -> memory, I see that Min is 0, and Max is 2147483647 MB, which is obviously more than 4GB!

And again, when I look at perfmon, I see that Total Server Memory is also 1.6 GB.

So my question is, since the 2005 server has 4GB available, and is set to use the max amount, how come only 1.6GB is available for SQL Server? Shouldn't it be higher than on the 2000 box??

Maybe I am missing something here. Any insights?

Thanks

 

[katbear]

Ok, one more question.

This is a dedicated SQL Server 2005 box.

My max memory is set to 3.5 gb.

What should my MIN memory be set to?

Thanks

 

[ptheriault]

That setting is really up to you. The minute you start running queries it's going to grab memory for data and procedure cache. I think a good start point may be around 1 gb.

- Paul

- If at first you don't succeed, find out if the loser gets anything.