Can someone explain my Windows/SQL Server memory usage

Question

I'm running Windows Server 2003 x64 with 8GB RAM and SQL Server 2005 64 bit. I have SQL set to use loads of memory, but the SQL process only ever takes under 100 MB RAM. Is this normal? It is accessing data with indexes many GBs in size.

Moreover, no process is taking over 100MB, yet there is only a minimal (<100MB) amount ofree memory.

Could some kind person explain to me where the memory is being used and if the SQL memory usage is normal?

Memory usage:

SQL Server memory settings:

Memory details:

Answer 1

The value displayed is not the actual amount of used memory. Mark Russinovich deals with memory managment issues on Windows in some posts in his blog. Have a look at Pushing the Limits of Windows: Physical Memory and Pushing the Limits of Windows: Virtual Memory.

Answer 2

I recommend Process Explorer (http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx) as a better way to gauge how much memory a process is using. Right click on the header and choose "Select Columns" and then the "Process Memory" tab and finally check the "Private Bytes" and "Private Bytes History" options.

As far as your SQL memory configuration, I recommend setting the minimum to whatever you know you will never need on the machine for other tasks, and the maximum to what pushes you to filling the physical memory (counting other processes), but no farther.

Answer 3

The memory usage is normal for those settings, and although you don't see it in the task manager, it's the SQL Server that is using the memory.

However, as you see the total memory usage is more than there is physical memory, which is bad for the server's performance. You should not set the memory limit for the database server to more than you have physical memory. The database server uses the memory to cache data and results, and if it has to swap the cache from disk there is no gain in having it.