SQL Server Performance

Memory Leak in sql server 2005

Discussion in 'SQL Server 2005 General DBA Questions' started by arijitnist, Mar 28, 2008.

  1. arijitnist Member

    Hi All,
    We are having performance problems with one of the servers on which sql server enterprise edition is installed. This is a windows 2003 server. On monitoring the performance we found that there was a memory leak due to sql server.
    When does a memory leak occur due to sql server and how to resolve this.
  2. Luis Martin Moderator

    What 2003 version and SP have you installed?. 32, 64?
    What Enterprise version 32 or 64?. What sp also.
    How many memory do you have and how sql memory is configured?
  3. melvinlusk Member

    What signs suggest that you have a memory leak?
    You may want to setup a SQL task that runs every 30 minutes or so that executes DBCC FREESYSTEMCACHE('ALL')
  4. LuckyStar New Member

    We have a very similar problem with our Server. We recently upgraded SQL 2000 to 2005 and added Reporting Services and Integration Services. We now have Windows Server 2003 with Sql Server 2005 SP2. When we initialze the server everything seems to run smoothly (sqlserver.exe memory usage round 700megsRAM, CPU usage round 5%). When we start to query the sql the process start to increase continuously untill it reach 1,6Gig and CPU usage raise and stays between 50-100% CPU utilization.
    We are struggling since few weeks now trying to see whats the problem. Any ieda of what is causing this behavior??
    Thank you very much for any help!
  5. Elisabeth Redei New Member

    To have "memory leak in SQL Server 2005" as a working theory while you are troubleshooting is a bad idea. Simply for the reason that there are a trillion far more probable causes for any given performance problem, I am assuming you are not using CLR components or any old custom extended stored procedures - those would be loaded into the memory address space of SQL Server and are more likely to leak.
    First of all, SQL Server has two memory areas. One will be committed up front at startup (some 2-300 MB on 32-bit and more on 64-bit) but the biggest part, the bufferpool, will gradually grow until it hits the roof (which would be your Max Server Memory setting). Once memory is committed in the buffer pool, it will stay there and only be decommitted if there is a memory shortage on the server. SQL Server, as any database system, is memory intensive and it wouldn't make sense to commit and decommit memory continously.
    The fact that Private bytes is kind of slowly just creaping up might look like a memory leak but it isn't.
    Look at the following counters:
    Process: Private Bytes - SQL Server - all committed memory for both "areas"
    Process: Working Set - SQL Server - committed memory that is mapped to physical RAM
    SQL Server Memory Manager: Total Server Memory - size of the bufferpool
    You probably need to step back and take a look at the overall performance and resource utilization on the server. Have a look at the "Troubleshooting Performance Problems in SQL Server 2005" whitepaper, www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx.
    Lucky Star, after the upgrade, did you run DBCC UPDATEUSAGE and sp_updatestats (documented in Books Online)? If you haven't, you should.
  6. LuckyStar New Member

    Wow! That is a quick respond!
    The application does use stored procedures. Do you think that this coudl cuase the memory leak? If so, is there any task or store proc that should be run regularly to free up memory?
    No, we didn't run the DBCC UPDATEUSAGE and sp_updatestats, I will definately look at it as well.
    Again thank you for the fast answer. This is really appreciated.
  7. madhuottapalam New Member

    If you have high CPU usage with less Read/write you may check for excessive recompilation of sps. As Elisabeth mentioned, Rebuild the indexes and update the statistics explicitly.
  8. satya Moderator

  9. Elisabeth Redei New Member

    Hi Luc,
    It's not stored procedures, it's EXTENDED stored procedures which is nothing more than a DLL. If you look in your errorlog you will see e.g. "Using xp_something.dll...".
    SELECT * from sys.dm_os_hosts tells you if you have EXTENDED stored procedures or CLR components loaded (you will have some builtin-in like SNI and MSDART loaded but that's OK).

Share This Page