[Database] [MSSQL] 서버의 각 데이터베이스별 메모리 사용량 확인하기.

* 각 데이터베이스별로 캐쉬된 메모리를 확인하기 위해 사용하는 쿼리이다.
* 출처 : https://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/

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;