How to check buffer pool size in sql server?

Question

Is there any way to find total allocated buffer pool size in sql server.In mysql we can find it by using the variable innodb_buffer_pool_size.Is there any equivalent for that in sql server?

Answer 1

To find out how much buffer cache each database on the sql instance is using you could run this query which uses the dmv sys.dm_os_buffer_descriptors:

SELECT
  database_id AS DatabaseID,
  DB_NAME(database_id) AS DatabaseName,
  COUNT(file_id) * 8/1024.0 AS BufferSizeInMB
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id),database_id
ORDER BY BufferSizeInMB DESC
GO