SQL Memory Usage Cumulative for each Database

Question

The following query shows how to find Current view of Buffer Memory used by each database. How do I find Average/Cumulative Memory buffer usage of each database since the last SQL server restart, not just a current time snapshot?

We have 5 microservice databases on one server, no cross database joins, do not relate. Stored procedures are called from appropriate db. We are trying to separate into different servers, and find baseline Ram and core processor requirements, not sure best way to do this

Thanks,

Determine SQL Memory usage by database

-- Note: querying sys.dm_os_buffer_descriptors
-- requires the VIEW_SERVER_STATE permission.

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 = 'Database 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; 

Answer 1

I don't believe there is any cumulative / average metric for this. Nor do I think it makes sense to look at it this way. I don't think you can derive any useful conclusion from such a view of things. This information is too volatile to use in that way. Some stuff ages out faster than others. It greatly depends on which pages are being accessed frequently, how much memory is available on the system, etc. Also, the Buffer Pool is not the only use of memory. There is also permissions cache, plan cache, DB Metadata cache, and others. Hence, such an approach will most likely be misleading.

In addition to looking in sys.dm_os_buffer_descriptors, there are also the following resources to check:

• DBCC MEMORYSTATUS;
• SELECT * FROM sys.dm_os_memory_clerks;
• SELECT * FROM sys.dm_os_performance_counters;

Regarding those three resources, please note:

1. there is some duplication between the DBCC and the others (and even some other DMVs not listed here)
2. in each of those resources, there are some entries that cover all databases and cannot easily be broken down, such as Connection Pool cache, plan cache, etc

Answer 2

To break down the plan cache by database you can GROUP BY the dbid plan attribute, eg

select db_name(try_cast(pa.value as int)) DatabaseName, 
       sum(size_in_bytes)/1024 plan_size_kb
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_plan_attributes(cp.plan_handle) pa
where attribute = 'dbid'
group by try_cast(pa.value as int)