SQL script to find the RAM used by database

Memory is one of the important areas to investigate/troubleshoot performance issues in SQL Server. We will out how much space is being taken by each database in the buffer cache (nothing but physical RAM).

Use below sql script to find the space details which is 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;