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
  • please have a look at the answer from aaron here: dba.stackexchange.com/questions/84329/… , the first query he provides will list who uses how much memory. run the query and update your question with the result. – RayofCommand Jul 27 '15 at 11:38
  • please read again what I wrote, check the answer from aaron not the query in the question, thanks – RayofCommand Jul 27 '15 at 12:16
  • the query which he mentioned works in 2012 only. In 2008 R2 it doesn't work. – tesla747 Jul 27 '15 at 12:22
  • sorry. So basically there is the buffer pool and the plan cache, which caches your query plans. for me with standart 2012 edition the max. memory is 64 GB for sql server (buffer pool) and overall it uses nearly 70. So if you want restrict your memory to max 28 GB (buffer+plan+ some smaller clerks) you should try to set max memory maybe to 25 GB. Or you find a way to query your plan cache memory size and substract that amout from 28GB to get your correct max setting – RayofCommand Jul 27 '15 at 12:25
  • now tell me one thing, my ram is 32 GB and memory utilized is 32 gb as from task mgr which is been whollu utilized by sql service. what should I do to bring that utilization down to 85-90%. no other services occupies space other than sql server. – tesla747 Jul 27 '15 at 12:30
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.

  • the sql server process wholly uses 32 GB out of the physical ram of 32 GB. How do I limit that? Thats my question. That is why I kept the max memory to 28 GB. Is there any way to limit the sql server process to use 28Gb or lesser since buffer pool is only using 4 gigs of memory. – tesla747 Jul 27 '15 at 11:29
  • @tesla747: I have answered the same. Before you put a cap on SQL server memory, you need to know what's the good value for SQL to use the memory. How you came to know its 28 GB for SQL, get some calculation done as per the article i posted, and then lower down the value accordingly for SQL. Moreover you need to find what OS process is requiring and how much do it actually needs? – KASQLDBA Jul 27 '15 at 11:34
  • oops.. my buffer pool is using 28 gigs where my max memory is 28 GB as well. now it makes sense. But in task manager sql server usage as 28 gb and in perfmon it shows sql server usage as 28 gb. now I need to bring down the utilization which is showing in task manager.. – tesla747 Jul 27 '15 at 12:13
  • also I have checked the query. Memory_usedby_Sqlserver_MB Locked_pages_used_Sqlserver_MB Total_VAS_in_MB process_physical_memory_low process_virtual_memory_low 29636 0 8388607 0 0 – tesla747 Jul 27 '15 at 12:14
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

  • noted. all I'm is asking so bring down the OS utilization (now it is 99%) the only option I have is to try giving different max server limits and check for utilization. Is that the only way? – tesla747 Jul 27 '15 at 17:38
  • And what makes you think this is issue ? SQL Server is designed to grab as much memory as possible and wont give it back unless OS flags low memory notification. You can lower down max server memory, it would bring down SQL Server consumption but you might be starving SQL Server processes. You can also run dbcc dropcleanbuffers and dbcc freeproccache but I would never run this on my prod system as it would clear cached plans – Shanky Jul 27 '15 at 17:43
  • My issue is only that my windows admin guy is saying the utilization is way up 99% all the time. And that was the reason we tried setting the max server but its still the same. As you said, I cannot clear the buffers as well. – tesla747 Jul 27 '15 at 17:49
  • And believe me Windows guys have not idea how SQL server works so they will keep complaining. IMO this is normal behavior. Did you lowered max server memory it will definitely come down unless some other application is using it. – Shanky Jul 27 '15 at 17:51
  • unfortunately it doesn't come down even a bit. The buffer pool is occupying 29 gigs and non-buffer takes the remaining. In total, sqlservice occupies 32 gigs out of 32 gigs. – tesla747 Jul 27 '15 at 17:54
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.

  • I have restarted the sql services after setting the max memory. Should I clear the cache as well? – tesla747 Jul 27 '15 at 10:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.