Showing posts with label SQL server 2005 memory consumption allocation AWE address tranlation. Show all posts
Showing posts with label SQL server 2005 memory consumption allocation AWE address tranlation. Show all posts

Wednesday, November 21, 2007

Where did my SQL Server Memory go?


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.
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.