Memory settings

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » CLR Integration and Programming. » Memory settings

Memory settings

Rate Topic

Display Mode

Topic Options

Author

Message

sqlserver12345

Posted Monday, October 31, 2011 12:21 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 12, 2012 5:05 PM
Points: 120, Visits: 383

Details:
Physical memory:6GB
64bit OS
windows 2008
sqlserver 2008
2 processors/2.67 Ghz

I have a windows 2008 and sql server 2008 on it.
Currently sqlserver.exe is using up all the memory.It fluctuates between 90 to 95% of the memory present.(Physical box not virtual.)
I would like to set the min and max memory utilised by sql server .How to calculate the min and max values.Can I get any t-sql to calculate these min /max values

Task manager shows:CPU used:1% to 5%
Physical memory :5.77 GB.Once I restart the sqlserver.exe service,it comes down to 2.0Gb physical memory used.Obviously sql server is not releasing the memory back to the OS once it grabs the memory.How do I prevent this from happenning.Are there any repurcussions if I leave it the way it is presently.

Thanks

Post #1198244

opc.three

Posted Tuesday, November 01, 2011 9:01 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 4:37 PM
Points: 3,498, Visits: 5,589

What does this say?

SELECT  *
FROM    sys.configurations
WHERE   name = 'max server memory (MB)' ;

You can set the max amount the buffer pool can use by setting this option...however if SQL Server is grabbing more memory because of SQL CLR operations there is nothing you can do about that. It's just like any other .NET app...if it needs memory it will request it from the OS.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Make everything as simple as possible, but not simpler. --Albert Einstein

We should be careful to get out of an experience only the wisdom that is in it - and stay there, lest we be like the cat that sits down on a hot stove
lid. She will never sit down on a hot stove lid again... but also, she will never sit down on a cold one any more. --Mark Twain

nanos gigantium humeris insidentes --Bernard of Chartres

How Standards Proliferate ~ Wisdom of the Ancients ~ Tradition ~ Forgot Algebra

Post #1198737

sqlserver12345

Posted Wednesday, November 02, 2011 11:51 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 12, 2012 5:05 PM
Points: 120, Visits: 383

Do the appdomain errors mean issues with the server/database?

Does this error mean trouble?
Error:appdomain is marked for unload due to memory pressure.

Post #1199529

opc.three

Posted Wednesday, November 02, 2011 11:54 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 4:37 PM
Points: 3,498, Visits: 5,589

sqlserver12345 (11/2/2011)

Do the appdomain errors mean issues with the server/database?

Does this error mean trouble?
Error:appdomain is marked for unload due to memory pressure.

It can mean trouble.

What are the results of the query I posted previously?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Make everything as simple as possible, but not simpler. --Albert Einstein

We should be careful to get out of an experience only the wisdom that is in it - and stay there, lest we be like the cat that sits down on a hot stove
lid. She will never sit down on a hot stove lid again... but also, she will never sit down on a cold one any more. --Mark Twain

nanos gigantium humeris insidentes --Bernard of Chartres

How Standards Proliferate ~ Wisdom of the Ancients ~ Tradition ~ Forgot Algebra

Post #1199534

sqlserver12345

Posted Thursday, November 10, 2011 12:30 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 12, 2012 5:05 PM
Points: 120, Visits: 383

Query result:
1544
max server memory (MB)
2147483647
16
2147483647
2147483647
Maximum size of server memory (MB)
1
1

Post #1203796

sqlserver12345

Posted Thursday, November 10, 2011 12:32 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 12, 2012 5:05 PM
Points: 120, Visits: 383

Query result:
1544
max server memory (MB)
2147483647
16
2147483647
2147483647
Maximum size of server memory (MB)
1
1

Post #1203798

opc.three

Posted Thursday, November 10, 2011 10:17 PM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 4:37 PM
Points: 3,498, Visits: 5,589

Please read this article and follow its recommendations for setting max memory.

http://sqlserverperformance.wordpress.com/2009/10/29/suggested-max-memory-settings-for-sql-server-20052008/

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Make everything as simple as possible, but not simpler. --Albert Einstein

We should be careful to get out of an experience only the wisdom that is in it - and stay there, lest we be like the cat that sits down on a hot stove
lid. She will never sit down on a hot stove lid again... but also, she will never sit down on a cold one any more. --Mark Twain

nanos gigantium humeris insidentes --Bernard of Chartres

How Standards Proliferate ~ Wisdom of the Ancients ~ Tradition ~ Forgot Algebra

Post #1203907

« Prev Topic | Next Topic »

Permissions