PERFORMANCE COUNTER THROUGH QUERYif object_id('tempdb..#perfMon') is not null Drop table #PerfMOn

/*
To add new counter
use below command
UNION
select 'Worktables Created/sec',0,0,0,0

*/


if object_id('tempdb..#perfMon') is not null Drop table #PerfMOn
create table #PerfMon(Counter_name varchar(1000),First_value decimal(18,2),Second_Value decimal(18,2),Time_interval Int,Sampling_Value Decimal(18,2))
insert into #PerfMon
select 'Worktables Created/sec',0,0,0,0
UNION
select 'Workfiles Created/sec',0,0,0,0
UNION
select 'Transactions/sec',0,0,0,0
UNION
select 'Target Server Memory (KB)',0,0,0,0
UNION
select 'Lock waits',0,0,0,0
UNION
select 'Logins/sec',0,0,0,0
UNION
select 'Logouts/sec',0,0,0,0
UNION
select 'age life expectancy',0,0,0,0
UNION
select 'Page reads/sec',0,0,0,0
UNION
select 'age writes/sec',0,0,0,0
UNION
select 'SQL Compilations/sec',0,0,0,0
UNION
select 'SQL Re-Compilations/sec',0,0,0,0
UNION
select 'Average Latch Wait Time (ms)',0,0,0,0
UNION
select 'Batch Requests/sec',0,0,0,0
UNION
select 'Buffer cache hit ratio base',0,0,0,0
UNION
select 'Lock Timeouts/sec',0,0,0,0


update #PerfMon set First_value = cntr_value
from sys.dm_os_performance_counters s,#PerfMon p where s.counter_name = p.Counter_name
Waitfor Delay '00:05:00';
update #PerfMon set Time_interval = 300
update #PerfMon set Second_Value = cntr_value
from sys.dm_os_performance_counters s,#PerfMon p where s.counter_name = p.Counter_name
update #PerfMon set Sampling_Value = (Second_Value-First_Value)/Time_interval
select * from #PerfMOn