January 25, 2013 at 6:31 am
Is there any value in setting a max server memory value for SQL Server 2008 R2 Express edition? I know express has a 1GB limitation, but the server properties do not reflect this limitation (still defaults the max server memory setting to 2TB).
My gut feeling is to set it and be prepared for anything..... I just want to be sure my servers are configured correctly 🙂
Thanks,
Kim
January 25, 2013 at 7:41 am
Express can't use more than 1 GB of memory, so unless you want to set max memory below that for some reason, no.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 25, 2013 at 7:41 am
I doubt anyone would argue against setting it but it's probably of limited value if you have multiple GBs of available RAM. It might be useful if your Express instances are commonly upgraded. If the upgrader neglected to set max memory post-upgrade then having had it set in Express would cover one base for you.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 25, 2013 at 2:48 pm
The best solution is to assign the OS 20% for making sure that it wont run in contention and that SQL server wont exhaust all the memory for itself.The rest depends on your requirements..this is a savior if you abide by!!
January 25, 2013 at 4:19 pm
Joechelad (1/25/2013)
The best solution is to assign the OS 20%
Very-very bad advice. 20% of 128GB is 25Gb, are you sure you want to allocate so much memory for OS?
January 26, 2013 at 2:49 am
Alexander Suprun (1/25/2013)
Joechelad (1/25/2013)
The best solution is to assign the OS 20%Very-very bad advice. 20% of 128GB is 25Gb, are you sure you want to allocate so much memory for OS?
For a dedicated SQL Server machine with 128 GB of memory, I'd probably set max server memory to around 110GB to start and adjust later.
The formula I use is not a straight 20%, it's more than 20% on lower memory servers and less on higher memory servers. There's a decent discussion in chapter 4 of http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply