SQL Server – Cumulative Memory Usage for Each Database

dmvperformanceperformance-tuningsql server

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;

Best Answer

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:

there is some duplication between the DBCC and the others (and even some other DMVs not listed here)
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

Related Solutions

Does Detach/Attach or Offline/Online Clear Buffer Cache for Database?

I initially thought you were on to something here. Working assumption was along the lines that perhaps the buffer pool wasn't immediately flushed as it requires "some work" to do so and why bother until the memory was required. But...

Your test is flawed.

What you're seeing in the buffer pool is the pages read as a result of re-attaching the database, not the remains of the previous instance of the database.

And we can see that the buffer pool was not totally blown away by the detach/attach. Seems like my buddy was wrong. Does anyone disagree or have a better argument?

Yes. You're interpreting physical reads 0 as meaning there were not any physical reads

Table 'DatabaseLog'. Scan count 1, logical reads 782, physical reads 0, read-ahead reads 768, lob logical reads 94, lob physical reads 4, lob read-ahead reads 24.

As described on Craig Freedman's blog the sequential read ahead mechanism tries to ensure that pages are in memory before they're requested by the query processor, which is why you see zero or a lower than expected physical read count reported.

When SQL Server performs a sequential scan of a large table, the storage engine initiates the read ahead mechanism to ensure that pages are in memory and ready to scan before they are needed by the query processor. The read ahead mechanism tries to stay 500 pages ahead of the scan.

None of the pages required to satisfy your query were in memory until read-ahead put them there.

As to why online/offline results in a different buffer pool profile warrants a little more idle investigation. @MarkSRasmussen might be able to help us out with that next time he visits.

Sql-server – Monitor Database Memory usage SQL Server

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!

Best Answer

Related Solutions

Does Detach/Attach or Offline/Online Clear Buffer Cache for Database?

Sql-server – Monitor Database Memory usage SQL Server

Related Question