I've come across comments about SQL Server "leaking memory" 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 the server's memory, so we have scheduled a weekly reboot to deal with it. Man, I wish Microsoft knew how to write software!" (you could replace "SQL Server" with "Exchange").

While it is entirely possible that SQL Server does leak memory in some edge scenarios, what most people are seeing is actually nothing of the sort, and they are actually reducing their systems performance continually recycling it. This is because SQL Server is a memory caching system, and as it reads pages in it will attempt to memory cache data to satisfy future reads, using more and more of the available memory as a data cache, unless explicitly given a limit. It's for this reason that a 4GB server is generally faster than a 512MB server for large databases-  more of it fits in memory, reducing the I/O requirements for reads (obvious writes are immediately persisted). SQL Server only grabs the memory on a need basis: When someone does a table scan of some occasionally touched table, it might request a memory cache for it, making SQL Server's memory footprint larger. If you monitor it over time it'll look like it's endlessly edging up, when in reality it'll release memory if something else starts asking for it, or as it approaches memory exhaustion.

In most situations this is ideal - or did you buy all of those memory sticks to sit unused? The only time that it really is a problem is on shared servers where SQL Server has to play nicely with another "use all of the memory" applications (like Exchange, or in some cases even the garbage collection model of .NET). In this case you might want to restrict both servers to a maximum amount of memory, or let them thrash it out.