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


memory issue: manual memory settings


memory issue: manual memory settings

Author
Message
Benki Chendu
Benki Chendu
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1929 Visits: 515
we have a server which has SQL 2008 R2 on it.
Its in a cluster and we have set it up in such a way that each instance has a dedicated node and one extra node.
The physical server has 64 GB RAM.
We had dynamic memory settings and the free memory was always taking a hit and it used to be just 300-400 MB.
I changed the dynamic memory to manual one where I fixed the maximum memory to 56 GB leaving the other 8 GB for OS and other processes. I knew this wouldnt fix the issue. Even now, the free memory is 300-400 MB.
Only difference that I could see is, SQL Server.exe process was earlier occupying entire RAM but now, I see it at around 59 GB.
How do I fix this issue?
How can I make sure I have atleast good amount of RAM free?
Kurt W. Zimmerman
Kurt W. Zimmerman
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2662 Visits: 1400
What else do you have running on that box? 8gb alone should be plenty to keep the OS happy. However if there is something else requiring extra memory you will want to allow for it as well.

Some of my smaller machines I make sure I have at least 2gb for the OS but keep in mind there isn't much else running on that box.

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
GilaMonster
GilaMonster
SSC Guru
SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)

Group: General Forum Members
Points: 229420 Visits: 46344
If SQL is using too much memory, if there's too little free memory left on the server, reduce max server 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


Benki Chendu
Benki Chendu
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1929 Visits: 515
GilaMonster (1/29/2014)
If SQL is using too much memory, if there's too little free memory left on the server, reduce max server memory.

Hi,

I dont see any difference between available free memory when SQL max memory was dynamic and even now when I made the max memory to be limited to 56 GB(8 GB left for OS)
Benki Chendu
Benki Chendu
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1929 Visits: 515
with reference to the above mentioned issue, please see how it looks in the task manager.
There is hardly any memory left for OS
Is there a way I can have some memory left free?



GilaMonster
GilaMonster
SSC Guru
SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)

Group: General Forum Members
Points: 229420 Visits: 46344
Don't use Task Manager to check SQL's memory, it lies. The labels on the values are also not as clear as they could be.

If SQL is still using too much memory, reduce max server memory slightly. Repeat until you're happy with the memory situation.

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


george sibbald
george sibbald
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25188 Visits: 13701
you have 788 MBytes available for processes to use, this is the figure you should be concerned with, rather than free memory.

see http://brandonlive.com/2010/02/21/measuring-memory-usage-in-windows-7/
for one description of what these numbers mean.

---------------------------------------------------------------------
Jacob Wilkins
Jacob Wilkins
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7635 Visits: 10243
Echoing what Gail said, if you want to free up memory by changing what SQL Server uses you need to reduce max server memory until you get it to a level you're happy with.

Before SQL Server 2012 (so this applies to your instance), the max server memory setting did not apply to all areas of SQL Server memory, so while 56 GB would be the cap for the various caches, it doesn't include things like CLR, multipage allocations, and some other pieces. You can see http://blogs.msdn.com/b/sqlosteam/archive/2012/07/12/memory-manager-configuration-changes-in-sql-server-2012.aspx for a quick explanation.

That's why you'll see SQL Server using more than 56 GB if you query, say, sys.dm_os_process_memory on your instance.

Cheers!
JudithK
JudithK
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 2319
Just in case the obvious has been missed... The max server memory setting won't take effect until you restart the sql server instance. :-)
Jacob Wilkins
Jacob Wilkins
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7635 Visits: 10243
To be clear:

The min server memory and max server memory options are advanced options. If you are using the sp_configure system stored procedure to change these settings, you can change them only when show advanced options is set to 1. These settings take effect immediately without a server restart.


From http://technet.microsoft.com/en-us/library/ms178067(v=sql.105).aspx

:-)
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