July 30, 2010 at 4:19 pm
I have 128 GB of memory (shows up as 127 on windows properties).
I also have SS2005 running SP2. The DB is around 35gb, growing maybe 2-3gb a year.
I have the Db and log on two separate drives other than the OS (so three logical drives, 6 psychical due to raid).
The OS is Windows Server 2003 Enterprise SP2 64bit.
Our virtual memory is set to 2-4gb by the Sys Admin
Now the issue I'm seeing is that under Task Manager, SQL Server has 1.3gb of memory usage.
Looking on Performance Tab of task manager I'm seeing a hard unchanging line of 52.1GB PF usage.
The Physical Memory (K) reads (with some rounding, the numbers keep changing slightly):
Total: 134,000,000
Available: 77,500,000
System Cache: 77,500,000
Though I rounded Available and System Cache to the same value, they're not equal, but very close.
So here I am. I have a X7350 64bit 16 core machine with 128gb of ram and it looks like SQL Server isn't really using it.
I guess it's SQL Server using the 52.1 GB Paging File because Windows is reporting that it's only got a 2gb paging file.
I understand that just because I throw a ton of memory at SQL Server, it's not going to just use it all, but it keeps releasing memory? Why? There s enough memory to keep the whole database and likely every query run for a week in memory if it were designed to do so.
I've always heard SQL is memory hungry and will over indulge, not nibble like it's on a diet and push the memory away.
A good portion of the database is a set of Materialized Views with indexes on them and truth be told the database is probably 60% indexes.
I'm just worried because the only real issues we get on this monster box is disk queue when doing something really intensive. I like to believe that much of this information can be held in memory rather than being read from the disk every time.
Could someone please provide any insight or advice here? Thanks!
July 30, 2010 at 4:28 pm
Task manager does not display memory usage correctly for SQL Server. Look at the perfmon counter SQLServer:Memory Manager Total Server Memory(KB) for the actual amount of memory SQL is using.
July 30, 2010 at 4:40 pm
I figured since it managed it's own memory. Thank you.
Total Server Memory (KB): 50177792. Unchanging.
This is the value I was seeing under Paging File under Task Manager.
Well it's 52.2GB now (was 52.1GB when I first posted). Apparently it changes, just slowly.
PerfMon says this is dynamic memory despite Task Manager declaring that it's a Page File.
Is it normal for SS to use roughly half the memory available? That makes me feel a bit better than having to justify why SQL Server was given 128 gb and used 2 🙂
July 30, 2010 at 4:51 pm
SQL will use only the memory it needs up to the max server memory you set. The entire db can fit into memory if it's only 35GB. Is the server a dedicated SQL server or are there other applications running on it? You should also set max server memory for SQL somewhere in the 112GB +\- range depending on what else is running on the machine. If max memory is not set, and SQL needs additional memory it can sometimes consume all the memory on the machine and starve out the OS.
July 30, 2010 at 4:58 pm
Pretty much a dedicated server.
Only SSMS and RDM have been used aside from admin tools like perfmon and those only get used on a rare occasion.
I'll take a look at the memory settings.
Thank you muchly for your help!
July 30, 2010 at 5:39 pm
Actually is there a way to verify that the computer is using all 128GB?
If I set Max Server Memory to 121GB and it can only access 64GB, it'll still choke out the OS.
July 30, 2010 at 7:59 pm
Hi STherrien
Available Memory = 128 GB
Used Memory depends on the load on the server.
For SQL Server to use Max memory on this dedicated box, use
Min Mem = Max Mem = 120 GB and use Lock Pages in Memory so that SQL Server pages will not be paged out to the Hard Disk. AWE is not required on 64-bit systems.
Thank You,
Best Regards,
SQLBuddy
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply