memory issue: manual memory settings
-
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?
-
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, NYhttp://www.linkedin.com/in/kurtwzimmerman
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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)
-
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?
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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.
---------------------------------------------------------------------
-
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!
-
Just in case the obvious has been missed... The max server memory setting won't take effect until you restart the sql server instance. 🙂
-
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
🙂
-
JudithK (2/7/2014)
Just in case the obvious has been missed... The max server memory setting won't take effect until you restart the sql server instance. 🙂The Max server memory setting takes effect immediately, no restart is necessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply