use admin

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetFileStats]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetFileStats]

GO

create procedure dbo.GetFileStats
/*******************************************************************************************************
*	dbo.GetFileStats.PRC
*	Creator:       Bill Wunder
*
*	Description:   create a trend history of ::fn_virtualfilestats output
*	Notes:         TimeStamp - Time at which the data was taken 
*                NumberReads - Number of reads issued on the file 
*                NumberWrites - Number of writes made on the file 
*                BytesRead - Number of bytes read issued on the file 
*                BytesWritten - Number of bytes written made on the file 
*                IoStallMS - Total amount of time, in milliseconds, that users 
*                            waited for the I/Os to complete on the file 
*
*
*	Usage:
	     EXECUTE admin.dbo.GetFileStats
*
*
*	Modifications:   
*          Developer Name     Date     Brief Description
*          ------------------ -------- ------------------------------------------------------------
*                    
********************************************************************************************************/
as

set nocount on

create table #file
  ( Name varchar(128) not null
  , FileId smallint not null
  , FileName nvarchar(260) 
  , FileGroup varchar(128)
  , Size nvarchar(18)
  , MaxSize nvarchar(18)  
  , growth nvarchar(18)
  , usage varchar(9))

if object_id('admin.dbo.FileStat','U') is null
  create table admin.dbo.FileStat
    (DbName varchar(128) not null
    , FileName varchar(128) not null
    , RecCreatedDt datetime not null
      constraint dft_FileStats__RecCreatedDt
      default getdate()
    , PathName nvarchar(260)
    , FileGroup varchar(128)
    , Size nvarchar(18)
    , usage varchar(9)
    , TimeStamp int
    , NumberReads bigint 
    , NumberWrites bigint 
    , BytesRead bigint 
    , BytesWritten bigint 
    , IoStallMS bigint
    , constraint pkc_FileStats__DbName__FileName__RecCreatedDt
      Primary key (DbName, FileName, RecCreatedDt))
  
exec sp_msforeachdb 'use ?
declare @dbid smallint
  , @fileid smallint
set @dbid = db_id()

truncate table #file

insert #file
  (name
  , fileid
  , filename
  , filegroup
  , size
  , maxsize
  , growth
  , usage)
exec sp_helpfile

select @fileid = min(FileId) 
from #file

while @Fileid is not null
  begin
 
   insert admin.dbo.FileStat
      (DbName
      , FileName
      , PathName
      , FileGroup
      , Size
      , usage
      , TimeStamp
      , NumberReads
      , NumberWrites
      , BytesRead
      , BytesWritten
      , IoStallMS)
    SELECT db_name(@dbid)
        , file_name(@fileid)
        , filename
        , filegroup
        , size
        , usage
        , TimeStamp
        , NumberReads
        , NumberWrites
        , BytesRead
        , BytesWritten
        , IoStallMS
    FROM :: fn_virtualfilestats(@dbid,@fileid) fn
    join #file f
    on fn.fileid = f.fileid
 
    select @fileid = min(FileId) 
    from #file
    where fileid > @fileid
  end'



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON 
GO

use [admin]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FileStatDailyTrend]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[FileStatDailyTrend]
GO


CREATE PROCEDURE [dbo].[FileStatDailyTrend] 
	@DbName varchar(128) = null   -- all
  , @FileGroup varchar(128) = null -- all
	, @BeginDate datetime = 0        -- all
  , @EndDate datetime = null       -- thru today
AS
/*******************************************************************************************************
*	dbo.FileStatDailyTrend.PRC 
*	Creator:       Bill Wunder
*
*	Description:   Produce report of captured files statistics
*	Notes:         can specify by database and/or daterange
*
*	Usage:
	     EXECUTE admin.dbo.FileStatDailyTrend 'admin'
*
*
*	Modifications:   
*          Developer Name     Date     Brief Description
*          ------------------ -------- ------------------------------------------------------------
*                    
********************************************************************************************************/
SET NOCOUNT ON

create table #filestat
  (DbName varchar(128)
  , FileName varchar(128)
  , RecCreatedDt datetime
  , FileGroup varchar(128)
  , usage varchar(9)
  , NumberReads bigint
  , NumberWrites bigint
  , BytesRead bigint
  , BytesWritten bigint
  , IoStallMS bigint
  , constraint pkc_#filestat__DbName__FileName__RecCreatedDt
    primary key (DbName, Filename, RecCreatedDt))

insert #filestat
  (DbName
  , FileName
  , RecCreatedDt
  , FileGroup
  , usage
  , NumberReads
  , NumberWrites
  , BytesRead
  , BytesWritten
  , IoStallMS)
select 
  DbName
  , FileName
  , convert(varchar(10),RecCreatedDt,102)
  , FileGroup
  , usage
  , min(NumberReads)
  , min(NumberWrites)
  , min(BytesRead)
  , min(BytesWritten)
  , min(IoStallMS)
from admin.dbo.FileStat
where BytesRead > BytesRead -- skip bad datapoint due to restart
and DbName like isnull(@DbName,'%')
and FileGroup like isnull(@FileGroup,'%')
and (RecCreatedDt >= @BeginDate) or @BeginDate is null
and (RecCreatedDt <= @EndDate) or @EndDate is null
group by DbName
  , FileName
  , convert(varchar(10),RecCreatedDt,102)
  , FileGroup
  , usage
order by DbName
  , FileName
  , convert(varchar(10),RecCreatedDt,102)
  , FileGroup
  , usage

select f2.DbName
  , f2.FileName
  , f2.FileGroup
  , f2.usage  
  , (f2.NumberReads - f1.NumberReads)/datediff(minute,f1.RecCreatedDt, f2.RecCreatedDt) [Reads/Min] 
  , (f2.NumberWrites - f1.NumberWrites)/datediff(minute,f1.RecCreatedDt, f2.RecCreatedDt) [Writes/Min]  
  , (f2.BytesRead - f1.BytesRead)/datediff(minute,f1.RecCreatedDt, f2.RecCreatedDt) [Bytes Read/Min] 
  , (f2.BytesWritten - f1.BytesWritten)/datediff(minute,f1.RecCreatedDt, f2.RecCreatedDt) [Bytes Written/Min]  
  , (f2.IoStallMS - f1.IoStallMS) * 1000 * 60/datediff(minute,f1.RecCreatedDt, f2.RecCreatedDt) [% Wait IO] 
from #filestat f1
join #filestat f2
on f1.DbName = f2.DbName
and f1.FileName = F2.FileName
and convert(varchar(10),f1.RecCreatedDt,102) = convert(varchar(10),f2.RecCreatedDt - 1,102)

RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON 
GO