March 20, 2008 at 3:44 pm
I'm running SQL2k5 9.0.3054 / Win2k3 5.02.3790 SP2
The machine has 8 processors, 8GB of RAM (and appears to be running 2 NUMA nodes)
boot.ini file on server running /PAE and /3GB
AWE is enabled on the server (config AND run) - min server memory 1024 MB and max server memory 6144 MB.
I've had some memory pressure problems which made me start looking at the system. The problem SEEMS to be that SQL is not getting all of the memory from the OS (I think).
Task manager lists sqlserver.exe as using generally no more than 200 MB
Performance totals on task manager lists that there is only 900 MB of memory FREE on the server; however, totalling up the processes in the list results in less than 1GB
SQL Server Agent reports (on startup) seeing only 4GB of RAM
running the following query:
SELECT memory_node_id, sum(awe_allocated_kb)/1024
FROM sys.dm_os_memory_clerks
group by memory_node_id
results in 0 = 4994 MB, 1 = 1149 MB allocated by AWE
I don't see any port-to-node configuration mappings in the configuration manager.
So, in summary, I have two big questions:
1. Why isn't task manager reporting that sqlserver.exe is holding that 1 to 6GB that it says it's holding (I am running another machine, Win2k3/SQL 2005, without AWE enabled, the machine has only 3.5 GB of RAM, and the two instances of SQL on that machine hold approx 1.4 GB each in the task manager)
2. Why is the SQL Agent reporting only 4GB of RAM - is there some NUMA configuration I need to look into?
-- http://dbachman.blogspot.com
March 21, 2008 at 7:30 am
Plain and simple, task manager will never accurately report memory usage. There is no 'real' way so to say to see 'actual' memory usage at any given time ... this has been a thorn in my side for to long. I've worked with Microsoft many a times on the topic, and they still feel that perfmon accurately reports memory usage (target/total server memory) which is completely untrue.
When AWE is enabled, task manager means nothing. When AWE is not enabled, task manager will accurately report memory usage until the max server memory is hit. That memory isn't always 'in use' but it is more or less allocated and available to SQL.
I'm always wanting to learn more on this topic, but I've yet to see any clear and definitive way to truly query the memory being used at any point in time by SQL Server.
DBCC MEMORYSTATUS is about your best bet.
March 21, 2008 at 7:32 am
Also, if you're experiencing memory pressure, you should probably allow your SQL Server service account to lock pages in memory.
March 22, 2008 at 2:28 am
Thank you - that's helpful (and it explains the difference between the two machine's memory reporting in the task manager). I had checked on the "lock pages in memory" option, it's enabled in the local security policy, and I'm waiting on our Network guy to verify there are no domain-level overrides on that service account.
-- http://dbachman.blogspot.com
March 24, 2008 at 7:53 am
You have to restart the SQL Server Service for lock pages in memory to be enabled.
March 24, 2008 at 9:12 am
Right, the service account is actually holding that permission on the server right now, but I have this odd feeling that it might be overridden at the domain level, as that service account has been put in sort of an odd OU, so I'm having the network guy check on that for me (he's out today, unfortunately, and while i can get in and look at the directory, i don't really have the know-how or mandate to verify/change anything)
I would need to restart because the service only tries to lock those pages when it starts up, is that correct?
-- http://dbachman.blogspot.com
March 24, 2008 at 9:15 am
Correct.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply