April 12, 2014 at 6:21 am
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?
April 12, 2014 at 6:39 am
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
April 12, 2014 at 10:58 pm
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?
April 13, 2014 at 5:37 am
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
April 13, 2014 at 11:09 pm
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?
April 14, 2014 at 4:13 am
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
April 15, 2014 at 7:40 am
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