2

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; 
6
  • 3
    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. Such an approach will most likely be misleading. Jan 24, 2018 at 7:10
  • What do you want to find out after getting those stats. Even which database is using most of the memory is of little help it only means most of the data pages belonging to that database was queried frequently there is no useful meaning which can be derived from it
    – Shanky
    Jan 24, 2018 at 7:33
  • You've been asking a lot of questions lately that monitoring tools have already answered. Rather than stumble through reinventing the wheel, why not just trial some paid tools and find one that works for you? I guarantee it'll be cheaper than all the time you'll spend on this. Jan 24, 2018 at 12:38
  • Although this information isn't particularly actionable, you could schedule the query to take periodic snapshots with the snapshot time and store in a permanent table. That would allow you to calculate the usage over time.
    – Dan Guzman
    Jan 24, 2018 at 12:57
  • Well we have all these 5 microservice databases on one server, no cross database joins, do not relate, we are trying to separate into different server, and gauge ram and core processor requirements, not sure best way to do this
    – user129291
    Jan 24, 2018 at 13:04

2 Answers 2

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
1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.