SQL Server – Not Using Allocated Memory

performancesql serversql-server-2005

We have noticed that for one of our SQL server with below configuration ,memory is not getting availed as allocated.

SQL server 2005 SP4, 32 bit

Windows server 2003 R2 SP2, 32 bit with 16 GB of RAM and 12 Cores

Also, we have enabled AWE and PAE as well.

Min server memory = 1024

Max server memory = 13500

I know Task manager is not a best way, but RAMMAP is also not working, but we believe SQL is not using the allocated memory.

Please advise what can be done here or is my AWE not properly configured?

Best Answer

You have a odd system. Your database size is 350+ G and you have 32 bit system I would say this is a system which I would never like to have in my environment. Its very difficult to manage 350 G database on 32 bit SQL Server which has VAS limit(by default) of 2 G. You are bound to face memory pressure going ahead.

AWE in 32 bit system only allows SQL Server to use memory beyond its VAS capabilities and that too only data and index pages can take advantage of extra memory by AWE. Plan cache, procedure cache and other caches cannot utilize this memory.

I know Task manager is not a best way, but RAMMAP is also not working, but we believe SQL is not using the allocated memory

This is not clear, what do you mean by believe, can you show some facts how you reached to conclusion that SQL Server is not using memory. Please run

DBCC MEMORYSTATUS()

command and share output on some shared link(onedrive,dropbox...) and post link into your question so that we can see how much memory SQL server is using.

Edit:

But when i looked on task manager and process explorer for private bytes, memory never got exceeded to 300-500 MB.

This is the incorrect thing you are doing. Task manager is not a place to see SQL Server memory utilization we have dbcc memorystatus output and perfmon counters to see SQL Server 2005 memory utilization. If you note task manager show Process private bytes this memory is pageable but memory allocated via AWE API in 32 bit is Locked and non pageable and non shareable. So task manager is not showing you TOTAL memory utilized by SQL Server as it is not tracking memory allocated via AWE. So I guess you got the difference now. Please read AWE how it functions in 32 bit.

Now from dbcc memorystatus dump you posted 

Memory Manager   KB
VM Reserved     1632608
VM Committed    169000
AWE Allocated   13762560
Reserved Memory 1024
Reserved Memory In Use  0

Memory utilized by SQL Server 2005 would be sum of VM committed and AWE Allocated. This would not give you absolute value but is correct. There are some more allocations which are not included in this but that would be just few MB and would hardly matter

So memory utilized by SQL Server is (169000+13762560)KB which is approx 13G. Which matches with max server limit you have set. So don't worry SQL Server is using memory allocated to it.

Plus whatever may be result please upgrade to 64 bit version of both Windows server and SQL Server

Related Question