I am using SQL Server 7.0 on a Windows NT machine. We have been having problems with SQL Server not releasing the memory after it has utilized it.
Currently it is configured to allow a max of 511MB memory (1024 is total on the machine). I had some advice that my best solution would be to reduce the Max Memory to a lower value (say 400 MB) to help reduce the problem.
Is this not counter-intuitive? Or is this the correct solution?
I'll phrase my concern/question another way: over a period of time, I start to see a sluggish performance in the SQL Server. Is the fact that SQL Server is not releasing memory an issue for SQL Server's performance, or would that only be an issue for the other applications (very few) running on the same machine?
It actually shouldn't be an issue for either. The way SQL Server is supposed to work is by gobbling up as much memory as it needs, until the operating system starts telling it that other applications want to play too. So long as these other applications are relatively static in their memory needs, a sort of balance will be achieved. But if the upstart applications storms in, eats 100MB of memory, then closes in under a few minutes, then SQL Server will not have enough time to give up its memory, and you may encounter some page file usage.
As for SQL Seeming to be sluggish after tme, not sure what that could be. Have you set up perfmon to monitor the box? Maybe some sort of resource (memory) leak?
i suggest create maintenance plan for optimisation my friend. and also u havent mentioned db size temp db info . run this index tuning wizard... remove or add proper indexes.. it will ease down stress on sql server
After a period of 9-11 days, I'll see the response time to user requests slow down; a restart clears this up.
I have a DB Maintenance plan than runs every weekend to reduce the DB size, reset the indexes, etc. Pretty much all the standard features.
The total size of the 5 DBs is only 4GB, so we're not talking about anything significant in terms of size. Number of transactions is low (thousands, instead of millions...). Number of users is relatively small (two dozen).
DB Backup is every evening.
The responses here have lowered my concerns that the SQL server memory is the issue, and will allow me to focus on the application connection, and other aspects to the server.