I have a virtual machine (Windows 2008 Server R2 64 bit running on vmware esxi 4) with 6 GB of installed RAM. It's reporting 95% memory usage and close to 0% CPU. This server hosts some WCF services on IIS and has AppFabric installed which installs its own SQL Server 2008 R2 Express. All in all, pretty low usage though.

When looking at the memory tab of the resource monitor, there is no obvious process consuming very much memory. The sum of the commit column for all processes listed is around 1.5 GB. So, while the machine is reporting 95% usage (over 5 GB), I can't figure out what's using it.

Looking at perform, I added several counters in the Process object and the biggest anomaly I found was that sqlserver had a virtual bytes count greater than 6 GB, yet it's private bytes count is 125 MB.

Does that high virtual byte count prove sql is the source of the high memory use? What would cause that spike? Or should I be looking at something else to isolate the memory hog?

  • "Why is my server wasting so little memory?" (There are only two things you can do with RAM, use it or waste it. You can't save it for later. So "lots of use" means "little waste".) – David Schwartz Sep 17 '14 at 18:13

If you didn't set a maximum memory value for the SQL Server instance, it will consume as much system RAM as possible for caching. That's likely the source of your problem.

  • This is correct, the default value is stupid high and so in most real world scenarios it means all RAM will be consumed by SQL Server. However I don't think that applies to Express version of SQL. I'm pretty sure there's a 1GB limit on SQL Server Express. – chunkyb2002 Jul 19 '12 at 18:46
  • I thought express had a limit too, but in looking at the memory settings, I see the 2147483647 MB limit. – RyanW Jul 19 '12 at 19:04

If you do not set a limit on how much memory SQL can have it will take all it can get its hands on. It shows memory usage being so high because SQL has claimed all of that memory that does not mean that it is actively using that memory for queries. It is instead using that memory to store Pages, execution plans, and many other goodies. To see what SQL is actively doing with the memory you need to run this. select * from sys.dm_exec_query_memory_grants All you need to do is lower the amount of ram SQL can use keep in mind that it will go slightly over the limit that you set for the RAM windows requires to run SQL.

Hope that helps.

Your Answer

