Memory DMVs

Where is SQL Server's memory actually going? This collection covers sys.dm_os_buffer_descriptors and the Buffer Manager entries in sys.dm_os_performance_counters. The first query reports how many buffer pages each database holds and what percentage that is of the whole buffer pool; the second drills down to schema/object/index level so you can see which tables and indexes are eating the cache. Both require VIEW_SERVER_STATE. The final query pulls the raw Buffer Manager counters (free/total/database pages and target vs actual server memory) for a quick at-a-glance read.

/*
sys.dm_os_buffer_descriptors
sys.dm_os_performance_counters
*/


-------------------------------
-- Get memory usage by database - tip 2393
-------------------------------
-- 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 = '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; 



-------------------------------
-- Get memory usage by database objects - tip 2393
-------------------------------
;WITH src AS
(
   SELECT
	   [Schema] = o.schema_id,
       [Object] = o.name,
       [Type] = o.type_desc,
       [Index] = COALESCE(i.name, ''),
       [Index_Type] = i.type_desc,
       p.[object_id],
       p.index_id,
       au.allocation_unit_id
   FROM
       sys.partitions AS p
   INNER JOIN
       sys.allocation_units AS au
       ON p.hobt_id = au.container_id
   INNER JOIN
       sys.objects AS o
       ON p.[object_id] = o.[object_id]
   INNER JOIN
       sys.indexes AS i
       ON o.[object_id] = i.[object_id]
       AND p.index_id = i.index_id
   WHERE
       au.[type] IN (1,2,3)
       AND o.is_ms_shipped = 0
)
SELECT
   SCHEMA_NAME(src.[Schema]),
   src.[Object],
   src.[Type],
   src.[Index],
   src.Index_Type,
   buffer_pages = COUNT_BIG(b.page_id),
   buffer_mb = COUNT_BIG(b.page_id) / 128
FROM
   src
INNER JOIN
   sys.dm_os_buffer_descriptors AS b
   ON src.allocation_unit_id = b.allocation_unit_id
WHERE
   b.database_id = DB_ID()
GROUP BY
   SCHEMA_NAME(src.[Schema]),
   src.[Object],
   src.[Type],
   src.[Index],
   src.Index_Type
ORDER BY
   buffer_pages DESC;



-------------------------------
-- sys.dm_os_performance_counters
-------------------------------
SELECT
	object_name    
	,Counter_name     
	,cntr_value    
	,ROUND(( cntr_value * 8192.0 ) / 1048576, 0) AS cntr_value_MB  	
FROM
	sys.dm_os_performance_counters 
WHERE   
	object_Name LIKE '%Buffer Manager%'     
	AND RTRIM(counter_name) IN ( 'Free pages', 'Total pages', 'Database pages' )  
UNION 
SELECT
	object_name
	,Counter_name
	,cntr_value
	,ROUND(( cntr_value / 1024 ), 0) AS cntr_value_MB 
FROM
	sys.dm_os_performance_counters 
WHERE
	counter_name IN ( 'Target Server Memory (KB)', 'Total Server Memory (KB)' )