CPU Utilization from Ring Buffer
SQL Server DMV collects data on an interval,sys.dm_os_ring_buffers provided cpu utilzation percentage by SQL Server.exe and other processes.
select top 1 record_id,
SQLProcessUtilization,SystemIdle,
100 – SystemIdle – SQLProcessUtilization as OtherProcessUtilization
from (
select
record.value(‘(./Record/@id)[1]’, ‘int’) as record_id,
record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]’, ‘int’) as SystemIdle,
record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]’, ‘int’) as SQLProcessUtilization,
timestamp
from (
select timestamp, convert(xml, record) as record
from sys.dm_os_ring_buffers
where ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’
and record like ‘%<SystemHealth>%’) as x
) as y
order by record_id desc
record_id | SQLProcessUtilization | SystemIdle | OtherProcessUtilization |
46895 | 0% | 92% | 8% |
Physical and Logical Processor from DMV
This will help in having quick result on Physical and Logical Processor on a Server using SQL Server DMV.
DMV sys.dm_os_sys_info will provide this information
SELECT (cpu_count/hyperthread_ratio) as PhysicalCPUs,cpu_count as LogicalCPUs FROM sys.dm_os_sys_info