3

We are using SQL Server 2014 on Windows server 2008 Web edition (64 bit). Total RAM of OS is 8 GB .When we check high physical memory usage in the task manager it shows nearly 90 to 95 % full but I have checked all system processes which are taking nearly 2-3GB RAM memory.

I used the RAMmap tool to check the running processes' memory and it is showing AWE process which is using this extra memory. AWE is not releasing this memory but when we increase the RAM then it is capturing all the free RAM memory slowly.

Does anyone know how to reduce this AWE memory usage?

  • Task Manager is the worst way to determine SQL Server's RAM usage. Use the performance counter "Total Server Memory (Kb)" instead. – spaghettidba Jul 1 '15 at 12:26
  • SQL Server will normally grab a lot of memory. You should generally cap it around 80 - 90% of the systems memory. The system should have at least 4GB of memory comfortably. Or, if memory starved, perhaps 2 GB. – Chris Aldrich Jul 1 '15 at 12:36
  • @spaghettidba that's interesting. Do you know what Task Manager is doing differently? – Jmaurier Jul 1 '15 at 12:37
  • I like to follow the formula I read from Jonathan Kehayias. 1 GB for OS, plus 1 GB for each 4GB for the first 16 GB, and then 1GB for each 8 GB after the first 16 GB). This is what should be reserved for the system. The rest can go to SQL Server if it is a dedicated system. – Chris Aldrich Jul 1 '15 at 12:39
  • @Jmaurier brentozar.com/archive/2011/09/… – spaghettidba Jul 1 '15 at 12:49
2

AWE is not a "process", it is an API used for memory allocation. Quick googling shows that SQL Server may be using that API to allocate its bufferpool pages. It must be a quirk of RAMmap to show this under the AWE category.

I think it is pretty normal for a database server to show high memory utilization ratios, otherwise unused memory would be simply wasted.

| improve this answer | |
2

I would focus on core points

When we check high physical memory usage in the task manager it shows nearly 90 to 95 % full but I have checked all system processes which are taking nearly 2-3GB RAM memory.

You are doing mistake, which probably many newbies do when referring to memory utilized by SQL Server. SQL Server would never show you correct memory utilization in task manager if Locked pages in memory privilege(LPIM) is there for account running SQL Server service because task manager only shows Process Private Bytes, the memory allocated via Virtual_alloc function which is pageable. Now if SQL server service account has LPIM most part of memory allocation would be done by AWE API and this allocated memory is NON pageable and thus does not comes under task manager,in the end showing you different values. Hope this is clear

Other point to note is task manager shows memory utilized by all OS processes so why are you even referring to it and making wrong conclusion about SQL Server. Remember Task manager is Windows tool not a SQL Server tool.

I used the RAMmap tool to check the running processes' memory and it is showing AWE process which is using this extra memory. AWE is not releasing this memory but when we increase the RAM then it is capturing all the free RAM memory slowly.

RAMMAP is not the tool of your interest again. If you want to check memory utilized by SQL Server 2008 and above you can use below query.

select (physical_memory_in_use_kb/1024) as Phy_Mem_in_MB,
(locked_page_allocations_kb/1024) as Mem_allocated_AWE_MB,
 (virtual_address_space_committed_kb/1024) as Total_memory_utilized
  from sys.dm_os_process_memory

Can you please add the output of above query in question and lets see how much memory AWE API is allocating.

Does anyone know how to reduce this AWE memory usage?

As already noted AWE is an Windows API not SQL Server functionality, which does memory allocation for SQL Server process if account running SQL Server has LPIM. You can use

dbcc dropcleanbuffers
dbcc freeproccache

to clear memory allocated via AWE API but I would not advise you to run that specially on production server. Its pretty much normal for SQL Server to use memory, and good thing is when there is memory crunch SQL Server would even trim down its memory consumption upto Min server memory value and release memory.

| improve this answer | |
-1

You should be sure to set a hard limit on the amount of memory that SQL Server is allowed to use. As others have mentioned, SQL Server will gradually use more and more memory when it is available. It's much easier to service read requests when SQL Server can read from high speed RAM instead of reading from slow spinning disks.

If you open SSMS you can connect to your SQL Server instance, right click on the instance name, select properties, in the left pane select memory, and then set a 'maximum server memory' that is slightly less than the maximum available on the server.

A somewhat inaccurate way to determine whether or not there is memory pressure on SQL Server is to check how long SQL Server is keeping 8kb data pages in RAM. The rule of thumb is that pages should stay in RAM for longer than 300 seconds, however that entirely depends on workload and I've seen data warehouses perform great with less than 300 seconds for page life expectancy. Run the query below on your SQL Instance to determine how many seconds data pages are staying in RAM.

SELECT [object_name],
[counter_name],
[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'
| improve this answer | |

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.