How to know real SQL Server memory and CPU usage

My SQL Server 2019 SE runs on Windows Server 2019 SE, with hardware specs as below:

40 Core
1TB RAM

I think the above spec is overkill!, but if I see from Windows Task Manager:

the SQL Server memory usage is: 384GB and the Processor usage is 8 Core.

How log the real SQL Server memory and CPU utilization per minute into a table, so I can analyze what is the real spec required?

Create a job in SQL Server Agent, that would run on a schedule each minute

In the job, you can use this T-SQL to gather current CPU and memory consumption


declare
@CPU_Usage_Percentage int,
@Total_SQL_Server_Memory_MB int

-- CPU
WITH y AS (
SELECT
CONVERT(VARCHAR(5), 100 - ca.c.value('.', 'INT')) AS system_idle,
CONVERT(VARCHAR(30), rb.event_date) AS event_date,
CONVERT(VARCHAR(8000), rb.record) AS record
FROM (
SELECT
CONVERT(XML, dorb.record) AS record,
DATEADD(ms, ( ts.ms_ticks - dorb.timestamp ), GETDATE()) AS event_date
FROM sys.dm_os_ring_buffers AS dorb
CROSS JOIN (
SELECT
dosi.ms_ticks
FROM sys.dm_os_sys_info AS dosi ) AS ts
WHERE dorb.ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%%' ) AS rb
CROSS APPLY rb.record.nodes('/Record/SchedulerMonitorEvent/SystemHealth/SystemIdle') AS ca(c)
)
SELECT @CPU_Usage_Percentage = (select
TOP 1 y.system_idle
FROM y
ORDER BY y.event_date DESC)

-- memory
select @Total_SQL_Server_Memory_MB = (select
cntr_value / 1024
from sys.dm_os_performance_counters pc
where [object_name] = 'SQLServer:Memory Manager'
and counter_name = 'Total Server Memory (KB)'
)

select
@CPU_Usage_Percentage [CPU_Usage_Percentage],
@Total_SQL_Server_Memory_MB [Total_SQL_Server_Memory_MB]

Insert values of @CPU_Usage_Percentage and @Total_SQL_Server_Memory_MB into logging table, and you are good

StackExchange Database Administrators Q#298516, answer score: 6