how determine max memory setting

Question

how determine max memory setting

Denise McMillan

SSCertifiable

Points: 6216
More actions
March 17, 2011 at 11:18 am

#233349

How should you determine the max memory setting? For example, I have server with 8 Gb ram. It is Windows 2003, 32 bit. We have SQL Server 2008 sp1 installed.
Should I set the max memory to 6Gb, i.e. 6144 Mb?
Are there guidelines for setting this?
Thanks very much.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Glenn Berry is a trusted source:

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

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

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 2

Thanks, I have read that article. It lists suggestions for 64 bit, but not 32 bit.

In the discussions he also mentions that running x86/Windows 2003 will only be able to access 2Gb of ram (while os gets 2Gb) Does this mean it doesn't matter what I set the maxservermem to?

With 8Gb ram he suggests maxservermem setting of 6400. Should I try less than that? I don't want to make it so low that SQL Server can't function.

Thanks again very much for the help.

sturner SSC-Insane Points: 22411 More actions · Answer 3

In order to utilize any more than 4GB of memory on a 32 version you will need to enable AWE. The only thing SQL server can use it for is basically buffer cache. Data structures associated with connections and processes will need to be in the basic virtual memory space.

The probability of survival is inversely proportional to the angle of arrival.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

The max memory guidelines in the article should still be valid for 32-bit. The /3GB switch will add 1GB to directly addressable memory for SQL Server to get into which will help. AWE allows SQL Server to utilize more than the 3GB of natively addressable RAM by extending the addressable memory space through software extensions, however you'll still incur a slight performance penalty when accessing the AWE space when compared to a 64-bit system that can address more than 3GB natively for apps.

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

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 5

If it is recommended to change the max memory settng from the default, should I use 6Gb in this case then because we have 8Gb on the server?

Or because this is 32bit, should I set it to 2Gb or 3Gb?

We reboot this server weekly at 3:30am on Mondays. Our memory related errors don't happen until just before the reboot.

Would it help if we rebooted twice a week?

Thanks for all the help.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

6GB would be a fine place to start...max memory is an art not a science. Glenn has 6400MB recommended on a system with 8GB of RAM which is a shade more than 6GB.

If you're having memory issues to the point where you need to reboot your system weekly, and are considering making it twice weekly, then you have other issues. What else is running on your system that could be holding onto memory and not releasing it? Are you making heavy use of Linked Servers to access non-MSSQL data sources?

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

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 7

Thanks very much for the advice. The memory errors have only started since an upgrade of our SQL Server and our application about a month ago. The application has Web components and does use linked servers but I don't think extensively. The application has changed though.

I'm afraid the problem may be a combination of all that plus the fact the we have this version/edition of SQL on 32 bit, Windows 2003.

I haven't run across this kind of issue before and have been reading a lot online.

Enough to get myself confused and unsure of the best way to proceed.

And I don't want to just make changes on a production system. We have a test system but it doesn't completely match the live set up and has no activity going on.

Thanks again.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

Third-party drivers used via Linked Servers are a notorious source of memory leaks. Depending on how your app maintains its connections, the use of the procedure sys.sp_xml_preparedocument to prepare an XML document without an accompanying call sys.sp_xml_removedocument when done using the XML document is also worth mentioning as something to look for in your T-SQL code as that can cause large amounts of memory to be allocated and not freed for long periods of time.

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