SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Total Server Memory Of SQLServer


Total Server Memory Of SQLServer

Author
Message
mah_j
mah_j
Right there with Babe
Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)

Group: General Forum Members
Points: 725 Visits: 1262
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?
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99689 Visits: 33014
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 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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
mah_j
mah_j
Right there with Babe
Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)

Group: General Forum Members
Points: 725 Visits: 1262
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?
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99689 Visits: 33014
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
mah_j
mah_j
Right there with Babe
Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)

Group: General Forum Members
Points: 725 Visits: 1262
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?
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99689 Visits: 33014
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Greg Edwards-268690
Greg Edwards-268690
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4342 Visits: 8599
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search