SQL Server 2008 memory usage exceeds configured Max Server Memory

  • khandelwal_arun

    SSC Eights!

    Points: 849

    Hi,

    My server have 32GB RAM and it's bind with 28GB to SQL Server but when I run the test load over 20hrs it's exceed the configured value.

    And we are using shared memory connection with Applicaiton.

    Can someone light on this, this is memory leak with SQL server or anything else...

    If this is memory leak then Microsoft has released any hotfix for it?

    Thanks,

  • okbangas

    SSChampion

    Points: 11773

    First thing to come into my mind: Do you have multiple instances on the server? If so, you should be aware of the fact that max server memory is a per instance setting.

    Apart from this, where/how do you look up the amount of memory allocated to SQL Server?



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • khandelwal_arun

    SSC Eights!

    Points: 849

    No there is only single instance and I bind memory in database level I means in property -> Memroy -> Max Memory setting.

    Thanks for your prompt response.

    Regards,

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Yup. Perfectly normal.

    Max server memory is the max size of the buffer pool, the memory area that contains the data cache, plan cache and a whole bunch of other caches. SQL also uses memory outside the buffer pool for things like backup buffers, thread stack, linked server drivers, CLR and a few other things. This is outside of the buffer pool, so it's not part of 'max server memory'

    On 32-bit SQL, that's referred to as MemToLeave (memory to leave unallocated when assigning the buffer pool). On 64 bit that term has no meaning.

    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
  • khandelwal_arun

    SSC Eights!

    Points: 849

    Thanks for you prompt reply...

    Is anyway we can bind SQL Server memory which will not exceed configured value.... in 64-bit environment..

    Regards,

  • Gail Shaw

    SSC Guru

    Points: 1004424

    You can only control the buffer pool. The non-buffer pool memory should be relatively small, unless you're doing weird things with CLR. If SQL as a whole is using too much memory, reduce the size of the buffer pool as that's you can control

    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
  • benjamin.reyes

    SSCertifiable

    Points: 5189

    For example on one of my machines I have max memory set at 26,624 MB, which is my buffer size, the plan cache is an additional 4,408 MB. I don't believe there is a setting to force the plan cache to be smaller, only the buffer cache, you really shouldn't be running anything else on your sql box anyway.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    benjamin.reyes (10/28/2011)


    For example on one of my machines I have max memory set at 26,624 MB, which is my buffer size, the plan cache is an additional 4,408 MB.

    The plan cache is part of the buffer cache, so your plan cache takes up 4408MB of the 26624MB buffer pool. What's outside of the buffer pool is things like CLR memory, backup buffers, thread stacks, linked server drivers and a couple other things. They're generally smallish memory allocations

    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
  • benjamin.reyes

    SSCertifiable

    Points: 5189

    Is there a way to find how much memory each of these specific components use?

  • okbangas

    SSChampion

    Points: 11773

    You mean something like this

    select

    SUM(pages_allocated_count)*8 as MemKB,

    type

    from

    sys.dm_os_memory_objects

    group by

    type

    order by

    MemKB desc

    select

    SUM(pages_allocated_count)*8 as MemKB,

    type

    from

    sys.dm_os_memory_cache_entries

    group by

    type

    order by

    MemKB desc



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • alanspeckman

    Hall of Fame

    Points: 3344

    I was going to add a topic on this, but then I saw this thread.

    In the following article, it talks about setting min server memory based on an equation, 24k * numusers.

    http://technet.microsoft.com/en-us/magazine/ff621496.aspx

    It also states "recommended maximum value for stand-alone servers is at or near total RAM".

    I have never heard of either of these. I set min to be 1gb myself, so the OS doesn't take all the RAM back from SQL and SQL can't run. I have also read M$ recommends min to be 512mb somewhere.

    As for the max set to total RAM, I have never read that before. I usually give it 4gb to 8gb on most of my servers, or limit it to 2gb on small servers.

    I'm asking what real-world DBAs use for Min and Max, are their rules the community uses, or a Best Practice that is used depending on certain conditions, maybe something laid out in a Matrix?

  • SpringTownDBA

    SSCertifiable

    Points: 6756

    (BALLPARK Numbers. No two sql server instances have the same workload.)

    Dedicated to 1 instance of SQL Server (leaving 1.5-4 GB for the OS):

    GB RAM/Max Memory Setting

    8/6

    16/14

    32/29

    64/60

    128/124

    If you've got more stuff running (multiple instances, SSAS, SSRS, SSIS, etc) then you'll need to figure out what makes sense for your situation.

    For example, If you've got a server with 32 GB, running SQL, SSRS, SSAS and SSIS.

    After watching memory usage, you determine that the SSIS packages max out around 800 MB total at any point in time. SSRS seems to stay around 1.2 GB.

    So, do some math, 32 Gb - 1.5GB (for the OS) -.8GB (SSIS) - 1.2GB (SSRS) = 28.5GB for SQL and SSAS to split.

    You experiment and determine that SQL getting 18 GB and SSAS getting 10 is a good split and those settings become your max memory settings. You may also decide to set min memory in case something bad happens (rogue SSRS report), so you decide that sql should always have at least 8 GB, and SSAS at least 6.

    You've covered your bases, and you've got 14GB of wiggle room if something goes haywire, but on a good day, most of your ram will be utilized without paging.

  • Devendra (Dev) Shirbad

    SSC-Forever

    Points: 42488

    So, do some math, 32 Gb - 1.5GB (for the OS) -.8GB (SSIS) - 1.2GB (SSRS) = 28.5GB for SQL and SSAS to split.

    It's good to start but there is no formula to get this number. You can guess (yes, it's a right word) some number based on your experience, which you MUST verify with different data load.

    Take the same example:

    8GB (SSIS)

    1.2GB (SSRS)

    Nobody runs SSIS packages all day. So you may count it for SQL Server database.

  • okbangas

    SSChampion

    Points: 11773

    Nice to see that I am nobody 🙂



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • Devendra (Dev) Shirbad

    SSC-Forever

    Points: 42488

    okbangas (11/17/2011)


    Nice to see that I am nobody 🙂

    LOL 😀

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

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