How to Recycle SQL Server Memory or Should We?
Don't recyle SQL Server if it consumes all your memory!
Moving to a dedicated server to host our dynamic Anchorage, Alaska Web Design sites caused an interesting observation. Our Web sites use SQL Server as the back end. After a week of starting the new server, we saw SQL Server caching anything it read into memory. Next time the Web application requires the same item, it will already be loaded in memory. SQL Server tracks how many times objects are accessed until the memory is full. When no more memory is available, SQL Server flushes least used items from memory before caching new items into memory.
After a month of observation, we learned SQL Server won't willingly release memory to the operating system. SQL Server's memory footprint kept growing. Wondering when it would stop, we saw SQL Server keeping all system memory it acquired. We learned SQL Server wants to respond to queries very quickly, so reading directly from memory is much quicker than reading from hard drives.
By design then, SQL Server should not release memory. If Web applications running on SQL Server's computer don't run well because SQL Server consumed all the memory, consider moving these other applications to another computer.
Alternatively, to restrict SQL Server from consuming all the computer's memory, from Enterprise Manager / SQL Management Studio change the upper limit to how much memory SQL Server is allowed to allocate/consume.
Don't interrupt SQL Server operations if it consumes too much memory, because this is caused by design.