locked
SQL Server 2005 64 bits memory leaks RRS feed

  • Question

  • I am using 2005 64 bits version and the memory consumption keeps going up to 8G and I have 16G memory. I read that this is not memory leak but a feature of SQL Server. If so, do I need to set a upper limit so that it won't cause problem and how to do that?
    Sunday, February 20, 2011 8:16 AM

Answers

  • http://msdn.microsoft.com/en-us/library/aa175282(v=sql.80).aspx

    sp_configure 'show advanced options', 1

    RECONFIGURE

    GO

    sp_configure 'max server memory', 12288   --12gb

    RECONFIGURE


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Tom Phillips Sunday, February 20, 2011 2:48 PM
    • Marked as answer by WeiLin Qiao Sunday, February 27, 2011 2:17 PM
    Sunday, February 20, 2011 8:55 AM
    Answerer
  • Not mentioned yet but worth stating to you is that you should ensure that you set your upper limit to allow for three things :-

    1. Operating system requirements
    2. SQL Server's requirement (obviously)
    3. Other applications running on server (including Anti Virus)

    So at very least you will have the first two requirements to address, the amount of memory you leave for the OS depends but 2GB is a good starting point, so should no other applications be running on the server, 14GB would be a good initial upper limit.

    Secondly you should also make sure you set an adequate minimum memory limit from default (this is especially relevant on SQL Servers that co-habit with other applications OR that run on Virtual infrastructure that uses balloon drivers - such as VMWare ESX server).


    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    • Marked as answer by WeiLin Qiao Sunday, February 27, 2011 2:18 PM
    Sunday, February 20, 2011 6:40 PM

All replies

  • http://msdn.microsoft.com/en-us/library/aa175282(v=sql.80).aspx

    sp_configure 'show advanced options', 1

    RECONFIGURE

    GO

    sp_configure 'max server memory', 12288   --12gb

    RECONFIGURE


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Tom Phillips Sunday, February 20, 2011 2:48 PM
    • Marked as answer by WeiLin Qiao Sunday, February 27, 2011 2:17 PM
    Sunday, February 20, 2011 8:55 AM
    Answerer
  • As Uri has suggested, please review your current configuration setting for the MAX SERVER MEMORY settings.

    SQL Server will acquire as much memory as is needed up to this value.

    What you are describing is not a memory leak.


    John Sansom | SQL Server DBA Blog | Twitter
    Sunday, February 20, 2011 9:00 AM
  • I am using 2005 64 bits version and the memory consumption keeps going up to 8G and I have 16G memory. I read that this is not memory leak but a feature of SQL Server. If so, do I need to set a upper limit so that it won't cause problem and how to do that?

    In addition to what John and Uri have said, if your consumption only goes up to 8g, either that is already set as the limit or you have a lot more memory than you need at the moment!

    Is there anything else running on the box, other SQL instances, web or exchange servers, app servers?

    Josh

     

    Sunday, February 20, 2011 4:53 PM
  • Not mentioned yet but worth stating to you is that you should ensure that you set your upper limit to allow for three things :-

    1. Operating system requirements
    2. SQL Server's requirement (obviously)
    3. Other applications running on server (including Anti Virus)

    So at very least you will have the first two requirements to address, the amount of memory you leave for the OS depends but 2GB is a good starting point, so should no other applications be running on the server, 14GB would be a good initial upper limit.

    Secondly you should also make sure you set an adequate minimum memory limit from default (this is especially relevant on SQL Servers that co-habit with other applications OR that run on Virtual infrastructure that uses balloon drivers - such as VMWare ESX server).


    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    • Marked as answer by WeiLin Qiao Sunday, February 27, 2011 2:18 PM
    Sunday, February 20, 2011 6:40 PM