Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

This will probably sound like a strange issue but I'm a bit concerned our main database server isn't utilising all of the memory availbable to it. It is a 32-bit Windows 2003 / SQL Server 2005 Standard deployment. It is the only instance running on that box and has the default memory limit settings still applied. The server has 4GB RAM and has AWE turned on. We've had reports of database slowness recently so I went on to the server to take a look. I was surprised to see that the sqlserver.exe process was only using (and has peaked at) 137MB RAM. We have multiple SharePoint farms, several internet facing websites and multiple internal applications all running from this one database server, I would expect it to be using much more RAM than that.

Without knowing a better way to test this I wrote a SQL query that created a temp table and inserted some large strings hundreds of times (I'm actually a developer first and an admin second). On my local box when I ran this query the memory usage went up to nearly 2GB. On all other servers there was a marked increase in memory usage. However on our production server the memory did not change at all - it stayed at approx 137MB (The processor usage went up so it was definitely doing something).

All in all I'm a bit confused by this and was hoping that someone might be able to shed some light on this?

share|improve this question
    
Why is AWE turned on if you only have 4 GB? –  Massimo Feb 15 '10 at 15:46
    
Hi, I'm guessing from your comment that we shouldn't be? I didn't configure this system but I presume this was to maximise memory availability. Are you about to point out an error in our ways? –  j.strugnell Feb 15 '10 at 16:14
    
With 4GB of RAM, I'd assign max 3GB to SQL so that the OS has at least 1GB. Of course I didn't see this minor issue when I originally answered :-) –  user3914 Feb 15 '10 at 20:46

3 Answers 3

up vote 3 down vote accepted

With AWE, you won't see the true RAM usage of SQL Server in the process list (because of the way AWE maps memory). You'll need to go into Performance Monitor and have a look from there (I forget which SQL counter to use, but it'll be under SQLServer:Memory Manager).

share|improve this answer
    
OK. I've used perfmon on my machine and can see that Total Server Memory counter maps quite closely to the memory usage reported in Task Manager. On the server this counter is running at around 3GB which would be about right I guess. Thanks for the tip. –  j.strugnell Feb 15 '10 at 16:04
1  
One handy query I use in SSMS for this is select sum(awe_allocated_kb) from sys.dm_os_memory_clerks. –  phoebus Feb 15 '10 at 17:16

You can try manually setting the memory limit in SQL Server.

share|improve this answer

sp_configure 'min server memory', 1024

RECONFIGURE

GO

sp_configure 'max server memory', 4000

RECONFIGURE

GO

Should make the memory usage go up.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.