2

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?

  • You are using a very very old hardware and unsupported SQL Server SP --- windows server 2003 ? 32 bit ? SQL 2005 SP2 ? ... – Kin Shah Mar 24 '15 at 13:44
  • Try a look in perfmon.exe, SQL Server has some performance Counter regarding Memory. – Sunzi Mar 24 '15 at 13:52
  • Referring MSDN, technet.microsoft.com/en-us/library/ms175581(v=sql.105).aspx Support for AWE is available only in the SQL Server Enterprise, Standard, and Developer editions and only applies to 32-bit versions of SQL Server. Standard 32-bit addresses can map a maximum of 4 GB of memory. The standard address spaces of 32-bit processes are therefore limited to 4 GB. By default, on 32-bit Microsoft Windows operating systems, 2 GB are reserved for the operating system, and 2 GB are made available to the application. How much ram are you seeing used by SQL? – Alocyte Mar 24 '15 at 13:59
  • First, what edition of SQL Server is this? (Enterprise, Standard, Workgroup, etc.). Secondly, tell us how you arrived at the conclusion that SQL Server is not allocating the memory, what tools did you use and what did you observe? Finally, how is your SQL Server being used, and what was going on in your SQL Server while you were making these measurements? – RBarryYoung Mar 24 '15 at 14:25
  • 1
    The most important thing is what is edition of Windows server. If it is standard edition windows server can't see more than 4 G. Plus why SQL server at SP2 ? I guess you know SP4 is only supported one. – Shanky Mar 24 '15 at 14:49
3

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

  • Thanks for the inputs. the performance counters for Total and Target server memory was always fixed at 137662650.( Analysed for the peak 8 hours) and memory grants pending always 0. But when i looked on task manager and process explorer for private bytes, memory never got exceeded to 300-500 MB. Hence assuming SQL not using amount of memory allocated during that peak 8 hours . Upgrade as of now is not possible being month end and no downtime in hands – KASQLDBA Mar 24 '15 at 15:57
  • Also please refer to the link for memory stats drive.google.com/file/d/0ByOFiaTVF8BsMm5kM0pVTGFvWVE/…. – KASQLDBA Mar 24 '15 at 15:58
  • Please see updated answer – Shanky Mar 24 '15 at 17:00

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.