Archive

Archive for the ‘Monitor CPU’ Category

CPU Utilization from Ring Buffer

December 27, 2012 Leave a comment

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%
Categories: CPU

Physical and Logical Processor from DMV

December 13, 2012 Leave a comment

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

 

 

Categories: CPU, Monitor CPU

Monitor CPU utilization from T-SQL & Powershell

December 4, 2012 Leave a comment

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(‘ :’,output)+2, len(output)) + ‘ %’ from @CPU where Output IS NOT NULL

Print @Text + ‘ : ‘ +@Utilization

SET NOCOUNT OFF

go 10
CPU Utilization

Categories: Monitor CPU