About Me - Who is DR DBA?

Hello. My name is Rudy Panigas and I am a Production Senior Microsoft SQL Server Database Administrator (DBA) with over 14 years of experience. Have published articles with SQL Central, am a leader for the my local PASS chapter in Toronto (TORPASS) and love to automate anything SQL including disaster recovery. I created this blog to share my views, knowledge and to discuss Microsoft SQL server in order to help out others in this field. Please test these scripts before using. I do not warranty and do not take any responsibility for it, its misuse or output it creates. You can reach me at this email address: sqlsurgeon@outlook.com

Friday, December 19, 2014

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;

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.