3

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?

1
  • 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/…
    – Bob Klimes
    Aug 25, 2021 at 18:58

1 Answer 1

6

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

8
  • Or run a Perfmon trace. Aug 25, 2021 at 18:46
  • 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
    – Donald
    Aug 26, 2021 at 2:52
  • @Donald Total Server Memory = Total amount of dynamic memory the server is currently consuming Aug 26, 2021 at 14:24
  • 1
    @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 Aug 26, 2021 at 14:25
  • 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?
    – Donald
    Aug 27, 2021 at 5:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.