1

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?

3
  • Why is AWE turned on if you only have 4 GB?
    – Massimo
    Commented Feb 15, 2010 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? Commented Feb 15, 2010 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
    Commented Feb 15, 2010 at 20:46

3 Answers 3

3

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

2
  • 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. Commented Feb 15, 2010 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
    Commented Feb 15, 2010 at 17:16
0

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

0

sp_configure 'min server memory', 1024

RECONFIGURE

GO

sp_configure 'max server memory', 4000

RECONFIGURE

GO

Should make the memory usage go up.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .