Sql Server 2008 and the Case of the Missing Memory

I had the strangest thing happen to me recently. I recently order and configured a new server for a client with SQL 2008. This was a beefy box. 8 gb of ram, dual quad core cpus, the works.

First impressions of SQL 2008 were good. Performance was good, and the SQL Server Studio had some nice usability enhancements that I appreciated. Anyway, this server supported 2 ArcSDE instances for a very high volume site. I set up SQL server to pretty much take as much memory as it wanted. I really wanted to cache as much as I could.

So, one day, I pop in and open up Process Explorer and see how we are looking. To my shocking disbelief, the server was reporting that it was almost maxed out on physical memory - right around 7.4 gbs. Wow I thought, thats a lot of RAM. Then I looked a bit closer, and the SQL Server process itself was only report 200 mb of RAM usage! Wha wha what? Where in the heck is all my memory going if SQL Server isnt using it? Again, using Process Explorer, I exported all my active processes and dumped into excel, where I added up all my process' RAM usage - and it only came to about 1.6 gbs. Where in the heck did remaining memory go?

Well, turns out, SQL Server 2008, unlike previous versions, does not report its total memory usage to task manager or to Process Explorer.

The amount of memory that will show up in Task Manager (or Process Explorer) for the SQL Server process is just what is used by the process itself. It does not represent the memory used by the SQL Server buffer pool. The SQL execution engine (SQLOS in SQL 2005+) manages its own threads and therefore takes care of its own memory allocations. The SQL command

DBCC MEMORYSTATUS

will provide a breakdown of the SQL memory clerks. In particular, take a look at the MEMORYCLERK_SQLBUFFERPOOL output, as this will tell you how much memory is being used by the buffer pool, which is likely where most of the system's memory is allocated.

For more information, see these links to the Microsoft Developer Network (MSDN) site about SQL Server Memory:

Memory Management Architecture - http://msdn.microsoft.com/en-us/library/cc280359.aspx Monitoring Memory Usage (This talks specifically about monitoring memory usage in SQL 2008. Provides some SQL-specific perfmon counters that show the actual memory usage) - http://msdn.microsoft.com/en-us/library/ms176018.aspx

In the end, I was able to use Perfmon to find all my missing memory. I used SQLServer:Memory Manager - Total Server Memory in PerfMon. Not sure I think that this is the smartest thing to do MS. I have a feeling this will freak out a good amount of folks...At least I know now though...Hope this helps someone and I can save them the 3 days of frantic Google Searches...