[SQL SERVER][TSQL]查詢緩衝區資料頁所使用記憶體大小
前言
所有RDBMS設計最主要目的之一，就是硬碟I/O最小化，
所以SQL Server會在記憶體中建立緩衝集區(Buffer pool)，
藉以保存從資料庫所讀取的資料頁面(data page)。
我們可以透過sys.dm_os_buffer_descriptors 這個DMV來查看緩衝區相關頁面使用量資訊。
查詢某物件所耗用緩衝區記憶體大小
SELECT count(*) * 8 AS '記憶體大小(Kb)'
,name ,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
and name='REPORT_STATISTICS'
GROUP BY name, index_id
ORDER BY '記憶體大小(Kb)' DESC;
查詢每個資料庫所使用的緩衝區記憶體
SELECT count(*) * 8 / 1024 AS '記憶體大小(Mb)'
,sum (CONVERT (bigint, free_space_in_bytes)) / (1024) AS '空閒記憶體(Kb)'
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY '記憶體大小(Mb)' DESC;
note:如果要清除緩衝集區可以執行 dbcc dropcleanbuffers (不必重新啟動) 。
參考