Jeremy Carter

Welcome back to part 2 of our series on memory. Hopefully you have all had a good week and you don’t have any more fires to put out. So now that we can get down to business we previously learned in part 1 why sql server is using all available memory and what we can do about it.  Today in part 2 we are going to explore the second memory related question that I am always being asked.

Question #2 – Why does task manager only show sql is using a few hundred MB?

In the same breath of the question they then state that task manager also shows something is using most of the ram, and they think it is sql server but are not sure.  While you can probably be safe in saying that it is indeed sql using this “missing” memory there are ways we can verify it. The easiest way is to run the following query inside of sql server.

select sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb] 
from  sys.dm_os_memory_clerks

If you were paying careful attention to the query above you will notice it returns how much memory is allocated using something called AWE. What is AWE you ask? It stands for Address Windowing Extensions. This is part of what allows us to use more memory than is typically allowed in a 32bit environment. The key to the disparity in task manager is that any memory that is used with AWE is not assigned as part of the Process Private Bytes. In task manager we are seeing all of the sql server process memory except the bufferpool which is the only cache that can take advantage of AWE.

OK so that explains the disparity in a 32bit environment but what if you see this in a 64bit environment? Again the answer has to do with AWE and this is where people start to really get confused. Most people think that AWE is only for 32bit. On the surface you are correct as the setting is not needed for 64bit and in fact the setting is ignored by SQL Server. However the SQL Server team at Microsoft found a nice side benefit of using the AWE api. It turns out that any memory that is allocated using the AWE api is locked and cannot be paged out which gives some performance benefits. Thus the term and feature “Lock Pages in Memory” was born.