Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Total Server Memory Of SQLServer Expand / Collapse
Author
Message
Posted Saturday, April 12, 2014 6:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 4:36 AM
Points: 108, Visits: 1,097

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?

Post #1561190
Posted Saturday, April 12, 2014 6:39 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 15,618, Visits: 27,998
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1561196
Posted Saturday, April 12, 2014 10:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 4:36 AM
Points: 108, Visits: 1,097

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?

Post #1561262
Posted Sunday, April 13, 2014 5:37 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 15,618, Visits: 27,998
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1561272
Posted Sunday, April 13, 2014 11:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 4:36 AM
Points: 108, Visits: 1,097

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?
Post #1561323
Posted Monday, April 14, 2014 4:13 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 15,618, Visits: 27,998
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1561417
Posted Tuesday, April 15, 2014 7:40 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:02 PM
Points: 1,231, Visits: 6,617
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.
Post #1561873
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse