Capture `Working Set` & `% Processor Time` for all processes in Perfmon

[imajaydwivedi]

Capture Working Set & % Processor Time for all processes in Perfmon

Remove job/collection of OSTaskList

Query to plot for Memory Utilization of all processes

;with cte_process_memory as (
	select pc.collection_time_utc, pc.host_name, pc.object, pc.counter,
			pc.value, 
			pc.instance,
			process = case when CHARINDEX('#',s1.instance) > 0 then left(s1.instance, CHARINDEX('#',s1.instance)-1) else s1.instance end
	from dbo.performance_counters pc
	outer apply (select instance = case when CHARINDEX('$',pc.instance) > 0 then left(pc.instance, CHARINDEX('$',pc.instance)-1) else pc.instance end ) s1
	where 1=1
	--and collection_time_utc >= DATEADD(minute,-5,getutcdate())
	and collection_time_utc = (select max(i.collection_time_utc) from dbo.performance_counters i)
	and object = 'process' and counter = 'working set'
	--and object = 'process' and counter = '% processor time'
	and pc.instance <> '_total'
)
select top 10 collection_time_utc, p.host_name, p.object, p.counter, p.process
		,memory_mb = floor(sum(p.value)/1024.0/1024.0)
from cte_process_memory p
group by collection_time_utc, p.host_name, p.object, p.counter, p.process
order by memory_mb desc;
go

Query to plot for CPU Utilization of all processes

;with cte_process_cpu as (
	select pc.collection_time_utc, pc.host_name, pc.object, pc.counter,
			pc.value, 
			pc.instance,
			process = case when CHARINDEX('#',s1.instance) > 0 then left(s1.instance, CHARINDEX('#',s1.instance)-1) else s1.instance end
	from dbo.performance_counters pc
	outer apply (select instance = case when CHARINDEX('$',pc.instance) > 0 then left(pc.instance, CHARINDEX('$',pc.instance)-1) else pc.instance end ) s1
	where 1=1
	--and collection_time_utc >= DATEADD(minute,-5,getutcdate())
	and collection_time_utc = (select max(i.collection_time_utc) from dbo.performance_counters i)
	and object = 'process' and counter = '% processor time'
	and pc.instance not in ('_total','idle')
)
select top 10 collection_time_utc, p.host_name, p.object, p.counter, p.process
		,cpu_percent = ceiling(sum(p.value)/si.cpu_count)
from cte_process_cpu p
outer apply (select si.cpu_count from sys.dm_os_sys_info si) si
group by collection_time_utc, p.host_name, p.object, p.counter, p.process, si.cpu_count
order by cpu_percent desc;
go