SQL Server and Memory Leaks

I’ve come across comments about SQL Server “leakingmemory” countless times during discussion group spelunking,seeing one yet again this morning. Generally it goes something like”after 5 days, SQL Server is consuming almost all of theserver’s memory, so we have scheduled a weekly reboot to deal withit. Man, I wish Microsoft knew how to write software!” (youcould replace “SQL Server” with “Exchange“).

While it is entirely possible that SQL Server does leak memoryin some edge scenarios, what most people are seeing is actuallynothing of the sort, and they are actually reducing theirsystems performance continually recycling it. This is because SQLServer is a memory caching system, and as it reads pages in it willattempt to memory cache data to satisfy future reads, using moreand more of the available memory as a data cache, unless explicitlygiven a limit. It’s for this reason that a 4GB server is generallyfaster than a 512MB server for large databases-  more of itfits in memory, reducing the I/O requirements for reads (obviouswrites are immediately persisted). SQL Server only grabs the memoryon a need basis: When someone does a table scan of someoccasionally touched table, it might request a memory cache for it,making SQL Server’s memory footprint larger. If you monitor it overtime it’ll look like it’s endlessly edging up, when in realityit’ll release memory if something else starts asking for it, or asit approaches memory exhaustion.

In most situations this is ideal – or did you buy all of thosememory sticks to sit unused? The only time that it really is aproblem is on shared servers where SQL Server has to play nicelywith another “use all of the memory” applications (like Exchange,or in some cases even the garbage collection model of .NET). Inthis case you might want to restrict both servers to a maximumamount of memory, or let them thrash it out.