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

SQL Server Memory - High Expand / Collapse
Author
Message
Posted Tuesday, December 18, 2012 7:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 12, 2014 2:00 PM
Points: 87, Visits: 546
Hello

I am running a physical server dedicated to SQL Server 2008 SP2 on
windows 2008 Enterprise Edition 64bit SP2 with 48GB RAM and my memory usage is staying at 47.2 GB.

Running EXEC sp_configure shows

name max server memory (MB)
minimum 16
maximum 2147483647
config_value 44000
run_value 44000


If I am reading this correctly, the max setting is already at the recomended setting of 44000 for 48 GB of physical RAM.

Is my memory usage normal? Do I have a problem - what should I look into?

If I need to get cooperation from the windows admin, where do I ask him to start looking?

In Resource Monitor, sqlservr.exe is using 46,710,000 KB of memory so I'm sure the problem is SQL.

Any clarification is appreciated.

Thanks
Dave
Post #1397787
Posted Tuesday, December 18, 2012 7:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:45 AM
Points: 5,221, Visits: 5,120
SQL will use memory from outside of the buffer pool, things like CLR for example use additional memory, so even though it is capped, it can go over by usually 2GB depending on the RAM needed.


I would recommend reading Chapter 4 of the Accidental DBA guide (link in my signature) which goes into memory management.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1397811
Posted Tuesday, December 18, 2012 7:54 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: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
NJDave (12/18/2012)
Is my memory usage normal? Do I have a problem - what should I look into?


Yes it is. No, you do not.
At most you may want to drop max server memory down a little. 44GB on a server with 48GB is a little high.



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 #1397813
Posted Tuesday, December 18, 2012 9:31 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 12, 2014 2:00 PM
Points: 87, Visits: 546
Thank you for the help - reading Chapter 4 now.

Thanks
Dave
Post #1397880
Posted Tuesday, December 18, 2012 10:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:26 PM
Points: 2,330, Visits: 3,509
44 out of 48 is definitely too high. Keep in mind that the OS needs some RAM to manage the RAM itself.

Assuming no other software using significant RAM is running on that box to any significant degree, I'd suggest capping at no more than 40GB.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1397895
Posted Tuesday, December 18, 2012 11:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 12, 2014 2:00 PM
Points: 87, Visits: 546
Thank you - I will cut it down to 40gb.

44 is what it was set at and I see that setting advised here...

http://www.sqlservercentral.com/blogs/glennberry/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008/

Thanks
Dave
Post #1397954
Posted Tuesday, December 18, 2012 1:16 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: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
I prefer a conservative approach to memory. Set max server memory lower (the 40 out of 48) and if monitoring shows available memory, increase it slightly. Repeat until happy.


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 #1397999
Posted Tuesday, December 18, 2012 2:26 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 2:25 PM
Points: 332, Visits: 537
Suppose Says you Have XGB of memory
I prefer 6GB for OS and the Remaining (X-6)GB for SQL by default on any server.
This will not work for Ram less than 10GB.
Post #1398045
Posted Tuesday, December 18, 2012 3:55 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: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
That's too much for low memory servers and too little for high memory servers.

Chapter 4: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/



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 #1398085
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse