SQL 2000 Memory problem

[antzzz]

Hi,

We have a Win 2000 server with 1 GB RAM hosting a SQL 2000 Standard Ed. (SP3) and small applications called Call Center Agents that broker requests to the database from the Client PCs that host the Call Center Applications. SQL Memory is dynamically allocated.

The vendor who developed the Call Center System is complaining that his agents located on the SQL Server machine are not getting enough memory to do their functions properly. When they checked SQL Server using Task Manager, it is using 900MB of memory. I told them that SQL Server by default tries to get as much memory as it can and will release it only if not needed. They suggested to limit the amount of memory used by SQL SErver to around 700MB so that their applications will also have memory to play with. My concern is that if I limit the amount of memory used by SQL Server, it might start paging a lot.

I have the following questions:
1) When does SQL Server actually release memory: if it doesn't need it OR if the Windows OS requests it?
2) How do I know how much memory is needed by the other applications running on the same machine as SQL SErver? Can I use Task Manager to check their memory usage while I shutdown SQL Server? Is this a good way?
3) Is 700MB enough for SQL Server on 1GB RAM machine?
4) IF we do increase the memory to let's say 2GB and SQL memory is dynamically allocated, will SQL Server, by default, get all the memory again? My concern is that it won't leave enough memory for the other apps again. Is SQL server "not a good team player"?

Any advice appreciated.

Thanks

 

[mrdenny]

1) SQL will decide when to release the memory.
2) That is one way. Another is to check with the vendors and see what they say. I generaly like to leave 500MB or more of memory for the OS and anything else on the server. But most of my servers are 2GB+ of memory.
3) If your server is currently using 900MB of memory then I'd have to say that no 700MB won't be enough. I would install more memory, of more the call center app to another server.
4) Yes SQL will take all the memory that it needs. If SQL want's to take 1.9 GB then it will, unless you limit it to say 1.5 GB.

SQL shouldn't start swapping to the page file to much. It should just slow down as it can't load everything that it wants to into memory.

Denny

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

 

[SQLSister]

SQL Server is best on a system where no other applications are running. It is not meant to share with other applications. Yes you can limit the amount of memory and may have no choice if you continue to run other applications on the same machine, but your SQL Server performance may suffer. It really is best to move the other applications to another server.

Questions about posting. See faq183-874