Total Server Memory Of SQLServer

  • Hi

    Our server has the total memory almost 25 G and the memory that sql server has in task manager is 135 M and when I run this query in management studio

    SELECT counter_name,

    [object_name],

    cntr_value

    FROM sys.dm_os_performance_counters

    WHERE OBJECT_NAME = 'SQLServer:Memory Manager'

    AND counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)')

    the Target Server Memory (KB) and Total Server Memory (KB) has the same value and it is equal to the maximum server memory in the properties of server and it is 18 G

    so we just have less than 2 G free memory

    how can I know what is the total memory that sql is consuming?and how can I decrease it?

  • SQL Server will consume all the memory you allow it to have. That's how it works. You should allow it as much memory as you can on any give server while still setting aside some memory for the operating system (and SQL Server processes that run outside the normal memory space). I'd suggest reading Jonathan Kehayias article [/url]on max memory to figure out what you should set yours to.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thanks for your help

    but I am so confused about the setting the max server memory for SQL with the right value.

    I read the Memory Management chapter of Jonathan Kehayias ebook:

    "if the server only has 4 GB

    of physical memory (RAM) installed, that's going to be the upper limit for SQL Server.

    If a server has 64 GB of RAM, and the Windows operating system is Windows Server 2008

    64-bit Standard Edition, then the OS limit is 32 GB and that is the most that could be

    used by SQL Server."

    and also

    From this ebook I understand that "SQL Server will use as much memory as you can give it, and it will not release the memory that it has allocated under normal operations, unless the operating

    system has set the memory low resource notification flag" but I can't understand how does he calculate the max server memory option!!!

    What happen if I set it to the value that is grater than the physical memory (RAM) installed???

    In this case how much memory does sql consume?

  • The first couple of sentences in the article I linked gives you a calculation. 1gb to the OS for every 4gb of memory up to 16gb. So, if you have a 16gb machine, set SQL Server to have a max of 12gb. After 16gb he recommends 1gb for every 8gb of memory. So, if you have a 32gb machine you'd set SQL Server to 26gb, that's 4gb for the first 16gb and 2gb for the next 16. It's pretty straight forward as a recommendation.

    SQL Server can only consume as much memory as the OS will let it have. If you don't set a max, SQL Server and the OS will be swapping memory all the time. You don't want that. You just set the limit so that they're not fighting over the resources. SQL Server will then take all the memory up to that max value.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thank you for simplifying the calculation,so as he said if a server has 64 GB of RAM, and the Windows operating system is Windows Server 2008 64-bit Standard Edition, then the OS limit is 32 GB and that is the most that could be used by SQL Server.

    but if I use this calculation I come to the value of 54G for a 64G machine,is it different for above 32G?

  • No. But remember, it's just a guideline. You may need to adjust it up or down for your given situation. But it does give a starting point.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • mah_j (4/13/2014)


    Thank you for simplifying the calculation,so as he said if a server has 64 GB of RAM, and the Windows operating system is Windows Server 2008 64-bit Standard Edition, then the OS limit is 32 GB and that is the most that could be used by SQL Server.

    but if I use this calculation I come to the value of 54G for a 64G machine,is it different for above 32G?

    That is explaining the OS max. Standard vs. Enterprise.

    I had a server running Std, with 32 GB.

    To see more memory (i.e. scale the server), I would have to more to Enterprise version of the OS.

Viewing 7 posts - 1 through 6 (of 6 total)

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