We all know that SQL Server loves memory, and in my opinion SQL Server can never have to much RAM. RAM is faster and cheaper than IO capacity and RAM can reduce the pressure on the IO sub system.

Let’s see if we can write some diagnostic queries that can tell us more about the memory that is used by SQL Server. First off all I would like to know which database is taking up the most of the consumed memory. For that purpose I use a DMW called sys.dm_os_buffer_descriptors, this DMV returns information about all the data pages that are currently in the SQL Server buffer pool.

Here goes the SQL statement:

SELECT
    DB_NAME(database_id) AS [Database Name],
    COUNT(*) * 8/1024.0 AS [Buffer Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE (database_id > 4) AND (database_id <> 32767)
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC

And if we take a look at the output, we can see what database is taking up the most memory.

AdventureWorks is the most memory consuming database on my newly rebooted system. Try running this query on your systems, and I am sure you will be surprised by the result – for sure I have been for a couple of times.

Let’s dig even deeper!. Now let’s see if we can find out what tables have the most data loaded into the buffer pool. I am interested in the 10 Mb of data that AdventureWorks is taking up in my buffer pool. Here is the query to dig even deeper:

SELECT
    OBJECT_NAME(t3.object_id) AS 'ObjectName',
    t3.object_id,
    COUNT(*) * 8/1024.0 AS '[Buffer Size (MB)]',
    COUNT(*) AS 'buffer_count'
FROM sys.allocation_units t1
INNER JOIN sys.dm_os_buffer_descriptors t2 ON (t1.allocation_unit_id = t2.allocation_unit_id)
INNER JOIN sys.partitions t3 ON (t1.container_id = t3.hobt_id)
INNER JOIN sys.objects t4 ON (t3.object_id = t4.object_id)
WHERE (t2.database_id = db_id('AdventureWorks')) AND (t4.type = 'U')
GROUP BY t3.object_id
ORDER BY 3 DESC;

As you can see in the query text I’m only looking at user objects (t4.type = ‘U’), that is why the numbers don’t match. The 9,50 MB from the previous query will not show all together here, some of the system object will take up the rest of the space. Here is the result from my system:

That’s all for this weeks blog post, see you next week – and the best of luck with finding your lost memory

@geniiiuscom