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 […]

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    

Monitor CPU utilization from T-SQL & Powershell

This piece of code may help when you are not able to remote login to the server. Script: SET NOCOUNT ON DECLARE @CPU TABLE (Output varchar(max)) DECLARE @Text varchar(20); DECLARE @Utilization varchar(10); insert into @CPU exec master.dbo.xp_cmdshell ‘Powershell -Command Get-WmiObject win32_processor’ delete from @CPU where output not like ‘Load%’ select @Text= ‘CPU Utilization’ , @Utilization=substring(output,charindex(‘ […]