1

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 > 0 OR multi_pages_kb > 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
Shanky
  • 19,148
  • 4
  • 37
  • 58
tesla747
  • 1,910
  • 10
  • 42
  • 77

3 Answers3

1

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.

Community
  • 1
KASQLDBA
  • 7,203
  • 6
  • 30
  • 53
1

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.

Buffer pool only caters to request which require pages <=8KB for any pages or memory request which require pages >8KB memory would not be allocated by buffer pool but would directly be done by windows API. This memory allocation can cause SQL Server memory consumption to go beyond limit set in max server memory configuration page. Following can take memory outside buffer pool

  1. COM objects
  2. Extended stored procedure
  3. Memory allocated by linked servers (loaded in process ) or other Dll’s loaded in SQL Server process
  4. Memory allocated by SQL Server memory manger if the allocation size in greater than 8K and need’s contiguous memory (Multiple_pages_kb).
  5. SQLCLR

I have seen posts saying max memory is for buffer pool only.

This is correct only for SQL Server versions before 2012 so this means it is correct for SQL Server 2008 R2. But you must clearly note that SQL Server can use memory beyond the limit set in max server memory.

Rest all is explained by KASQLDBA

Shanky
  • 19,148
  • 4
  • 37
  • 58
0

I believe as the SQL Server max memory has not been capped before to max memory it has grabbed all the memory available and not able to release it now, can you try clearing all the cache and see if it works, if not you need to Restart the SQL services to release the memory to OS according to me.

KiranDBA
  • 23
  • 3