SQL Server 2008 R2 Memory Hog

  • I have an instance of SQL Server 2008 R2 installed on a VM with 4 cpus and 16 GB of RAM. It currently uses 15.5GB of memory and I can't get it to use less. I'm having problems logging into the VM because there's not enough memory for remote desktop to function properly. I've tried setting the "Maximum server memory" setting and currently have it at 4096 but it seems to do nothing. I'm still maxing out the memory. Can anyone help?

  • What process are you using to determine what amount of RAM the SQL Server instance is using?

    The maximum server memory is only for what the buffer pool will allocate. At the other end the minimum setting is to specify when SQL Server has to release buffer pool memory it should not go below that value.

    If you are using Task Manager to view the amount of RAM the "sqlservr.exe" process is using for your instance that is showing RAM for more than just the buffer pool.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • Yes, I'm using task manager. I'm not sure what the "buffer pool" is but what I do know is that if I do the following commands

    NET STOP "SQL Server Agent (BRKSQL02)"

    NET STOP "SQL Server (BRKSQL02)"

    then task manager shows that the total Memory used goes from 15.6GB to 800MB and other programs (such as remote desktop) are then usable, that is for the next hour or so when SQL Server hogs it back up again. Is changing the amount of buffer pool the wrong way to limit SQL Server? Should I have something in the "Minimum server memory"? Currently it's a 0.

    Thanks for your response!

  • Min server memory is fine at 0. What's the exact value for max server memory?

    Run

    exec sp_configure 'show advanced', 1

    RECONFIGURE

    exec sp_configure 'max server memory'

    What's the exact output?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • When I run that code I get:

    name__________________minimum_____maximum____config_value____run_value

    max server memory (MB)____16______2147483647______4096_________4096

  • Ok, that is weird.

    Will have to hunt for a memory breakdown script to see what's happening.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • DKY (3/9/2014)


    I have an instance of SQL Server 2008 R2 installed on a VM with 4 cpus and 16 GB of RAM. It currently uses 15.5GB of memory and I can't get it to use less. I'm having problems logging into the VM because there's not enough memory for remote desktop to function properly. I've tried setting the "Maximum server memory" setting and currently have it at 4096 but it seems to do nothing. I'm still maxing out the memory. Can anyone help?

    Is SQL Server 32 bit or 64 bit ? Did you have Lock Pages in Memory enabled ?

    And did you restart the SQL server after you changed the Max Memory to 4 GB ? This is to release excessive memory grabbed by SQL.

    --

    SQLServer

  • sqlbuddy123 (3/10/2014)


    And did you restart the SQL server after you changed the Max Memory to 4 GB ? This is to release excessive memory grabbed by SQL.

    Max server memory does not require a restart. It is effective immediately.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I believe it's 64 bit (even though it seems to be installed in the 86 program files folder).

    I followed this process for the lock pages in memory and there are no users or groups, it's blank.

    http://msdn.microsoft.com/en-us/library/ms190730.aspx

    The server was restarted over the weekend and it's still having this issue.

  • GilaMonster (3/10/2014)


    sqlbuddy123 (3/10/2014)


    And did you restart the SQL server after you changed the Max Memory to 4 GB ? This is to release excessive memory grabbed by SQL.

    Max server memory does not require a restart. It is effective immediately.

    Yeah, I know. This is true when going from lower value to higher value and not necessarily Vice Versa. That's why I mentioned

    This is to release excessive memory grabbed

    http://social.msdn.microsoft.com/Forums/en-US/7e245bab-b694-4db1-b148-bb31d8b20cea/sql-server-memory-setting-restart-required?forum=sqltools

    --

    SQLBuddy

  • Are there other instances installed on this box?



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Actually, yes and no. I inherited this install without the password for the initial instance. I've since stopped the service (a long time ago) and am unable to log in to completely remove the instance but it shouldn't be taking up any memory if it's not running, right?

  • What are your Memory Settings right now ? How much memory is SQL Server consuming in the Task Manager ?

    What is the SQL Server Build number ?

    Check the o/p of this query ..

    select [Memory Used KB] = (pages_allocated_count * page_size_in_bytes)/1024 from sys.dm_os_memory_objects where type = 'MEMOBJ_RESOURCE'

    Check the Values of these counters

    Total Server Memory

    Target Server Memory

    Available Mbytes

    Performance object: Process

    Counter: Private Bytes

    Instance: sqlservr

    Performance object: Process

    Counter: Working Set

    Instance: sqlservr

    --

    SQLBuddy

  • DKY (3/10/2014)


    Actually, yes and no. I inherited this install without the password for the initial instance. I've since stopped the service (a long time ago) and am unable to log in to completely remove the instance but it shouldn't be taking up any memory if it's not running, right?

    Right. When did you stop the instance and when did you start getting this issue ?

    --

    SQLBuddy

  • sqlbuddy123 (3/10/2014)


    GilaMonster (3/10/2014)


    sqlbuddy123 (3/10/2014)


    And did you restart the SQL server after you changed the Max Memory to 4 GB ? This is to release excessive memory grabbed by SQL.

    Max server memory does not require a restart. It is effective immediately.

    Yeah, I know. This is true when going from lower value to higher value and not necessarily Vice Versa. That's why I mentioned

    This is to release excessive memory grabbed

    It is true for both cases. When the Lazy Writer task runs it checks the value for Max and Min and adjusts the targets and the notifies the clerks to adjust their memory accordingly.



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply