--The Virtual File Stats DMV
--select * from sys.dm_io_virtual_file_stats(null, null)

--sys.master_files sits in master and is a superset of sys.database_files cumulated for every db
--select * from sys.database_files
--select * from sys.master_files
use dba
go

insert into dba..vfsLog(
	 logdatetime
	,dbName
	,file_id
	,type
	,num_of_reads
	,num_of_bytes_read
	,io_stall_read_ms
	,num_of_writes
	,num_of_bytes_written
	,io_stall_write_ms
	,io_stall
	,size_on_disk_bytes)
select 
	current_timestamp 
	,d.name 
	,vfs.file_id
	,mf.type_desc 
	,vfs.num_of_reads
	,vfs.num_of_bytes_read
	,vfs.io_stall_read_ms
	,vfs.num_of_writes
	,vfs.num_of_bytes_written
	,vfs.io_stall_write_ms
	,vfs.io_stall
	,vfs.size_on_disk_bytes
from sys.dm_io_virtual_file_stats(null, null) vfs
join sys.databases d on d.database_id = vfs.database_id
join sys.master_files mf on  mf.database_id = vfs.database_id
						 and mf.file_id = vfs.file_id		


select * from dba..vfsLog

alter table vfsLog add _num_of_reads bigint
alter table vfsLog add _num_of_bytes_read bigint
alter table vfsLog add _io_stall_read_ms bigint
alter table vfsLog add _num_of_writes bigint
alter table vfsLog add _num_of_bytes_written bigint
alter table vfsLog add _io_stall_write_ms bigint
alter table vfsLog add _io_stall bigint

select * from dba..vfsLog order by dbName, file_id, logdatetime desc

-- Yes, you did drink enough coffee, you are right ! This won't work !
select 
	p.logdatetime, 
	c.*
from vfsLog c
join (
	select 
		 max(logdatetime) logdatetime,
		 dbName,
		 file_id
	from vfsLog
	group by 
		 dbName
		,file_id) p on  p.logdatetime > c.logdatetime
				    and p.dbName = c.dbName
					and p.file_id = c.file_id
	order by 
		 c.dbName
		,c.file_id
		,c.logdatetime desc

-- This will, do you know why ?
select 
	p.logdatetime, 
	c.*
from vfsLog c
cross apply (
	select 
		 max(logdatetime) logdatetime
	from vfsLog 
	where c.logdatetime > logdatetime
	and c.dbName = dbName 
	and c.file_id = file_id) p
order by c.dbName, c.file_id, c.logdatetime desc

-- Get the additional data so we can calculate the delta's. 
-- Skip where p.logdatetime is null because there is no previous record to calculate delta's from

select 
	p.logdatetime,
	 c.logdatetime
	,c.dbName
	,c.file_id 
	,c.num_of_reads - pd.num_of_reads
	,c.num_of_bytes_read - pd.num_of_bytes_read
	,c.io_stall_read_ms - pd.io_stall_read_ms
	,c.num_of_writes - pd.num_of_writes
	,c.num_of_bytes_written - pd.num_of_bytes_written
	,c.io_stall_write_ms - pd.io_stall_write_ms
	,c.io_stall - pd.io_stall
	,c.*, p.*, pd.*
from vfsLog c
cross apply (
	select 
		 max(logdatetime) logdatetime
	from vfsLog 
	where c.logdatetime > logdatetime
	and c.dbName = dbName 
	and c.file_id = file_id) p
join vfsLog pd on  pd.logdatetime = p.logdatetime 
			   and pd.dbName = c.dbName 
			   and pd.file_id = c.file_id
where p.logdatetime is not null
order by 
	 c.dbName
	,c.file_id
	,c.logdatetime desc

