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
- Dozens more answers to tough SQL Server questions from Michael Hotek are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in August 2003