SQL Server - RAM Used By Database

Memory is one of the important area to investigate/troubleshoot performance issues in SQL Server.

Pages retrieved from RAM directly will increase the performance of the query rather retrieving it from DISK to RAM.

The first approach to find out how much space is being taken by each database in the buffer cache (nothing but physical RAM), the below shows the space details taken by databases in RAM:

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value

FROM   sys.dm_os_performance_counters

WHERE  Rtrim([object_name]) LIKE '%Buffer Manager'

       AND counter_name = 'Total Pages';

;

WITH src

     AS (SELECT database_id,

                db_buffer_pages = Count_big(*)

         FROM   sys.dm_os_buffer_descriptors

         GROUP  BY database_id)

SELECT [db_name] = CASE [database_id]

                     WHEN 32767 THEN 'Resource DB'

                     ELSE Db_name([database_id])

                   END,

       db_buffer_pages,

       db_buffer_MB = db_buffer_pages / 128,

       db_buffer_percent = CONVERT(DECIMAL(6, 3), db_buffer_pages * 100.0 / @total_buffer)

FROM   src

ORDER  BY db_buffer_MB DESC;

Query Result

Now, you’ll have idea that which database is consuming maximum memory, the below query will identify the tables which are taking space in the particular database:

USE [Target_Database];
GO
WITH src AS(
SELECT       [Object] = o.name,       [Type] = o.type_desc,       [Index] =                  COALESCE(i.name, ''),[Index_Type] = i.type_desc,       p.                        [object_id],       p.index_id,       au.allocation_unit_id   
FROM         sys.partitions AS p   
INNER JOIN    sys.allocation_units AS au       
             ON p.hobt_id = au.container_id
INNER JOIN   sys.objects AS o       ON p.[object_id] = o.[object_id]   
INNER JOIN    sys.indexes AS i       ON o.[object_id] = i.[object_id]       AND               p.index_id = i.index_id   
WHERE         au.[type] IN (1,2,3)       AND o.is_ms_shipped = 0)
SELECT       src.[Object],   src.[Type],   src.[Index],   src.Index_Type,   
             buffer_pages = COUNT_BIG(b.page_id),   
             buffer_mb = COUNT_BIG(b.page_id) / 128
FROM         src
INNER JOIN    sys.dm_os_buffer_descriptors AS b  
ON          src.allocation_unit_id=b.allocation_unit_id 
WHERE        b.database_id = DB_ID()
GROUP BY      src.[Object],   src.[Type],   src.[Index],   src.Index_Type
ORDER BY      buffer_pages DESC;

Query Result

With help of above queries, it’ll be clear regarding which tables pages are more in physical RAM. Indeed, that table is being retrieved more than any other table in the database.

You can now proceed furthermore for performance troubleshooting to check what type of queries are being executed on the given tables. Now you can ask the below questions to yourself, and define a plan for action:

1) Why all column data needed in the RAM?
2) Do we need to create index and extract only needed columns?