Take the 2-minute tour ×

A friend of mine asked me today (trying to calm down an agitated customer of his) how you could find out in SQL Server 2005 which database uses how much memory (in the server's RAM that is) at any given time.

Is that possible at all? If so - how? Can you do this with built-in SQL Server tools, or do you need extra third-party options?

His customer was all flustered because his dedicated SQL Server machine suddenly uses all but 200KB of its 4 GB of RAM. I don't think this is a problem, really - but since this guy claims it happened more or less over night, he wants to know what caused this increase in memory usage.....


share|improve this question

2 Answers 2

up vote 24 down vote accepted

It was most likely caused by a query wanting to read more pages into the buffer pool, and the buffer pool grabbing more memory to accomodate that. This is how SQL Server is supposed to work. If the box experiences memory pressure, it will ask SQL Server to give up some memory, which it will do. The customer shouldn't be concerned.

You can use the DMV sys.dm_os_buffer_descriptors to see how much of the buffer pool memory is being used by which database. This snippet will tell you how many clean and dirty (modified since last checkpoint or read from disk) pages from each database are in the buffer pool. You can modify further.

   COUNT (*) AS 'Page Count'
FROM sys.dm_os_buffer_descriptors
   GROUP BY [database_id], [is_modified]
   ORDER BY [database_id], [is_modified];

I explain this a little more in this blog post Inside the Storage Engine: What's in the buffer pool?

You could also checkout KB 907877 (How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005) which will give you an idea of the breakdown of the rest of SQL Server's memory usage (but not per-database).

Hope this helps!

share|improve this answer
You're a genius, Paul ! –  marc_s Jun 11 '09 at 12:07

Your friend can also limit the amount of RAM that SQL will take because, as Paul states above, SQL will take every bit of memory it can.

Limit the amount of memory taken by SQL Server to 2000 Mb (or whatever you think is best).

--Enable advanced options:
USE master
EXEC sp_configure 'show advanced options', 1

--Set the maximum amount of memory to 2000 MB:
USE master
EXEC sp_configure 'max server memory (MB)', 2000

--Display the newly set configuration:
USE master
EXEC sp_configure 'max server memory (MB)'

--Set 'show advanced options' back to default:
USE master
EXEC sp_configure 'show advanced options', 0 
share|improve this answer
Thanks - I was aware how to limit the memory, but I didn't know how to find out which db is using how much buffer pool memory at any given time. –  marc_s Jun 11 '09 at 12:08

Your Answer


