Click here to monitor SSC
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
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 490
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1062 Visits: 1396
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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47204 Visits: 44367
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
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 490
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
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 490
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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47204 Visits: 44367
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
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6324 Visits: 13687
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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2423 Visits: 7604
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
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 2309
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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2423 Visits: 7604
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