I sometimes see clients that have questions about their memory settings for the database servers. Memory configuration is a funny thing, really, because there is no exact answer as to how much memory you should assign to SQL Server. I liken it to the amount of oil that goes into an engine. Depending on how you drive you may want to run your engine lean, or rich, so you might not add the full amount or you might add a little bit extra. Same with memory, it simply depends on how you “drive” your server.
The best you can do is give an educated guess for your initial configuration. Anyone telling you they know exactly how much memory you need is lying.
Yeah, I know, a bold statement there, especially for people who claim to be experts in memory configurations. Unless they have learned to predict the future then even the experts should agree that they simply cannot tell you a specific number. They can, however, give you a very good estimation as to how much you should start with.
Now, to add to the fun, there is no shortage of conflicting information on the intertubz with regards to memory and how to configure your SQL Server settings. For example, this link will tell you it is valid to set the ‘min server memory’ and ‘max server memory’ settings to the same value (UPDATED: Microsoft fixed the wording on this MSDN page shortly after it was brought it to their attention). And then this link will tell you to not set them to the same value. (Hint: One of them MUST be wrong.)
I like to keep things simple, so when I have a client ask me how they should configure the memory settings for a dedicated SQL Server instance (and by ‘dedicated’, I mean only a single instance on the server), I tell them this:
- Always look to leave at least 4GB for the OS (i.e., server has 32GB of RAM, and 32-4 = 28)
- Set your ‘max server memory’ setting to that number (i.e., that same 28GB RAM)
- Set the ‘min server memory’ to be at least half of the total server memory (i.e., 32/2 = 16GB RAM)
It is by no means an exact measurement. If you want to have more precise numbers then you need to set up some monitoring on the server for a period of time such that you feel comfortable with the workloads that have been measured. You may need to make adjustments in the meantime, but after a period of time you should be able to come up with a better range of min and max settings than what you started with initially.
One thing that people often forget nowadays is that SSDs need some memory for their block mapping too – that has to be taken into account when setting max server memory.
Ah, yes, I don’t have such knowledge yet…still stuck with spinning disks.
How much additional memory is needed? And do you have a blog post about this already? If so, link it here!
We have been using the memory setup above except for the ‘min server memory’. We have had to adjust the amount we give to the OS from time to time but generally that’s a good starting point.
We have not found the ‘min server memory’ setting to be that helpful. We lock pages in memory as a standard since all the servers are dedicated and don’t set the min. Are there any specific situations that you have seen the minimum setting help? Perhaps we have just been lucky…
Mike,
The min setting just means that SQL will hold onto that amount of memory once the level is reached. The situation where this would be helpful is if you need to ensure that SQL has a minimum amount of memory. So, if you have a database server that is also running some other applications you may find it helpful to ensure that SQL always has a minimum.
We did not find that to be the case. When the OS would trim from SQL it would ignore the minimum. We tried setting the minimum prior to applying SP3 CU4 so we could lock pages in memory on our standard edition servers.
Mike,
What O/S and version of SQL are we talking? How much memory? How many CPUs?
windows 2003 standard, SQL Server 2005. 32GB of memory. 8 CPUs.
This was a couple years ago and we have not really given much thought to the minimum memory setting since we started locking pages in memory at that time.
32-bit, yes? and what did you set the min and max values to? you mentioned standard edition, was that for both O/S and for SQL? and when you locked pages in memory, that is when you were able to guarantee that SQL would retain a certain amount? What about the /PAE or /3GB switches in the boot.ini file, were either of those enabled?
These were 64 bit machines and we were setting at or near 16GB. We had a mix of standard and enterprise for the OS, but the SQL verison in this case would have been standard. Enterprise already had the lock pages and so we never had a need to test the minimum setting there.
Mike,
Have you seen this yet? http://blogs.technet.com/b/askperf/archive/2008/03/25/lock-pages-in-memory-do-you-really-need-it.aspx
I wonder if your Win2k3 box was seeing something similar?
I had not seen that particular post. I don’t recall ever seeing an Event 333 in our environment and we had hundreds of SQL Servers all running locked pages in memory. Scenario 1 was generally the cause of the flushing in our case, but the problems went away once we locked the pages. Yes, sometimes we had to give the OS more memory than 4GB to keep things on an even keel. That’s how we tried to deal with the issue before being able to lock pages was to give the OS more memory up front and we had some success with that. I just never saw any success with the minimum memory setting.
The ‘min server memory’ configuration option is useful on multi-instance or multi-purpose servers where you want to ensure that SQL doesn’t reduce its buffer pool to a smaller value when another instance or process ramps up usage. It was never intended to deal with problems like Windows Server 2003 hard trimming the working set, the SQLOS isn’t in control of that, Windows forcibly performed the trim, and the concepts in the SQL Server Books Online are specific to how the SQLOS manages memory, not how Windows does. The working set trim issue with x64 SQL Servers on Windows 2003 was a Windows and not a SQL problem. This is discussed in KB918483 (http://support.microsoft.com/kb/918483) under the Improvements in Windows Server 2008 section. If you are on Windows Server 2008, you don’t need to use LPIM to prevent working set trims. In fact I’d be interested to hear and see evidence of someone on Windows Server 2008 that had a hard trim of the sqlservr process by Windows without LPIM in use. I haven’t used it on my Windows Server 2008 servers with up to 96GB RAM and have never had an issue. Things change that make old hacks unnecessary on newer platforms.
Actually Tom, too much oil in an engine and the agitator whips it into an airy froth. Too little and you get the same situation – inadequate lubrication. At a minimum you will wear out parts prematurely, at a maximum the pistons will seize in the cylinders.
I thought dragsters ran rich in so they could get some nice flames shooting out the exhaust, and some other kinds of racing tend to run lean but use some synthetics in order to help with lubrication. I’ll check with my mechanic on this one, was writing from memory which tends to fail me…
“Rich” & “Lean” terms reflect the ratio of fuel/air that is getting fed to the cylinders. “Rich” means more fuel, “lean” means less.
That ratio affects and is used to manage lots of things, including the temperature of the eninge and the amount of power that it is putting out. Depending on the engine, this may need to be adjusted depending on ambient air temp to keep the engine running happily & within temperature limits (due to the difference in density betwen hot & colder air and other factors). This is managed by the engine control computer in modern cars, and extra-modern little piston airplanes (airplanes have to deal with altitude changes affecting air density). In the vast majority of little airplanes, this management is in the hands of the pilot.
The flames on dragsters is probably due to combustion still being in-progress during the exhaust cycle, so the still-burning fuel/air mixture just gets vented out the exhaust manifold (don’t really know, assuming here). That could/would be caused by an overly-rich mixture, which at the power outputs they’re running, they need to to keep temperatures down.
(Tom, for the record, I was going to let you slip on this topic, but since someone else brought it up… 😀 )
Ha! No problem…so, ‘rich’ and ‘lean’ never infer to the amount of oil (although i swear i heard it put that way once), it is about fuel instead?
Yep, you are correct Airborne.
Inferred here is that liquid gasoline does not burn – the fumes do. The fuel-to-air ratio (rich or lean) is about 13:1 air-to-gas for a normal 4 cycle engine. But it sounds like you know more than this old shade-tree mechanic.
I imagine the top fuel dragsters use something like AVgas. That brings into play other factors like octane – the higher the harder to ignite but a bigger bang blowing them carbon chains apart – as well as flash point. Some fuels have such a low flash point you could throw a lit cigarette onto a puddle of them and they won’t ignite.
But I like the analogy … mix/max to lean/rich.
I once tried to explain to my wife that what makes a car go is a bunch of timed, controlled explosions. She said what makes the car go is the keys 😉
I’m curious why you recommend leaving 4GB for the OS. This seems excessive to me (assuming a dedicated database server), or perhaps I’ve been memory-starving my servers all this time (!!). Can you explain a little? Thanks.
Colleen,
In some cases 4GB won’t be enough for everything ‘extra’ running on a typical server. The OS itself should have 2GB reserved. After that, consider you have things like AntiVirus software running. and newer x64 servers have a memory requirement perthread on the order of 2MB. So, if you have a server with 16 logical CPU, that could be 480 threads which is 960MB, almost a full GB right there. Oh, and if you are also running SSIS, or SSAS, well those have memory needs, too.
Now, here’s the thing, you *may* be memory starved…but you don’t know if you are unless you are measuring for it! So you could configure your box as I have outlined and never see any problem, or you could see memory pressure down the road and find out that leaving 6GB is better for your server.
The outline I have given is for a new box, one that I have just installed SQL onto, and I don’t have any particular memory requirements handed to me. I need to start somewhere, and the guidelines here just serve as a starting point, nothing more.
Jonathan,
While I would generally use the maximum setting in those situations, I can see where the minimum setting could be useful. I was mainly curious if people have actually used them in that manner successfully. I didn’t mean to imply that the trimming issue was anything but a Windows issue. My expectation at the time of our testing the trimming issue was that the SQLOS would communicate to the OS that it should not take any memory below the minimum setting so I was surprised when it did.
Colleen -As an example Fulltext indexing prior to SQL Server 2008 runs as a separate process and can cause you to need a lot more than 4GB for the OS.
Thanks for the extra info, that was very helpful.
When I’m not on tour singing, I like to read Bob Duffy. He’s got great stuff on SQL Infrastructure stuff (a hobby of mine to divert my atention from being a Diva):
http://blogs.msdn.com/b/boduff/archive/2008/03/31/how-to-set-max-server-memory.aspx