memory check, lpim check

--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