[n00b] Buffer Manager Question
-
IT Pro with basic SQL administration skills. Asked to start 'looking into' performance related issues on geospatial databases.
I came across an article some place that suggested I check some of the Memory/Buffer Manager statistics. I ran this query:
SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
and this is what it returned, seems extraordinary to my ignorant eyes.
counter_namecntr_valueBuffer cache hit ratio 1939
Buffer cache hit ratio base 1939
Page lookups/sec 258618204542
Free list stalls/sec 7007
Database pages 548350
Target pages 32751616
Integral Controller Slope 0
Lazy writes/sec 32677
Readahead pages/sec 8359214
Page reads/sec 16466857
Page writes/sec 238703906
Checkpoint pages/sec 9100680
Background writer pages/sec 22
Page life expectancy 818504
Am I crazy?
~illushinz
-
I assume you're referring to the ungodly-high Anything/sec counters?
It should seem extraordinary, and you're not crazy 🙂
Those metrics, despite being named .../sec, are not actually stored as a per second calculated figure. They're just stored as basic counters, so that number you're seeing is just a cumulative count (for example, page reads/sec is just a count of all the page reads that have occurred since the counter initialized).
If you look at those using Perfmon, then it will perform some calculations using those base numbers to show you the number you expect.
Cheers!
-
WHEW! Ok, I'll take a look at them in perfmon and see how they look there.
Thanks a bunch!
-
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply