SQL Server 記憶體監控 (Memory Monitor)
2021-06-10
筆記 SQL Server 關於資料庫伺服器記憶體、資料庫使用記憶體的查詢筆記。
說明
查詢 OS 的記憶體情況
SELECT
total_physical_memory_kb/1024 as [總實體記憶體(MB)]
,available_physical_memory_kb/1024 as [可用實體記憶體(MB)]
,total_page_file_kb/1024 as [總分頁檔(MB)]
,available_page_file_kb /1024 as [可用分頁檔(MB)]
,system_cache_kb/1024 as [系統快取記憶體(MB)]
,system_high_memory_signal_state
,system_low_memory_signal_state
,system_memory_state_desc
FROM sys.dm_os_sys_memory 查詢 SQL Server 各項資源使用記憶體的情況
SELECT type
,sum(pages_kb)/1024 [記憶體佔用大小(MB)]
,sum(virtual_memory_reserved_kb)/1024 [保留虛擬記憶體的數量(MB)]
,sum(virtual_memory_committed_kb)/1024 [使用虛擬記憶體的數量(MB)]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY [記憶體佔用大小(MB)] DESC查詢各資料庫使用的記憶體
SELECT
DB_NAME(database_id) [資料庫],
Count(*)/128 as [耗用暫存記憶體(MB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY 2 desc查詢特定資料庫中各物件使用的記憶體
SELECT
OBJECT_NAME(p.object_id) AS [物件名稱],
p.index_id,
i.name AS [索引名稱],
COUNT(*) * 8 / 1024 AS [記憶體使用量(MB)],
COUNT(*) AS [頁面數量]
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN sys.allocation_units AS au
ON bd.allocation_unit_id = au.allocation_unit_id
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
LEFT JOIN sys.indexes AS i
ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE bd.database_id = DB_ID()
AND OBJECTPROPERTY(p.object_id, 'IsUserTable') = 1
GROUP BY p.object_id, p.index_id, i.name
ORDER BY [記憶體使用量(MB)] DESC摘要 SQL Server 記憶體相關資訊
DBCC MEMORYSTATUS