Does SQL Server have memory leaks? How can I tell?

A. Generally speaking SQL Server doesn't have much of a problem with memory leaks and it is almost always other programs/drivers that cause the problem. For specifics on known SQL Server bugs causing memory leaks see the end of the faq entry. It's always a good idea to apply the latest service pack if you haven't already - MS do fix these bugs as soon as they find them.

Remember that SQL Server will grab memory for it's data cache up to the amount you have specified via sp_configure. This is in 2K pages (for SQL 6.5) and in 1MB chunks for SQL 7.0 and above. This amount does not include any amount for tempdb in ram (6.5 and below only - SQL 7.0 and above don't support tempdb in ram) or for the SQL kernel and some other internal memory structures. It would not be unusual for the SQL kernel and other memory structures to use an extra 10-20MB of ram. In addition to this it also requires o/s buffers and memory for things like BULK INSERT, OLE/COM programs etc. These don't come out of the memory that SQL allocates for data cache.

Therefore if you configured SQL Server to use a maximum of 50MB ram, then don't worry until the memory allocated to sqlservr.exe goes over, say, 70MB.

With SQL 7.0 the default is to dynamically allocate memory - though you can set an upper limit if you wish. SQL Server 7.0 will keep grabbing memory, up to the set limit (default no limit), until NT tells it that other processes need the memory. On a dedicated machine this won't happen, so it is not unusual for SQL 7.0 to seem to keep grabbing more and more memory.

After reading the above explanations, if you still think you have a memory leak then run performance monitor and select the processes object. Choose all running processes (make sure everything you normally run is going at the time) and for these choose the paged pool, non-paged pool and virtual bytes objects. Put these on a chart or log with a long interval period. Monitor these objects over time to see what always increases. If it is SQL Server (sqlservr.exe) continues increasing above the maximum memory (+20MB) it should have allocated then it may be responsible for a memory leak. You could also check for handles and threads always increasing as these could also potentially be leaking - and running out of these is just as bad as running out of memory.

You can also get these parameters from task manager - go into the process view, choose view columns and add the relevant columns.

If no processes in task manager/perfmon show a memory leak, but the overall memory is still going up, then the leak must be down at the kernel level. To trace this use the poolmon.exe program. Instructions to use this are in Microsoft Technet and the Microsoft knowledge base site - just search for poolmon.

-----------------------------------

Known memory leak issues :-

1. If you are running the Novell network client v4.5 or above on the server then you may experience a memory leak. Go back to version 4.11 to resolve. (I don't know whether this is an Microsoft bug or a Novell one).

2. SQL 7.0 RTM had a couple of leaks that were resolved in SP1.

3. If you do 1000's of BULK INSERT's then you will notice a fairly significant memory leak with SQL 7.0 RTM and SP1. This will be fixed in 7.0 SP2. (Or contact Microsoft PSS for a post-SP1 hot-fix)

4. SQL 6.5. If you utilise performance counters and connect using the local computer name then a leak may occur in WinLogon.exe. (NT Performance monitor doesn't cause the problem). See Q249343 for more information. The problem will be fixed in 6.5 SP6.

5. If you use xp_cmdshell with the @attached_results option on SQL 6.5 then this leaks memory. Contact MS PSS or apply SP6 (when available)