March 15, 2013 at 7:06 am
For SQL 2008, the min and max server memory settings are limited by total memory. If my server has 4gb memory and 12 processrors whats the max value i can give?
March 15, 2013 at 8:48 am
max memory is not limited to the total memory on the server and the highest you CAN set it would be the default of 2147483647. The sql server will just page out if it requires more memory then physically available.
The question should be: How high SHOULD max memory be?
This would depend on factors such as size of data, workload, whether other services are running on the server. check these articles:
http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/
http://www.brentozar.com/archive/2012/06/sql-server-max-memory-myths-video/)
March 15, 2013 at 9:11 am
Reconfirming... so i can give max memory 2147483647 even though i have 4gb memory only. SQL would get memory pressure and page out only if it exceeds 2147483647. Is this assumption correct?
March 15, 2013 at 9:48 am
if you don't change max memory setting , 2147483647(2 petabytes) is what it is set to out of the box.
if you leave it at this setting (not recommended) sql server will page out to disk if it can not access any physical data. For example if on you 4GB server windows and any other software installed is using 2GB of memory then only 2GB will be available to SQL Server. If sql server need to access more than 2GB then it will get that memory space from the page file. If sql server is using the page file it will slow down the server considerably and you want to avoid this situation.
In you case, I personally would start with 2GB(2048 MB) as max memory setting and then monitor memory for a bottleneck to see if max need to be increased.
March 15, 2013 at 10:31 am
balasach82 (3/15/2013)
Reconfirming... so i can give max memory 2147483647 even though i have 4gb memory only. SQL would get memory pressure and page out only if it exceeds 2147483647. Is this assumption correct?
No.
If you set max server memory to more than the physical memory, SQL will allocate and allocate until the OS says to stop (due to memory pressure). SQL will not page itself out, which is good, because paging SQL's buffers to disk is a bad thing (SQL uses memory to avoid the cost of going to disk, Using disk that's pretending to be memory to avoid the cost of going to disk is as silly as it sounds)
The only time SQL will be paged out is when the OS is under memory pressure, it asks SQL to reduce its memory usage and SQL either doesn't, or doesn't reduce fast enough. The OS will then page SQL's memory to disk. This is a bad thing.
Generally you want to set max server memory to a value that's low enough that the OS doesn't get starved for memory. On a 4GB server, I'd set max server memory no higher than 3GB and that's assuming that there's nothing else running on that server
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
March 16, 2013 at 7:43 am
Is your Server/SQL Server 32 or 64 bit? That will make a difference in terms of the percentage of your 4GB SQL Server can actually use by default.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 16, 2013 at 10:03 am
Its windows 2008, 64 bit
March 16, 2013 at 11:14 am
In that case I would go with a 3GB max memory setting as well provided SQL Server is the only thing running on the machine, and monitor available memory to ensure you are leaving enough for the OS to handle all non-SQL-Server-tasks.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 16, 2013 at 11:49 am
As a side-note, 12 CPUs and 4GB of RAM does seem a bit lopsided.
If it is a modern-enough server (2 sockets of 6 cores perhaps?) then surely it can take a bit more RAM, which is relatively inexpensive nowadays;-)
Cheers,
JohnA
MCM: SQL2008
March 17, 2013 at 6:20 am
@sqlcharger: Lopsided is a good way of putting it! It sounds very badly sized at the time of purchase!
Anyway, I use a practice of reserving 2GB for the OS and any applications that may be running independently of the database software. Whatever remains is what I could theoretically allocate to SQL Server. Obviously though, if you are running more that one instance on the same box then you are likely to have an assymetric allocation of that remaining memory among the instances. To that end, you would need to ensure that whatever method you use to allocate memory does not starve out any other instance on the box.
March 17, 2013 at 9:48 am
'lopsided' is my diplomatic way of saying : WTF dude 😛
2-4GB per core I've found to be a half-decent rule of thumb for many workloads.
Depending on code quality I may even go over 10GB/core, but here we are way below 1GB/core
After allowing for the OS, we're talking < 200MB/core. Bit low 😉
Cheers,
JohnA
MCM: SQL2008
March 17, 2013 at 12:26 pm
kevaburg (3/17/2013)
Anyway, I use a practice of reserving 2GB for the OS and any applications that may be running independently of the database software.
Typically that's too much at the low end (unless SQL's sharing with other apps) and too little at the higher end.
Chapter 4: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/ There's a decent starting formula in there.
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
March 17, 2013 at 12:37 pm
Typically that's too much at the low end (unless SQL's sharing with other apps) and too little at the higher end.
The one thing I don't like is the "Rule of Thumb" approach to sizing which is why I size as I do. The problem is that DBAs are very rarely privvy to information about the work the Server Administrators do. That means software can be installaed without the DBA being aware of it.
One example was the installation of anti-virus software on a particular server without including an exception for mdf and ldf datafiles. Each time the files were accessed the AV software scanned the file for viruses. This situation led to SQL Server slowdown but because it was still usable (with some patience involved from the end users!) because enough buffer in memory was allocated to allow for a problem such as this.
March 17, 2013 at 12:57 pm
Typically that's too much at the low end (unless SQL's sharing with other apps) and too little at the higher end.
The one thing I don't like is the "Rule of Thumb" approach to sizing which is why I size as I do. The problem is that DBAs are very rarely privvy to information about the work the Server Administrators do. That means software can be installed without the DBA being aware of it and memory starve the OS on systems with multiple, over-sized instances.
One example was the installation of anti-virus software on a particular highly transactional server without including an exception for mdf and ldf datafiles. Each time the files were accessed the AV software scanned the file for viruses. This situation led to SQL Server slowdown but it was still usable (with some patience involved from the end users!) because enough buffer in memory was allocated to allow for a problem such as this. Granted, I could allocate 1GB for the OS and things will tick along nicely, but none of us live in the ideal world and experience has dictated I need to prepare as best as is reasonably practicable for problems generally outside of my control.
Although I like the link you posted, I am also not an accidental DBA! I know and understand cause and effect when I do something that can have an instance- or database-wide influence. I understand to an extent your reasoning about allocating too much at the lower-end but too little at the top end? Have I calculated too little memory at the instance-level if it is constantly 99% in use? I normally plan for an RMS value of between 80%-90% for highly transactional databases but 99% at peak for me would indicate perfect allocation. An instance gets what it needs, no more and no less. If my monitoring indicates 100% utilisation for more than 15% of a period then I allocate more until it meets my requirements (or until my end-users are happy! :w00t:.
I have worked on far too many over-sized and under-utilised servers to accept that throwing resources at a potential problem is the answer.
I hope that didn't sound like a rant but "Rule of Thumb" is something I regard as a dangerous precedent. The only answer to memory allocation is experience, knowledge and a keen eye, not necessarily a solution someone else has thought up.
March 17, 2013 at 1:27 pm
kevaburg (3/17/2013)
I understand to an extent your reasoning about allocating too much at the lower-end but too little at the top end?
Absolutely. The OS needs memory to manage memory, so reserving 2GB for the OS is fine (over generous perhaps) at 4GB, great at 8, probably OK at 16, getting tight above that. It risks starving the OS of memory, leaving the OS and SQL in a perpetual reduce-increase-reduce-increase memory cycle or even to OS instability, especially if locked pages are enabled or if min and max server memory are set to the same thing.
Jonathan's guideline (which, btw is based on the experience of several people who know a lot about SQL, not something one person with minimal experience thought up) is a generous one, probably giving too much to the OS. He does also discuss what counters to look at so you can tell whether to adjust up or down so that you don't risk starving the OS but also don't end up leaving GB of memory unallocated and going to waste.
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 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply