|
|
|
|
|
Does SQL Server have memory
leaks? How can I tell? Why is SQL Server using so much memory? 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 issues see later. Try applying the latest service pack if you haven't already. Remember that SQL Server will grab memory up to the amount you have specified via sp_configure. This is in 2K pages (for SQL 6.5). This amount does not include any amount for tempdb in ram (6.5 and below only) or for the SQL kernel or some other 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 for 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 any limit you set) 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. 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 (+15MB) it should have allocated then it may be responsible for a memory leak. You could also check for handles and threads being leaked as these could also potentially be the cause of memory leaks. 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 MS Technet or the knowledge base site. ----------------------------------- 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 MS 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 MS PSS for a post-SP1 hot-fix) |
All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.