July 28, 2009 at 10:05 am
Hi,
I am a first time poster.
I would like to calculate writes and average IO latency between snapshot data coming for the fn_virtualfilestats function. Each row of data is a snapshot of current total counters since SQL was started by database and file_id. I am recording the data on an hourly basis and would like to process the data into another table with total IO and average stall latency, comparing the current total with previous total from the hour before.
So difference between num_of_writes each hour and difference io_stall_write_ms divided by number of writes that hour. I hope thats a detailed enough description.
Here is the table and data script.
CREATE TABLE [dbo].[pcpt1](
[database_id] [smallint] NOT NULL,
[file_id] [smallint] NOT NULL,
[num_of_writes] [bigint] NOT NULL,
[io_stall_write_ms] [bigint] NOT NULL,
[capture_time] [datetime] NOT NULL)
go
Insert into pcpt1
select 2,1,170001,23876081,'2009-07-28 00:00:02.203'
Insert into pcpt1
select 2,2,633917,2741071,'2009-07-28 00:00:02.203'
Insert into pcpt1
select 2,3,169833,23832163,'2009-07-28 00:00:02.203'
Insert into pcpt1
select 6,1,379022,617222,'2009-07-28 00:00:02.203'
Insert into pcpt1
select 6,2,3089921,3148843,'2009-07-28 00:00:02.203'
Insert into pcpt1
select 6,3,6220821,10492830,'2009-07-28 00:00:02.203'
Insert into pcpt1
select 2,1,170547,23876800,'2009-07-28 01:00:04.930'
Insert into pcpt1
select 2,2,634571,2742456,'2009-07-28 01:00:08.667'
Insert into pcpt1
select 2,3,170356,23832853,'2009-07-28 01:00:08.667'
Insert into pcpt1
select 6,1,379183,617227,'2009-07-28 01:00:08.667'
Insert into pcpt1
select 6,2,3095927,3149741,'2009-07-28 01:00:08.667'
Insert into pcpt1
select 6,3,6225302,10493228,'2009-07-28 01:00:08.667'
Insert into pcpt1
select 2,1,171475,23883602,'2009-07-28 02:00:01.940'
Insert into pcpt1
select 2,2,636022,2745927,'2009-07-28 02:00:02.253'
Insert into pcpt1
select 2,3,171298,23839810,'2009-07-28 02:00:02.253'
Insert into pcpt1
select 6,1,379465,618017,'2009-07-28 02:00:02.253'
Insert into pcpt1
select 6,2,3105258,3151886,'2009-07-28 02:00:02.253'
Insert into pcpt1
select 6,3,6231743,10519460,'2009-07-28 02:00:02.253'
July 28, 2009 at 10:13 am
If you use a CTE and the Row_Number function, ordered by the datetime of the data, it's pretty easy to join a row in a table to the row before it. That should be enough to accomplish what you need, right?
Does that help?
- 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
July 29, 2009 at 9:20 am
Thanks for the guidance GSquared!
I learned a lot about CTE's, the row_number function and joins.
=================================================
WITH myCTE AS
(select database_id
,file_id,capture_time
,row_number () over (order by database_id,file_id,capture_time) as 'RowNumber'
,num_of_writes
,io_stall_write_ms
FROM pcpt1)
SELECT
myCTE.database_id
,myCTE.file_id
,myCTE.capture_time
,myCTE.num_of_writes
,myCTE.num_of_writes-pcpt1.num_of_writes as 'Writes'
,myCTE.io_stall_write_ms-pcpt1.io_stall_write_ms as 'Write Stall ms'
,(myCTE.io_stall_write_ms-pcpt1.io_stall_write_ms)/(myCTE.num_of_writes-pcpt1.num_of_writes) as 'Ave write Ms'
FROM
myCTE
inner join pcpt1 on myCTE.database_id=pcpt1.database_id and myCTE.file_id=pcpt1.file_id
WHERE datediff(hh,pcpt1.capture_time,myCTE.capture_time)=1
July 30, 2009 at 6:42 am
Looks good.
The reason I mentioned Row_Number() for this is that you can join a CTE to itself, and you could include in the join criteria "and CTE1.Row = CTE2.Row-1", if you've aliased the first instance of the CTE as CTE1 and the second as CTE2. That's where you get the immediately prior row.
But it looks like you solved it without that. Well done.
- 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
January 22, 2010 at 8:40 am
Just wanted to leave a quick comment to say that this post saved me a lot of time.
Thanks very much, GSquared - your help is greatly appreciated!
-Simon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply