Find out which database in SQL Server 2005 uses how much RAM

Question

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.....

Marc

Solution 1

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.

SELECT
   (CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) AS 'Page State',
   (CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
   COUNT (*) AS 'Page Count'
FROM sys.dm_os_buffer_descriptors
   GROUP BY [database_id], [is_modified]
   ORDER BY [database_id], [is_modified];
GO

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!

Solution 2

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
RECONFIGURE WITH OVERRIDE

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

--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 
RECONFIGURE WITH OVERRIDE

Comments

[marc_s]

You're a genius, Paul !

[marc_s]

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.