Post reply

SQL Server Max Memory Verus Actual Usage

  • May 23, 2014 at 3:42 pm

    #286948

    I have a SQL Server that has its maximum server memory set to 5120 MB. However, in Resource Monitor, I can clearly see that the sqlservr.exe process is using about 2 GB more than that. I remember reading somewhere that the maximum meory cap is more of a "reccomendation" to SQL Server than an actual restriction. Is this true? If not, how can I account for the fact that the sqlservr.exe process is using more than the max cap?

    I can provide screenshots if needed.

    Thanks!

  • May 23, 2014 at 5:09 pm

    #1716279

    Can you check these two counters in perfmon?

    SQL Server, Memory Manager

    Target Server Memory (KB)

    Total Server Memory (KB)

    SQL Server, Memory Manager Object

  • May 24, 2014 at 10:45 am

    #1716325

    1) Don't use task manager/resource monitor to check SQL's memory

    2) The max server memory sets the buffer pool. SQL additionally uses a small amount of non-buffer memory for thread stacks, CLR, linked servers and a few other things.

    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

  • May 27, 2014 at 9:29 am

    #1716734

    Gila,

    Can you clarify for me a bit? Why is using task manager/resource monitor bad for SQL memory monitoring? Also, if SQL uses more memory to account for the items you mentioned, is there some place where I can see the amount being used so I can correlate all the numbers I am seeing?

    Thanks,

    Clint

  • May 27, 2014 at 10:50 am

    #1716768

    Clint-525719 (5/27/2014)

    Can you clarify for me a bit? Why is using task manager/resource monitor bad for SQL memory monitoring?

    Because it doesn't always show the correct values. Specifically it will read very, very low when SQL is using locked pages.

    Also, if SQL uses more memory to account for the items you mentioned, is there some place where I can see the amount being used so I can correlate all the numbers I am seeing?

    You can probably coaxes it out of the memory-related DMVs with some work (don't have query offhand). Otherwise Total Server Memory (perfmon) - buffer pool size = non-buffer memory.

    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

Viewing 5 posts - 1 through 4 (of 4 total)

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