RAM occupied by SQL Server Database

How much size of RAM is occupied by each database in SQL Server Database

Memory one of the important bottle neck in troubleshooting the performance of SQL Server.

It is very evident that the pages which are retrieved from RAM directly will increase the performance of any application rather than pages retrieving from DISK to RAM.

Our first preference is to find out how much space is being occupied by each database in the buffer cache (which is nothing but physical RAM).

Below is the query to find the space details occupied by database 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;

After identifying the database which is consuming more space in RAM, now it's turn to identify the table which is taking more space in that particular database.

USE [DB_NAME];
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;

Now it will be clear regarding which table pages are more in physical RAM.
Obviously this table is being retrieved more than any other table in the database.

Now it's up to you how you proceed for further troubleshooting. You need to check what type of queries being fired from the application or users. 

Question yourself

1. Is this table retrieving more than it is needed
2. Why do we need all the column data in the RAM
3. Can we create the index and extract only those columns data which is needed.

Now proceed the way where your question takes you to solve the issues.

Please post your issues or concerns so that together we can work and solve it.