CPU use for the last few hours (SQL Server and everything else), memory use, and which memory clerks are using it. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server 2017 and 2025.

-- 10_Memory_and_CPU.sql - CPU use for the last few hours, memory use, and what is using the memory. Read-only.
SET NOCOUNT ON;
SELECT TOP (240) DATEADD(MILLISECOND, -1 * (si.ms_ticks - rb.[timestamp]), GETDATE()) AS [time],
       rb.x.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_pct,
       100 - rb.x.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
           - rb.x.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS other_cpu_pct
FROM (SELECT [timestamp], CONVERT(xml, record) AS x FROM sys.dm_os_ring_buffers
      WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE N'%<SystemHealth>%') AS rb
CROSS JOIN sys.dm_os_sys_info AS si
ORDER BY rb.[timestamp] DESC;

SELECT physical_memory_in_use_kb / 1024 AS sql_memory_mb, memory_utilization_percentage,
       process_physical_memory_low, process_virtual_memory_low,
       (SELECT available_physical_memory_kb / 1024 FROM sys.dm_os_sys_memory) AS os_free_mb,
       (SELECT system_memory_state_desc FROM sys.dm_os_sys_memory) AS os_memory_state,
       (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = N'Page life expectancy' AND [object_name] LIKE N'%Buffer Manager%') AS page_life_expectancy,
       (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = N'Memory Grants Pending') AS memory_grants_pending
FROM sys.dm_os_process_memory;

-- which databases are using the buffer pool
SELECT CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE DB_NAME(database_id) END AS [database], COUNT(*) / 128 AS cached_mb
FROM sys.dm_os_buffer_descriptors GROUP BY database_id ORDER BY cached_mb DESC;

SELECT TOP (10) [type], SUM(pages_kb) / 1024 AS mb FROM sys.dm_os_memory_clerks GROUP BY [type] ORDER BY mb DESC;