Memory Usage by All Databases

Hello,

Do you know how much memory your databases are using and how much free memory you SQL Server has? Try this script below. 

The output will show the following:

• List all the databases with how much memory they are using
• Total usage for all databases
• Max memory set in SQL Server
• Memory available by subtracting the 2 values from above.

Enjoy!

DECLARE 
@TotalUsedByDatabases NUMERIC
,@TotalMAXMemorySQLSrv NUMERIC

SELECT
 isnull(db_name(database_id), 'resourcedb') [dbname]
, CAST(COUNT(row_count)/128.0 as decimal(10,2)) [size]
INTO #RAM
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id

SELECT dbname [Name of Databases], size [Memory Usage in MB] FROM #RAM

SET @TotalUsedByDatabases = (SELECT SUM(size) FROM #RAM);
SET @TotalMAXMemorySQLSrv = (SELECT CAST(value_in_use as NUMERIC) 
FROM sys.configurations 
WHERE name LIKE 'max server memory%');

SELECT @TotalUsedByDatabases [Total Memory used by Databases in MB]
, @TotalMAXMemorySQLSrv  [Max Memory Allocated to SQL Server in MB]
, @TotalMAXMemorySQLSrv - @TotalUsedByDatabases [  ** Memory Available ** in MB]


DROP TABLE #RAM;