memory check, lpim check
카테고리 없음 / 2023. 7. 4. 12:39
--2019
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT '01' OrderKey,'OS_MEMORY_TOTAL' MemType, physical_memory_kb PagesKB FROM SYS.DM_OS_SYS_INFO
UNION ALL
SELECT '02' OrderKey,'SQL_VISIBLE_TARGET_KB' MemType, visible_target_kb PagesKB FROM SYS.DM_OS_SYS_INFO
UNION ALL
SELECT '03' OrderKey,'SQL_COMMITTED_KB' MemType, committed_kb PagesKB FROM SYS.DM_OS_SYS_INFO
UNION ALL
SELECT '04' OrderKey,'OS_MEMORY_CLERKS_TOTAL' MemType, SUM(pages_kb) + sum(virtual_memory_committed_kb)+sum(awe_allocated_kb)+sum(shared_memory_committed_kb) PagesKB FROM SYS.DM_OS_MEMORY_CLERKS
UNION ALL
SELECT *
FROM
(
SELECT TOP 20 '04' OrderKey, type MemType, SUM(pages_kb) +sum(virtual_memory_committed_kb)+sum(awe_allocated_kb)+sum(shared_memory_committed_kb) PagesKB FROM SYS.DM_OS_MEMORY_CLERKS
GROUP BY TYPE
ORDER BY 3 DESC
) A
ORDER BY 1,3 DESC
-- lpim check
SELECT a.memory_node_id, node_state_desc, a.locked_page_allocations_kb
FROM sys.dm_os_memory_nodes a
INNER JOIN sys.dm_os_nodes b ON a.memory_node_id = b.memory_node_id
-- old version
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--SELECT '01' OrderKey,'OS_MEMORY_TOTAL' MemType, physical_memory_in_bytes/1024 PagesKB FROM SYS.DM_OS_SYS_INFO
--UNION ALL
--SELECT '02' OrderKey,'SQL_VISIBLE_TARGET_KB' MemType, bpool_visible * 8 PagesKB FROM SYS.DM_OS_SYS_INFO
--UNION ALL
--SELECT '03' OrderKey,'SQL_COMMITTED_KB' MemType, bpool_committed * 8 PagesKB FROM SYS.DM_OS_SYS_INFO
--UNION ALL
--SELECT '04' OrderKey,'OS_MEMORY_CLERKS_TOTAL' MemType, SUM(single_pages_kb) + sum(multi_pages_kb) + sum(virtual_memory_committed_kb)+sum(awe_allocated_kb)+sum(shared_memory_committed_kb) PagesKB FROM SYS.DM_OS_MEMORY_CLERKS
--UNION ALL
--SELECT *
--FROM
--(
--SELECT TOP 20 '04' OrderKey, type MemType, SUM(single_pages_kb) + sum(multi_pages_kb) +sum(virtual_memory_committed_kb)+sum(awe_allocated_kb)+sum(shared_memory_committed_kb) PagesKB FROM SYS.DM_OS_MEMORY_CLERKS
--GROUP BY TYPE
--ORDER BY 3 DESC
--) A
--ORDER BY 1,3 DESC