Grumpy Old DBA

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

CREATE TABLE [dbo].[FileStats] (
	[NumKey] [int] IDENTITY (1, 1) NOT NULL ,
	[TheDate] [datetime] NULL ,
	[DbID] [tinyint] NULL ,
	[FileID] [tinyint] NULL ,
	[StampTime] [bigint] NULL ,
	[Reads] [bigint] NULL ,
	[Writes] [bigint] NULL ,
	[BytesRead] [bigint] NULL ,
	[BytesWrite] [bigint] NULL ,
	[ioStall] [bigint] NULL ,
	[DbName] AS (db_name([DbID])) ,
	[HourWrites] AS ([dbo].[fn_FileStats_Write]([TheDate], [dbid], [fileid], [Writes])) ,
	[HourReads] AS ([dbo].[fn_FileStats_Read]([TheDate], [dbid], [fileid], [Reads])) ,
	[HourIo] AS ([dbo].[fn_FileStats_IOStall]([TheDate], [dbid], [fileid], [iostall])) ,
	[AvIoStall] AS ([dbo].[fn_FileStats_AvIoStall2]([TheDate], [dbid], [fileid], [Reads], [Writes], [ioStall])) 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[FileStats] ADD 
	CONSTRAINT [DF__FileStats__TheDate] DEFAULT (getdate()) FOR [TheDate],
	CONSTRAINT [uk_FileStats_Numkey] UNIQUE  NONCLUSTERED 
	(
		[NumKey]
	)  ON [PRIMARY] 
GO
--
--
--
Functions:-
--
--
CREATE function [dbo].[fn_FileStats_Write] (@p1 smalldatetime,@p2 tinyint,@p3 tinyint,@p4 bigint)
-- =============================================================  
--  Function: 		fn_FileStats_Write                          
--  Written by: 	Colin Leversuch-Roberts 
--			www.kelemconsulting.co.uk
--			(c) 6th Jan 2004                                
--                                                                
--  Purpose:            Populates a computed column in table FileStats                                 
--                                                                
--  System:		DBA maintenance
--
--  Input Paramters:   	@p1 smalldatetime, @p2 tinyint, @p3 tinyint, @p4 bigint
--			row timestamp, database id, file id, number of writes
-- 			
--  Returns : 		bigint                                      
--                                                                
--  Usage:              Computed column in table { select dbo.fn_FileStats_Write(@p1,@p2,@p3,@p4) }
--                                                                
--  Notes:		This function calculates the number of writes recorded against the file id
--			for the last hour. The writes, reads and i/o stall figures are cumlative hence
--			the calulations required to work out the figures
--                                                                
--  VERSION HISTORY
--  Version No		Date			Description
--  1			6 jan - 2004	Initial Release	                            
-- =============================================================  
returns bigint
as
begin
	declare @Return bigint,@Writes bigint
	select @Writes=Writes	
	from dbo.filestats with (nolock)
	where convert(char(15),theDate,113)+'00'=convert(char(15),dateadd(hh,-1,@p1),113)+'00'
	and dbid=@p2 and fileid=@p3
	IF @p4=@Writes
		set @return=0
	ELSE
		set @Return=abs(@p4-@Writes)
	--endif
return(@Return)
end
--
--
--
CREATE function [dbo].[fn_FileStats_Read] (@p1 smalldatetime,@p2 tinyint,@p3 tinyint,@p4 bigint)
-- =============================================================  
--  Function: 		fn_FileStats_Read                          
--  Written by: 	Colin Leversuch-Roberts 
--			www.kelemconsulting.co.uk
--			(c) 6th Jan 2004                                
--                                                                
--  Purpose:            Populates a computed column in table FileStats                                 
--                                                                
--  System:		DBA maintenance
--
--  Input Paramters:   	@p1 smalldatetime, @p2 tinyint, @p3 tinyint, @p4 bigint
--			row timestamp, database id, file id, number of writes
-- 			
--  Returns : 		bigint                                      
--                                                                
--  Usage:              Computed column in table { select dbo.fn_FileStats_Read(@p1,@p2,@p3,@p4) }
--                                                                
--  Notes:		This function calculates the number of reads recorded against the file id
--			for the last hour. The writes, reads and i/o stall figures are cumlative hence
--			the calulations required to work out the figures
--                                                                
--  VERSION HISTORY
--  Version No		Date			Description
--  1			6 jan - 2004	Initial Release	                            
-- =============================================================  
returns bigint
as
begin
	declare @Return bigint,@Reads bigint
	select @Reads=Reads	
	from dbo.filestats with (nolock)
	where convert(char(15),theDate,113)+'00'=convert(char(15),dateadd(hh,-1,@p1),113)+'00'
	and dbid=@p2 and fileid=@p3
-- have to cope with divide by zero problem -- rats! makes code less neat!!
	IF @p4=@Reads
		set @return=0
	ELSE
		set @Return=abs(@p4-@Reads)
	--endif
return(@Return)
end
--
--
--
CREATE function [dbo].[fn_FileStats_IOStall] (@p1 smalldatetime,@p2 tinyint,@p3 tinyint,@p4 bigint)
-- =============================================================  
--  Function: 		fn_FileStats_IOStall                          
--  Written by: 	Colin Leversuch-Roberts 
--			www.kelemconsulting.co.uk
--			(c) 6th Jan 2004                                
--                                                                
--  Purpose:            Populates a computed column in table FileStats                                 
--                                                                
--  System:		DBA maintenance
--
--  Input Paramters:   	@p1 smalldatetime, @p2 tinyint, @p3 tinyint, @p4 bigint
--			row timestamp, database id, file id, number of writes
-- 			
--  Returns : 		bigint                                      
--                                                                
--  Usage:              Computed column in table { select dbo.fn_FileStats_IOStall(@p1,@p2,@p3,@p4) }
--                                                                
--  Notes:		This function calculates the iostall in ms recorded against the file id
--			for the last hour. The writes, reads and i/o stall figures are cumlative hence
--			the calulations required to work out the figures
--                                                                
--  VERSION HISTORY
--  Version No		Date			Description
--  1			6 jan - 2004	Initial Release	                            
-- =============================================================  
returns bigint
as
begin
	declare @Return bigint,@IOStall bigint
	select @IOStall=IOStall	
	from dbo.filestats with (nolock)
	where convert(char(15),theDate,113)+'00'=convert(char(15),dateadd(hh,-1,@p1),113)+'00'
	and dbid=@p2 and fileid=@p3
-- have to cope with divide by zero problem -- rats! makes code less neat!!
	IF @p4=@IOStall
		set @return=0
	ELSE
		set @Return=abs(@p4-@IOStall)
	--endif
return(@Return)
end
--
--
--
CREATE function dbo.fn_FileStats_AvIoStall2 (@p1 datetime,@p2 tinyint, @p3 tinyint,@p4 bigint,@p5 bigint,@p6 bigint)
-- =============================================================  
--  Function: 		fn_FileStats_AvIoStall2                          
--  Written by: 	(c) Colin Leversuch-Roberts
--			www.kelemconsulting.co.uk                                 
--                                                                
--  Purpose:            to produce an average time in ms for the file io                                 
--                                                                
--  System:		DBA maintenance
--
--  Input Paramters:   	@p1 datetime, @p2 tinyint, @p3 tinyint,@p4 bigint, @p5 bigint, @p6 bigint
--			datetime , database id, file id, reads, writes, io stall
-- 			
--  Returns : 		decimal with 4 decimal points                                      
--                                                                
--  Usage:              select dbo.fn_FileStats_AvIoStall2(@p1,@p2,@p3,@p4,@p5,@p6)                                          
--                                                                
--  Notes:		function to calculate the average i/o stall per i/o for the last hour
-- 			Can't do a direct function on the calculated columns as you 
--			can't use computed columns in a computed column, so do this way.
--                                                                
--  VERSION HISTORY
--  Version No		Date			Description
--  1			25-aug-2005	Initial Release	                            
--                             
-- =============================================================  
returns decimal(9,4)
as
begin
declare @reads bigint,@writes bigint,@iostall bigint,@return decimal(9,4)
--
select @reads = dbo.fn_FileStats_Read(@p1,@p2,@p3,@p4)
select @writes = dbo.fn_FileStats_Write(@p1,@p2,@p3,@p5)
select @iostall = dbo.fn_FileStats_IOStall(@p1,@p2,@p3,@p6)
--
IF @reads=0 and @writes=0
	set @return = 0
ELSE
	set @return =  abs(((@iostall*1.0)/(@reads+@writes)))
--endif
--
return(@return)
end
--
--
PS .. still struggling with creating blog entries , please accept my apologies for poor formatting!!