Monitor Database Memory usage SQL Server

In our PROD environment, there are two servers on which we have system critical database. The management requirement is to monitor these databases for high memory usage or access or long running queries, etc. My requirement is to create a SQL Job for this purpose and if any spikes are observed for this database an event gets generated. So that we can take corrective actions, before the sites hosted on these Dbs slows down. Please don't recommend using any third party tools for this purpose.

Also, I have come up with one idea, using the following query:

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
       WHERE database_id BETWEEN 5 AND 32766
       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;

The output of the above code is BUFFER USAGE (MB) and % BUFFER for each database, except system Dbs. Planning on creating a job which will keep on monitoring the output and check if the Buffer usage goes beyond a threshold.

Kindly suggest something or give comments about my idea.
Thanks in advance

You can use the below query to determine the Memory usage at database level:

SELECT

(CASE WHEN ([database_id] = 32767)
    THEN N'Resource Database'
    ELSE DB_NAME ([database_id]) END) AS [DatabaseName],
COUNT (*) * 8 / 1024 AS [MBUsed],
SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
 FROM sys.dm_os_buffer_descriptors
   GROUP BY [database_id];
   GO

If you want to analyse further at object level that which object in that database from above query is using a lot memory use below query:

EXEC sp_MSforeachdb
N'IF EXISTS (SELECT 1 FROM (SELECT DISTINCT DB_NAME ([database_id]) AS [name]
FROM sys.dm_os_buffer_descriptors) AS names WHERE [name] = ''?'')
BEGIN
 USE [?]
SELECT
''?'' AS [Database],
OBJECT_NAME (p.[object_id]) AS [Object],
p.[index_id],
i.[name] AS [Index],
i.[type_desc] AS [Type],
--au.[type_desc] AS [AUType],
--DPCount AS [DirtyPageCount],
--CPCount AS [CleanPageCount],
--DPCount * 8 / 1024 AS [DirtyPageMB],
--CPCount * 8 / 1024 AS [CleanPageMB],
(DPCount + CPCount) * 8 / 1024 AS [TotalMB],
--DPFreeSpace / 1024 / 1024 AS [DirtyPageFreeSpace],
--CPFreeSpace / 1024 / 1024 AS [CleanPageFreeSpace],
([DPFreeSpace] + [CPFreeSpace]) / 1024 / 1024 AS [FreeSpaceMB],
CAST (ROUND (100.0 * (([DPFreeSpace] + [CPFreeSpace]) / 1024) / (([DPCount] + [CPCount]) * 8), 1) AS DECIMAL (4, 1)) AS [FreeSpacePC]
    FROM
   (SELECT
    allocation_unit_id,
    SUM (CASE WHEN ([is_modified] = 1)
        THEN 1 ELSE 0 END) AS [DPCount],
    SUM (CASE WHEN ([is_modified] = 1)
        THEN 0 ELSE 1 END) AS [CPCount],
    SUM (CASE WHEN ([is_modified] = 1)
        THEN CAST ([free_space_in_bytes] AS BIGINT) ELSE 0 END) AS [DPFreeSpace],
    SUM (CASE WHEN ([is_modified] = 1)
        THEN 0 ELSE CAST ([free_space_in_bytes] AS BIGINT) END) AS [CPFreeSpace]
FROM sys.dm_os_buffer_descriptors
WHERE [database_id] = DB_ID (''?'')
GROUP BY [allocation_unit_id]) AS buffers
 INNER JOIN sys.allocation_units AS au
ON au.[allocation_unit_id] = buffers.[allocation_unit_id]
 INNER JOIN sys.partitions AS p
ON au.[container_id] = p.[partition_id]
 INNER JOIN sys.indexes AS i
ON i.[index_id] = p.[index_id] AND p.[object_id] = i.[object_id]
WHERE p.[object_id] > 100 AND ([DPCount] + [CPCount]) > 12800 -- Taking up more than   100MB
  ORDER BY [FreeSpacePC] DESC;
  END';

Use the querys above in SP's to run at a time period to collect the data you require and this would help you analyse better!

StackExchange Database Administrators Q#89738, answer score: 2