Q

Releasing memory on the fly

We are running SQL Server 2000 and trying to maintain a 24x7 system. We have at least one process that runs that does not release memory. Is there a way to free up or release memory on the fly so we don't have to shut down SQL or reboot the server?

Having all of your memory allocated is NOT necessarily a problem. If you are running a dedicated SQL Server and it has allocated all of the memory on your system (less 128 MB which is will never encroach on), LEAVE it. It has that memory allocated, because at some point in time, it needed it. The SQL Server still has that memory, because no other process has requested memory from SQL Server. Since memory allocation is an expensive operation,...

it isn't just going to release the memory back to the OS unless something else needs it because if it needed that memory at some point in time, the assumption is that it will need that memory again. It would be dumb for SQL Server to just give up memory on a whim only to have to take the hit at a later point and reallocate it back again when nothing else needed the memory. One of the biggest operational mistakes that DBAs make is to assume that because SQL Server has allocated all of the memory (minus 128MB), that there is a problem on the system when in fact, your SQL Server is operating the way it was designed and the way that it performs the best. A SQL Server plays VERY well with everything else on a system. It WILL give memory back to the OS, ONLY IF something else needs it. If you collapse back the memory allocated, you are simply losing your data cache and you now have to resort to direct disk hits to retrieve data which is very expensive. By dumping the SQL Server to free up, you are losing all of the advantages to having a SQL Server running at peak efficiency with a maximum of data cache.

 

For More Information

This was first published in August 2003

Dig deeper on Microsoft SQL Server Installation

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close