SQL Server Max memory limit I have my SQL Server 2008 R2 installed running on 32GB ram. I need to decrease the limit SQL Server uses to 28 GB and I have set the same to 28GB but it is still taking more. I have seen posts saying max memory is for buffer pool only. Ultimately what I require is the OS utilization should not go above 90% which is why I kept the max memory to 28GB hoping the OS utilization would come down.. but its not happening. What are the solutions?

I have run this query:

SELECT 
    type, virtual_memory_committed_kb, multi_pages_kb 
FROM 
    sys.dm_os_memory_clerks
WHERE 
    virtual_memory_committed_kb &gt; 0 OR multi_pages_kb &gt; 0


Output:

type                        virtual_memory_committed_kb  multi_pages_kb
MEMORYCLERK_SQLBUFFERPOOL   29169592                     416
OBJECTSTORE_LOCK_MANAGER    131072                       0
MEMORYCLERK_SQLSTORENG      53312                        17744
MEMORYCLERK_SQLCLR          10612                        14792
MEMORYCLERK_XE_BUFFER       4224                         0
MEMORYCLERK_SQLUTILITIES    120                          0


Query:

select * from sys.dm_os_process_memory; 

Output:

physical_memory_in_use_kb   30599208 ~ 29G

large_page_allocations_kb 0

locked_page_allocations_kb 0

total_virtual_address_space_kb 8589934464 

virtual_address_space_reserved_kb 39439648 

virtual_address_space_committed_kb 31066216 ~ 30G

virtual_address_space_available_kb 8550494816 

page_fault_count 16996946 

memory_utilization_percentage 99 

available_commit_limit_kb 32705384 

process_physical_memory_low 0

process_virtual_memory_low 0
SQL Server's memory manager is designed to keep as much data cached in memory as it can in order to speed up access for incoming queries.


In order to work successfully with other processes on the machine,
SQL Server will release memory when a low- memory notification is
sent by Windows.
The memory may be committed by the SQL Server process, but it will be
released if another application requests enough to trip the
low-memory notification.


Before you proceed with setting up the MAX and MIN server memory config setting would recommend you to go through this great write up by Jonathan How much memory does my SQL Server actually need?


  Ultimately what I require is the OS utilization should not go above
  90% which is why I kept the max memory to 28GB


If you are changing above just to settle down the threshold, sounds may not be the perfect reason you should be cutting down the memory what SQL needs.

However, the first thing you will need to analyse what process that are running at OS level and for how long that needs the amount of memory you are looking for, because SQL will use what has been assigned.

Baseline the server accordingly and see if you really face OS memory pressure:

Also see here  and here.

In addition there is this excellent article from Shanky : SQL Server Memory and Troubleshooting which should get you going.