This one is for the new DBAs…
There is a lot of confusion on memory settings in SQL Server. Specifically Min and Max settings found in the Properties of an instance:
There are dozens, if not hundreds of blog postings that will go into beautiful detail about all of the intricacies of SQL Server memory…after all, SQL lives there! Thus, it is very important to have a basic understanding early in your SQL DBA career….especially if you are an accidental DBA.
In the screenshot above, I have set the Min and Max on my laptop to 2GB and 14GB. Its a 16GB laptop, so I left 2GB for the O/S…pretty basic stuff.
Max Server Memory is fairly obvious…when SQL Server reaches this point, it stops allocating and starts paging to disk as necessary.
This is pretty much the same as setting a throttle control on a car. Or a restrictor plate in NASCAR. Without it you can run the car engine all the way to the max if you want, but eventually the other systems are going to fail (Cooling, transmission, brakes, etc.). Same thing if you don’t leave memory for the O/S.
Min Server Memory seems to get the most bad information spread around. SQL Server does NOT automatically grab memory up to the Min setting when it starts. However, once it gets there, it doesn’t give back.
Back to the car analogy…if you start up and head off down the road at 20 mph, you are above the default (0), but not at the max (100+, depending on the car and the tires…). If you set the cruise control, you can accelerate up and down above 20, but you won’t go below that unless you hit the brakes.
So that’s it…by default, SQL installs at idle and full speed ahead. Its your job to turn on the cruise control and not redline the engine until it blows.
There are holes in the analogy if you dig deep enough, but this should give you a real-world base to work from as you grow in your skills.