February 7, 2011 at 12:31 pm
I have a table that contains persisted data from the 'dm_os_performance_counters' view:
CREATE TABLE [GRID].[dm_os_performance_counters](
[object_name] [nvarchar](128) NULL,
[counter_name] [nvarchar](128) NULL,
[instance_name] [nvarchar](128) NULL,
[cntr_value] [bigint] NULL,
[date_stamp] [datetime] NULL
) ON [PRIMARY]
I'd like to get the 'buffer cache hit ratio' out of that table (which requires taking the existing 'buffer cache hit ratio' and dividing by 'buffer cache hit ratio base') over a specified period of time. I've tried:
select cast(a.cntr_value as numeric)/cast(b.cntr_value as numeric), a.date_stamp
from (select cntr_value, date_stamp, object_name from GRID.dm_os_performance_counters
where object_name = 'SQLServer:Buffer Manager'
and counter_name = 'Buffer Cache hit ratio'
and date_stamp between '2011-02-04 13:30:00.000' and '2011-02-04 14:30:00.000'
) a
join
(select cntr_value, date_stamp, object_name from GRID.dm_os_performance_counters
where object_name = 'SQLServer:Buffer Manager'
and counter_name = 'Buffer Cache hit ratio base'
and date_stamp between '2011-02-04 13:30:00.000' and '2011-02-04 14:30:00.000'
) b
on a.object_name = b.object_name
and
select cast(a.cntr_value as numeric)/cast(b.cntr_value as numeric), a.date_stamp
from (select cntr_value, date_stamp, object_name from GRID.dm_os_performance_counters
where object_name = 'SQLServer:Buffer Manager'
and counter_name = 'Buffer Cache hit ratio'
) a
join
(select cntr_value, date_stamp, object_name from GRID.dm_os_performance_counters
where object_name = 'SQLServer:Buffer Manager'
and counter_name = 'Buffer Cache hit ratio base'
) b
on a.object_name = b.object_name
where a.date_stamp between '2011-02-04 13:30:00.000' and '2011-02-04 14:30:00.000'
Can this be done without the join? Neither of the top two queries work and I'm struggling as to why.
I'd like it to look like this (i'll clean up the date stamp, this was a copy/paste from excel):
counter_namedate_stampa/b
-------------------------
Buffer cache hit ratio30:30.20.967619048
Buffer cache hit ratio35:30.10.998540146
Buffer cache hit ratio40:30.00.996662959
Buffer cache hit ratio45:30.10.99871134
Buffer cache hit ratio50:30.00.990990991
Buffer cache hit ratio55:30.10.992625369
Buffer cache hit ratio00:30.00.987915408
Buffer cache hit ratio05:30.00.998496241
Buffer cache hit ratio10:30.00.998909487
Buffer cache hit ratio15:30.10.983455882
Buffer cache hit ratio20:30.10.990131579
Buffer cache hit ratio25:30.00.996563574
TIA.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
February 7, 2011 at 12:49 pm
Do you need to join on the timestamp, instead of the object name? The way you've got it written right now, it'll be a cross join between the two sub-selects, because the join criteria are part of the inner Where clauses. So every row from a will be matched to every row from b.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 7, 2011 at 2:51 pm
Wow...duh! Thanks GS. Forgot my set theory for a second.
Is a join the only way to do this?
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
February 8, 2011 at 7:16 am
An inline sub-query in the Select clause would work just as well as an explicit join, probably. Has the advantage/liability that it will give an error if there are two matches.
select ColA,
(select Col1
from MyOtherTable
where MyOtherTable.Col2 = MyTable.ColB) as Col1
from MyTable;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply