In managing one of my servers I had to make sure everything was running the way it should. I am working on fairly big databases (in the order of 10s of gigabytes) and I wanted to make sure SQL is using all the memory it has available.
The server I am working on is an x64 windows 2003 enterprise running SQL 2005 enterprise 32 bit. The server is also equipped with 12GB of ram.
The server I am working on is an x64 windows 2003 enterprise running SQL 2005 enterprise 32 bit. The server is also equipped with 12GB of ram.
Whenever I would look into the task manager or the performance monitor the only memory allocation I see for SQL server is around 202 MB which makes no sense.
If you look at the screen capture enclosed you can see the memory allocation for SQL server.
I did some research on the subject and found the answer. SQL has a dynamic management view called sys.dm_os_memory_clerks. This view described in http://technet.microsoft.com/en-us/library/ms175019.aspx has detailed memory allocation information for SQL server. When you examine this view you can see a column that details AWE memory allocations that are detailed in the awe_allocated_kb column, in my case this was summed to the 10GB I assigned to the server. There are other useful rows columns in the view and they are documented in the technet article mentioned here.
One useful query i run constantly:
SELECT
SUM(awe_allocated_kb)/1024 AS [AWE_MEM_MB],
SUM(virtual_memory_reserved_kb)/1024 AS [VM_RES_MB],
SUM(virtual_memory_committed_kb)/1024 AS [VM_COM_MB],
SUM(shared_memory_reserved_kb)/1024 AS [SM_RES_MB],
SUM(single_pages_kb )/1024 [SNG_PAGE_MB],
SUM(multi_pages_kb )/1024 [MLT_PAGE_MB]
FROM sys.dm_os_memory_clerks
This returns the memory allocation totals in megabytes.
