On a 32-bit server the Perfmon TargetServerMemory results and Task Manager will never report more than 4GB memory in use.
In order to see if you are using more than 4GB memory you need to:
a) Look in the SQL Error Log for when SQL is started - you shold see a message that AWE memory has been allocated.
b) Look in the Perfmon SQL AWE counters for details of memory use above the 4GB line.
32-bit Windows is not able to directly memory above the 4GB line. The PAE features in Windows allow it to divide the memory above 4GB into 4K pages and give each page a slot number. Programs that are PAE-aware (such as SQL Server database services) can allocate these slots. When a PAE-aware program wants to read data in one of the slots, Windows copies it to a location below the 4GB line and the program can access it. If the page gets updated then Windows has to copy it back to its location above the 4GB line. If it has only been read then it can be discarded if a program requests to read a different slot.
Even though this is a slow way of using memory, the ability to cache daat read from disk into memory above the 4GB line allows programs to get better performance than always reading everything from disk.
In order to get fast and direct read and write access to memory above the 4GB line you need to run 64-bit Windows and 64-bit SQL Server.
Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 1 Dec 2016
: now over 39,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Quote: "When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist." - Archbishop Hélder Câmara