I just had cause to figure out which databases are actually consuming the most RAM on a server with quite a large number of databases on it. The query is quite simple but also pretty helpful, so I thought I’d share it here:
-- Determine how much memory each database is currently using in the buffer pool SELECT DB_NAME (database_id), COUNT(*) AS NumberOfPages, -- We take each page regardless of whether it is an index or data page -- Because we know each page is 8K regardless of whether its an index or data page, multiplying the page count -- by 8 gives us the size in Kilobytes. Divide by 1024 to get the current buffer pool usage in MB (COUNT(*) * 8)/1024 AS SizeInMB -- NOTE: Will return 1 row for *every page* currently sitting in the buffer pool. -- On large systems this can return a large numbers of rows FROM sys.dm_os_buffer_descriptors WITH (NOLOCK) GROUP BY sys.dm_os_buffer_descriptors.database_id -- Order by the total number of pages in the buffer pool ORDER BY COUNT(*) DESC
You should get something like this:
It basically uses the sys.dm_os_buffer_descriptors DMV to take a count of all pages in the buffer pool right now. A bit of multiplying and dividing give us a rough and ready estimate of which databases are currently wining the battle for RAM.
I hope that helps someone!