Performance, SQL Server

SQL Server: Determining which database is using the most memory right now

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:

Current Memory Usage
Current Memory Usage in SQL Server

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!

Advertisements

3 thoughts on “SQL Server: Determining which database is using the most memory right now

  1. The conversion from pages to megabytes can be simplified by dividing by 128 since there are 128 pages in a MB:
    COUNT(*)/128 AS SizeInMB

    Excellent article. I find it very useful. Thanks for taking the time to share it.

    Lee

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s