Why is SQL Server consuming more server memory?
SQL Server will consume as much memory as you will allow it. By default, that number would encompass 100% of your numerical memory on your machine. That's why you're seeing what you're seeing. If you give SQL Server 24 GB of memory, then SQL Server will do its best to use 24 GB of memory. Then you have SQL Server and the OS battling for resources, and it'll always result in poor performance.
When you set the max server memory configuration limit, you are limiting how much SQL Server can allocate for the buffer pool (virtually where it stores data pages and the procedure cache). There are other memory clerks within SQL Server, so for your particular version (2008 R2 and below), max server memory just controls the buffer pool. But this is always going to be the biggest memory consumer.
TechNet Reference on the Effects of min and max server memory
The min server memory and max server memory configuration options establish upper and lower limits to the amount of memory used by the buffer pool of the Microsoft SQL Server Database Engine.
As far as the question how much memory should I leave for the OS, that is a commonly debated metric. It really all depends, mostly on what other processes are running on the server. Provided it's a truly dedicated server (and that's almost never the case). I like to see at least 4 GB memory for the OS (and even more on big-hardware boxes). But most importantly, monitor how much unused memory there is. Be liberal, and give SQL Server more memory if you're noticing a lot of available (can be monitored through perfmon) and unused memory (of course, always leaving a small buffer for those corner situations).
SQL Server is designed to use all the memory on the server by default. The reason for this is that SQL Server cache the data in the database in RAM so that it can access the data faster than it could if it needed to read the data from the disk every time a user needed it. If we needed to go to the disks every time that SQL Server needed the same data over and over the disk IO requirements would be insanely high.
Normally I recommend people leave about 4 Gigs of RAM for Windows, but that really depends on the amount of software running on the server, what components of SQL Server you are using (not all the components follow that setting), etc.
This works instantly without the need for SQL restart:
Server Memory Server Configuration Options
Use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in megabytes) managed by the SQL Server Memory Manager for an instance of SQL Server. By default, SQL Server can change its memory requirements dynamically based on available system resources.
Procedure for configuring a fixed amount of memory To set a fixed amount of memory:
- In Object Explorer, right-click a server and select Properties.
- Click the Memory node.
- Under Server Memory Options, enter the amount that you want for Minimum server memory and Maximum server memory.
Use the default settings to allow SQL Server to change its memory requirements dynamically based on available system resources. The default setting for min server memory is 0, and the default setting for max server memory is 2147483647 megabytes (MB).
I hope this helps!