January 24, 2022 at 5:27 am
Hello All, Our entire domain is on VM.The CPU's usage rarely goes above 20%.Is it advisable to consolidate from a CPU perspective?Can we reduce the CPU count ?
This is what has been used to capture CPU Usage. Please advise.
SELECT
cpu_idle = record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int'),
cpu_sql = record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int')
FROM (
SELECT TOP 1 CONVERT(XML, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '% %'
ORDER BY TIMESTAMP DESC
) as cpu_usage
January 24, 2022 at 2:51 pm
Remember, that's a VM, so you're reading what it thinks it sees from within that VM. You should also run queries against the hypervisor to see how much CPU is being consumed there.
Also, I'd never rely on a single measure to make these kinds of decisions. What do your wait statistics look like? What are the principal causes of waits & queues on the system? How many waits or cues are CPU related? Do you have aggregate measures of CPU use for your queries? What about the worst performing queries? How is their CPU use? And waits?
Gather all this data, not just the one data point. Then, the decision will be silly simple.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply