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?

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top