Here are a couple of scripts that make the numbers more useful.
The first gives you all counters, including absolute values (like page life expectancy), percentages calculated by dividing a counter by a base (like cache hit ratio) and per-second averages since the last service restart.
use master;
set nocount on;
declare @up_secs bigint
select @up_secs = datediff(second, create_date, getdate()) from sys.databases where name = 'tempdb'
select rtrim(object_name) object_name, rtrim(instance_name) instance_name, rtrim(counter_name) counter_name, cntr_value
from sys.dm_os_performance_counters
where cntr_type = 65792
union all
select rtrim(object_name), rtrim(instance_name), rtrim(counter_name), 1. * cast(cntr_value as bigint) / @up_secs
from sys.dm_os_performance_counters
where cntr_type = 272696576
union all
select rtrim(v.object_name), rtrim(v.instance_name), rtrim(v.counter_name), 100. * v.cntr_value / case when b.cntr_value = 0 then 1 else b.cntr_value end
from (
select object_name, instance_name, counter_name, cntr_value
from sys.dm_os_performance_counters
where cntr_type = 537003264) v
join (
select object_name, instance_name, counter_name, cntr_value
from sys.dm_os_performance_counters
where cntr_type = 1073939712) b on v.object_name = b.object_name and v.instance_name = b.instance_name and rtrim(v.counter_name) + ' base' = rtrim(b.counter_name)
union all
select rtrim(v.object_name), rtrim(v.instance_name), rtrim(v.counter_name), 1. * v.cntr_value / case when b.cntr_value = 0 then 1 else b.cntr_value end
from (
select object_name, instance_name, counter_name, cntr_value
from sys.dm_os_performance_counters
where cntr_type = 1073874176) v
join (
select object_name, instance_name, counter_name, cntr_value
from sys.dm_os_performance_counters
where cntr_type = 1073939712) b on v.object_name = b.object_name and v.instance_name = b.instance_name and replace(rtrim(v.counter_name), ' (ms)', '') + ' Base' = rtrim(b.counter_name)
order by object_name, instance_name, counter_name
And the second runs for 10 seconds, giving only the per-second counters over that time period only.
use master;
set nocount on;
declare @before table (
capture_time datetime,
[object_name] nvarchar(255),
instance_name nvarchar(255),
counter_name nvarchar(255),
cntr_value bigint
)
declare @after table (
capture_time datetime,
[object_name] nvarchar(255),
instance_name nvarchar(255),
counter_name nvarchar(255),
cntr_value bigint
)
insert @before
select getdate(), rtrim(object_name), rtrim(instance_name), rtrim(counter_name), cast(cntr_value as bigint)
from sys.dm_os_performance_counters
where cntr_type = 272696576
waitfor delay '00:00:10'
insert @after
select getdate(), rtrim(object_name), rtrim(instance_name), rtrim(counter_name), cast(cntr_value as bigint)
from sys.dm_os_performance_counters
where cntr_type = 272696576
select b.[object_name], b.instance_name, b.counter_name, 1000. * (a.cntr_value - b.cntr_value) / datediff(ms, b.capture_time, a.capture_time) cntr_value
from @before b
join @after a on b.[object_name] = a.[object_name] and b.instance_name = a.instance_name and b.counter_name = a.counter_name
order by 1, 2, 3, 4
