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 12»»

memory issue: manual memory settings Expand / Collapse
Author
Message
Posted Tuesday, January 28, 2014 1:39 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Today @ 12:14 PM
Points: 188, Visits: 327
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?
Post #1535620
Posted Tuesday, January 28, 2014 1:54 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 2:10 PM
Points: 957, Visits: 1,282
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
Post #1535627
Posted Wednesday, January 29, 2014 12:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 41,525, Visits: 34,442
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 2008, MVP
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

Post #1535746
Posted Wednesday, January 29, 2014 6:59 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Today @ 12:14 PM
Points: 188, Visits: 327
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)
Post #1535887
Posted Wednesday, February 05, 2014 12:13 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Today @ 12:14 PM
Points: 188, Visits: 327
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?


Post #1538325
Posted Wednesday, February 05, 2014 2:22 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 41,525, Visits: 34,442
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 2008, MVP
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

Post #1538378
Posted Wednesday, February 05, 2014 3:43 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 5,847, Visits: 12,579
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.


---------------------------------------------------------------------

Post #1538394
Posted Thursday, February 06, 2014 3:15 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 11:06 AM
Points: 166, Visits: 374
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!
Post #1538924
Posted Friday, February 07, 2014 9:25 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 1:51 AM
Points: 17, Visits: 2,237
Just in case the obvious has been missed... The max server memory setting won't take effect until you restart the sql server instance.
Post #1539248
Posted Friday, February 07, 2014 10:01 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 11:06 AM
Points: 166, Visits: 374
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

Post #1539274
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse