CPU HyperThreading Core evaluation

Question

Hello,

We are currently trying to evaluate our current On-Prem Sql Server CPU HyperThreading Core evaluation for license cost purpose.

Our current Sql Servers are from 2014 to 2019 sql version.

Is it any prefer way I can estimate that Sql server has the right CPU configuration?

I know that CPU utilization depends on Query, sessions, index, stats and other factors also but we are looking if we have room to decrease CPU cores.

I tried to check the query using "sys.dm_os_schedulers" DMV views with Status = 'VISIBLE ONLINE'

Appreciate your help and guidance!

Answer 1

Here is a query taken from Dmitri Korotkevitch's book SQL Server Advanced Troubleshooting and Performance Tuning (of which I was a tech reviewer) that gives you the CPU load for the last 256 minutes with measurements once by minute:

DECLARE @now BIGINT;
SELECT @now = cpu_ticks / (cpu_ticks / ms_ticks)
FROM sys.dm_os_sys_info WITH (NOLOCK);
;WITH RingBufferData([timestamp], rec) AS
(
    SELECT [timestamp], CONVERT(XML, record) AS rec
    FROM   sys.dm_os_ring_buffers WITH (NOLOCK)
    WHERE  ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
      AND   record LIKE N'%<SystemHealth>%'
), Data(id, SystemIdle, SQLCPU, [timestamp]) AS (
    SELECT rec.value('(./Record/@id)[1]', 'int')
          ,rec.value ('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]','int')
          ,rec.value ('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int')
         ,[timestamp]
    FROM RingBufferData
)
SELECT TOP 256 dateadd(MS, -1 * (@now - [timestamp]), getdate())  AS [Event Time],
       SQLCPU AS [SQL Server CPU Utilization]
      ,SystemIdle AS [System Idle]
      ,100 - SystemIdle - SQLCPU AS [Other Processes CPU Utilization]
FROM Data
ORDER BY id desc
OPTION (RECOMPILE, MAXDOP 1);

You could schedule this query to save data to table a couple of times per day. The higher percentages you see, the less likely that you can cut down on CPUs.

Answer 2

Hi @pdsqsql

You may also consider using Performance Monitor (PerfMon) counters to track SQL Server CPU performance.

See this doc: Monitor CPU Usage.

An efficient way to determine CPU usage is to use the Processor:% Processor Time counter in Performance Monitor. This counter monitors the amount of time the CPU spends executing a thread that is not idle. A consistent state of 80 percent to 90 percent might indicate the need to upgrade your CPU or add more processors. For multiprocessor systems, monitor a separate instance of this counter for each processor. This value represents the sum of processor time on a specific processor. To determine the average for all processors, use the System: %Total Processor Time counter instead.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".