How to know real SQL Server memory and CPU usage

Question

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?

• Bob Klimes: Standard edition max CPU is 24 cores or 4 sockets. and max bufferpool size is 128GB, there are various other features that will use memory on top of this. regarless of what you actually use, you are definitely over provisioned for just SQL Server Standard Edition learn.microsoft.com/en-us/sql/sql-server/…

Answer 1

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

• David Browne - Microsoft: Or run a Perfmon trace.
• Donald: the result is the same with Task Manager, but I'm not sure about the result since SQL Server will take full of available RAM
• Aleksey Vitsko: @Donald Total Server Memory = Total amount of dynamic memory the server is currently consuming
• Aleksey Vitsko: @Donald SQL Server is consuming as much as it needs, it won't take "full of available RAM" if it does not need it all
• Donald: the problem is we need to have the exact amount of the required memory because we have a plan to move SQL Server to Alibaba Cloud RDS, currently, it consumes 384GB (it is really don't make sense for me), if we use 384GB as the requirement, it would be a very very expensive. that's why I need to get the exact memory value required by the SQL Server. any idea?