-- Update query (I'm using CTE so I can read / understand my own update statement later) 

;with delta
as
(
select 
	 c.logdatetime
	,c.dbName
	,c.file_id
	,(c.num_of_reads - pd.num_of_reads) num_of_reads
	,(c.num_of_bytes_read - pd.num_of_bytes_read) num_of_bytes_read
	,(c.io_stall_read_ms - pd.io_stall_read_ms) io_stall_read_ms
	,(c.num_of_writes - pd.num_of_writes) num_of_writes
	,(c.num_of_bytes_written - pd.num_of_bytes_written) num_of_bytes_written
	,(c.io_stall_write_ms - pd.io_stall_write_ms) io_stall_write_ms
	,(c.io_stall - pd.io_stall) io_stall
from vfsLog c
cross apply (
	select 
		 max(logdatetime) logdatetime
	from vfsLog 
	where c.logdatetime > logdatetime
	and c.dbName = dbName 
	and c.file_id = file_id) p
join vfsLog pd on  pd.logdatetime = p.logdatetime 
			   and pd.dbName = c.dbName 
			   and pd.file_id = c.file_id
where p.logdatetime is not null
)
	update vfsLog
	set	_num_of_reads			= d.num_of_reads
	,	_num_of_bytes_read		= d.num_of_bytes_read
	,	_io_stall_read_ms		= d.io_stall_read_ms
	,	_num_of_writes			= d.num_of_writes
	,	_num_of_bytes_written	= d.num_of_bytes_written
	,	_io_stall_write_ms		= d.io_stall_write_ms
	,	_io_stall				= d.io_stall
	from delta d
	where vfsLog.logdatetime = d.logdatetime
	and	  vfsLog.dbName = d.dbName
	and	  vfsLog.file_id = d.file_id
		
		
-- One more thing left to do !		
select * from vfsLog order by dbName, file_id, logdatetime desc
delete from vfsLog where _num_of_reads is null

-- vfs_demo1 is real system...
select * from vfs_demo1

-- old fashioned way, usable in Powerpivot

create view vw_vfs_demo1
as
select 
	case datepart(month, logdatetime)
	when 1 then '01 - January'
	when 2 then '02 - February'
	when 3 then '03 - March'
	when 4 then '04 - April'
	when 5 then '05 - May'
	when 6 then '06 - June'
	when 7 then '07 - July'
	when 8 then '08 - August'
	when 9 then '09 - September'
	when 10 then '10 - October'
	when 11 then '11 - November'
	when 12 then '12 - December'
	end month
   ,datepart(day, logdatetime) day
   ,datepart(hour, logdatetime) hour
   ,datepart(minute, logdatetime) minute
   ,case when _num_of_reads > 0 then (_io_stall_read_ms / num_of_reads) else 0 end read_latency_ms
   ,case when _num_of_writes > 0 then (_io_stall_write_ms / num_of_writes) else 0 end write_latency_ms
   ,(_num_of_bytes_read / 1024 /1024) _num_of_mb_read
   ,(_num_of_bytes_written / 1024 /1024) _num_of_mb_written
   ,*
from vfs_demo1

select * from vw_vfs_demo1
-- add date & time dimension
-- using Itzik Ben Gan's GetNums function 
CREATE FUNCTION [dbo].[GetNums](@n AS BIGINT) RETURNS TABLE
AS
RETURN
  WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
  SELECT TOP (@n) n FROM Nums ORDER BY n;

select * from dbo.getnums(10)

-- Date dimension
create table dimDate
(
	DateKey datetime not null, 
	year int not null,
	month varchar(12) not null,
	day int not null
)

declare @startdate datetime = '20000101'
declare @enddate datetime = '20501231'
declare @days int

declare @months table(MonthKey int, month varchar(12))

insert into @months(MonthKey, month) 
values	(1,  '01-January'), 
		(2,  '02-February'), 
		(3,  '03-March'), 
		(4,  '04-April'), 
		(5,  '05-May'), 
		(6,  '06-June'), 
		(7,  '07-July'), 
		(8,  '08-August'), 
		(9,  '09-September'), 
		(10, '10-October'), 
		(11, '11-November'), 
		(12, '12-December')

set @days = datediff(day, @startdate, @enddate)

;with dates
as
(
select 
	 dateadd(day, n-1, @startdate) DateKey
from getnums(@days)  
)
  insert into dimDate(DateKey, year, month, day)
  select 
	 d.DateKey
	,year(d.DateKey) 
	,m.month
	,day(d.DateKey) 
  from dates d
  join @months m on m.MonthKey = month(d.DateKey)

-- Time dimension
create table dimTime
(
	TimeKey datetime not null,
	hour int not null,
	minute int not null,
)

;with times
as
(
select 
	 dateadd(minute, n-1, '20000101') TimeKey
from getnums(24*60)  
)
  insert into dimTime(TimeKey, hour, minute)
  select 
	 t.TimeKey
	,datepart(hour, t.TimeKey)
	,datepart(minute, t.TimeKey)	 
  from times t


-- change the view so it uses the date & time dimension
alter view vw_vfs_demo1
as
select 
	cast(convert(char(8), logdatetime, 112) as datetime) DateKey
   ,cast('20000101 ' + convert(char(8), logdatetime, 8) as datetime) TimeKey
   ,case when _num_of_reads > 0 then (_io_stall_read_ms / num_of_reads) else 0 end read_latency_ms
   ,case when _num_of_writes > 0 then (_io_stall_write_ms / num_of_writes) else 0 end write_latency_ms
   ,(_num_of_bytes_read / 1024 /1024) _num_of_mb_read
   ,(_num_of_bytes_written / 1024 /1024) _num_of_mb_written
   ,* -- Yes I know, don't use * ever !
from vfs_demo1