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].[GetPerfHistory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetPerfHistory]
GO

Create proc dbo.GetPerfHistory
as
/*******************************************************************************************************
*	admin.dbo.GetPerfHistory 
*	Creator: Bill W	
*	Outline: query the system table sysperfinfo daily to capture: 
*			DB file size
*			Log File size
*			Log File size used
* 			Table Scans
*			Data Cache Hit Ratio
*			Procedure Cache Hit Ratio	
*		query sysperfinfo throughout the day to capture a daily distribution: 
*			transaction/sec 	
*			
*	How it Works:	The procedure is run from the SQL Agent scheduler on a regular interval.   
*			
*	usage:
		EXECUTE admin.dbo.GetPerfHistory 

*	Notes:		Query this table to determine the avg, max and min transactions per second by Date
*			use daily deltas to determine change trends
*	Modifications   
*
********************************************************************************************************/
declare @today datetime, 
	@CurDt datetime

declare @t table (DbName varchar(30) primary key,
		  TxnPerSec numeric(16,6))	

set nocount on

-- get a timestamp for now and midnight
set @CurDt = getDate()
set @today = cast(@CurDt as varchar(11))

-- don't do anything if wrong database
if db_name() <> 'admin'
	begin
		raiserror ('This utility may only be installed in the admin database!',16,1)
		return -1
	end

-- init the tables if first time on this server
if (object_id('dbo.DbPerfHistory','U') is null)
	create table dbo.DbPerfHistory (DbName varchar(30) NOT NULL, 
				SampleDate datetime NOT NULL,
				DataFileSize_KB int NULL,
				LogFileSize_KB int NULL,
				LogFileSizeUsed_KB int NULL,
				FirstTranRateSample int NULL,
				LastTranRateSample int NULL, 
				CurTxnPerSec numeric(16,6) NULL,
				MinTxnPerSec numeric(16,6) NULL,  
				MinTxnDt datetime NULL,  
				MaxTxnPerSec numeric(16,6) NULL, 
				MaxTxnDt datetime NULL, 
				TransActivityDetected int NULL,
				FirstSampleDt datetime NULL,	
				LastSampleDt datetime NULL,
				constraint pk_DbPerfHistory__SampleDate__DbName primary key
				(SampleDate, DbName))
			
		
if (object_id('dbo.SrvPerfHistory','U') is null)
	create table dbo.SrvPerfHistory (SampleDate datetime NOT NULL,
				TableScans numeric(16,6) NULL,
                                BatchRequestPerSec numeric(16,6) NULL,
				DataCacheHitRatio numeric(12,2) NULL,
				DataCacheHitRatioBase numeric(12,2) NULL,
				ProcCacheHitRatio numeric(12,2) NULL,
				ProcCacheHitRatioBase numeric(12,2) NULL,
				constraint pk_SrvPerfHistory__SampleDate primary key
				(SampleDate))

-- can add other values that require intraday range tracking here
insert @t (DbName, TxnPerSec)
select 	substring(instance_name,1,30), 
		cast(cntr_value as numeric(16,6))
	from master.dbo.sysperfinfo
	where counter_name= 'Transactions/sec'


-- if no rows for today, init a row
insert admin.dbo.DbPerfHistory (DbName, 
			FirstTranRateSample,
			FirstSampleDt,
			LastTranRateSample,
			LastSampleDt,
			CurTxnPerSec,
			MinTxnPerSec,
			MinTxnDt,
			MaxTxnPerSec,
			MaxTxnDt,
			TransActivityDetected,
			SampleDate)
select DbName,
	t.txnPerSec,
	@CurDt,
	t.txnPerSec,
	@CurDt,
	999999999.999999,
	999999999.999999,
	@CurDt,
	0,
	@CurDt,
	0,
	@today
from @t t
where not exists(select 1 from admin.dbo.DbPerfHistory
			where dbName = t.DbName
			and SampleDate = @today)

if not exists (select 1 from admin.dbo.SrvPerfHistory
			where SampleDate = @today)
	begin
		-- if buffer cache hit ratio getting smaller and scans increasing, new queries not using indexes
		insert admin.dbo.SrvPerfHistory (SampleDate, TableScans) 
		select @today, p.cntr_value 
		from master.dbo.sysperfinfo p
		where p.object_name = 'SQLServer:Access Methods'
		and p.counter_name = 'Full Scans/sec'

		-- as falls away from 100% more memory can help
		--select cast(cast(p2.cntr_value as numeric(14,4))/cast(p1.cntr_value as numeric(14,4)) as numeric(6,4)) * 100
		-- calc daily delta to plot over time (elim days where counter rolls over or restarts)
		update h
			set DataCacheHitRatio = p1.cntr_value,
		 	    DataCacheHitRatioBase = p2.cntr_value
		from master.dbo.sysperfinfo p1
		inner join master.dbo.sysperfinfo p2
		on p1.object_name = p2.object_name
		and p1.instance_name = p2.instance_name
		cross join admin.dbo.SrvPerfHistory h
		where p1.object_name = 'SQLServer:Buffer Manager'
		and p1.counter_name = 'Buffer cache hit ratio'
		and p2.counter_name = 'Buffer cache hit ratio base'  
		and h.SampleDate = @today   	   	-- work on today's record

		-- lower value may mean high dynamic sql use
		--select cast(cast(p1.cntr_value as numeric(14,4))/cast(p2.cntr_value as numeric(14,4)) as numeric(6,4)) * 100
		-- calc daily delta to plot over time (elim days where counter rolls over or restarts)
		update h
			set ProcCacheHitRatio = p1.cntr_value,
		 	    ProcCacheHitRatioBase = p2.cntr_value
		from master.dbo.sysperfinfo p1
		inner join master.dbo.sysperfinfo p2
		on p1.object_name = p2.object_name
		and p1.instance_name = p2.instance_name
		cross join admin.dbo.SrvPerfHistory h
		where p1.object_name = 'SQLServer:Cache Manager'
		and p1.instance_name = 'Prepared Sql Plans' 
		and p1.counter_name = 'Cache Hit Ratio'
		and p2.counter_name = 'Cache Hit Ratio Base'  
		and h.SampleDate = @today   	   	-- work on today's record

		-- use batch request per second as a general indicater of read/write activity over time 
		-- calc daily delta to plot over time (elim days where counter rolls over or restarts)
		update h 
			set BatchRequestPerSec = p.cntr_value 
		from master.dbo.sysperfinfo p
		cross join admin.dbo.SrvPerfHistory h 
		where p.object_name = 'SQLServer:SQL Statistics'
		and p.counter_name = 'Batch Requests/sec'
		and h.SampleDate > = @today   	   	-- work on today's record
	end

if exists (select 1 from admin.dbo.DbPerfHistory
			where SampleDate = @today
			and FirstSampleDt = @CurDt)
	begin
		update h 
			set DataFileSize_KB = p.cntr_value 
		from master.dbo.sysperfinfo p
		inner join admin.dbo.DbPerfHistory h
		on p.instance_name = h.DbName
		where p.counter_name = 'Data File(s) Size (KB)'
		and h.SampleDate = @today   	   	-- work on today's record
		and h.FirstSampleDt = @CurDt 		-- only if inserted this time

		update h
			set LogFileSize_KB = p.cntr_value 
		from master.dbo.sysperfinfo p
		inner join admin.dbo.DbPerfHistory h
		on p.instance_name = h.DbName
		where p.counter_name = 'Log File(s) Size (KB)'
		and h.SampleDate = @today   	   	-- work on today's record
		and h.FirstSampleDt = @CurDt 		-- only if inserted this time

		update h 
			set LogFileSizeUsed_KB = p.cntr_value 
		from master.dbo.sysperfinfo p
		inner join admin.dbo.DbPerfHistory h
		on p.instance_name = h.DbName
		where p.counter_name = 'Log File(s) Used Size (KB)'
		and h.SampleDate = @today   	   	-- work on today's record
		and h.FirstSampleDt = @CurDt 		-- only if inserted this time

	end
else
	-- if already rows for today, do tran rate aggregations
	-- use 0 as LastTranRateSample if counter has been reset
	-- used values returned in perfmon to determine calculations
	update d
	set LastTranRateSample = t.TxnPerSec,
	    LastSampleDt = @CurDt,	
	    CurTxnPerSec = case when ((t.TxnPerSec - d.LastTranRateSample) >= 0)
				then (t.TxnPerSec - d.LastTranRateSample) / DateDiff(s, d.LastSampleDt, @CurDt)
				else (t.TxnPerSec) / DateDiff(s, d.LastSampleDt, @CurDt) 
			end, -- handle a counter reset	
	    MinTxnPerSec = case when ((t.TxnPerSec - d.LastTranRateSample) / DateDiff(s,d.LastSampleDt,@CurDt) < d.MinTxnPerSec)  
				then case when ((t.TxnPerSec - d.LastTranRateSample) >= 0)
						then (t.TxnPerSec - d.LastTranRateSample) / DateDiff(s,d.LastSampleDt,@CurDt) 
						else (t.TxnPerSec) / DateDiff(s,d.LastSampleDt,@CurDt) 
				     end
				else d.MinTxnPerSec 
			   end,
	    MinTxnDt = case when ((t.TxnPerSec - d.LastTranRateSample) / DateDiff(s,d.LastSampleDt,@CurDt) < d.MinTxnPerSec) 
				then @CurDt 
				else d.MinTxnDt 
			       end,	
	    MaxTxnPerSec = case when ((t.TxnPerSec - d.LastTranRateSample) / DateDiff(s,d.LastSampleDt,@CurDt) > d.MaxTxnPerSec) 
				then case when ((t.TxnPerSec - d.LastTranRateSample) >= 0)
						then (t.TxnPerSec - d.LastTranRateSample) / DateDiff(s,d.LastSampleDt,@CurDt) 
						else (t.TxnPerSec) / DateDiff(s,d.LastSampleDt,@CurDt) 
				     end
				else d.MaxTxnPerSec 
			   end,
	    MaxTxnDt = case when ((t.TxnPerSec - d.LastTranRateSample)/DateDiff(s,d.LastSampleDt,@CurDt) > d.MaxTxnPerSec) 
				then @CurDt 
				else d.MaxTxnDt 
		       end,		
	    TransActivityDetected = case when (t.TxnPerSec - d.LastTranRateSample) = 0
					then TransActivityDetected
					else TransActivityDetected  + 1
					end
	from @t t
	inner join admin.dbo.DbPerfHistory d 
	on t.DbName = d.DbName
	where SampleDate = @today   		   	-- work on today's record
	and (   LastSampleDt < @CurDt) 		-- unless inserted this time

return


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].[DbActivityTrend]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DbActivityTrend]
GO


CREATE PROCEDURE [dbo].[DbActivityTrend] 
	@DbName varchar(30) = ''
	  
AS
/*******************************************************************************************************
*	admin.dbo.DbActivityTrend 
*	Creator:	Bill Wunder
*	Outline:	Query the stored samples from master..sysperfinfo
*			to display the transaction rate history for a database
*			
*			
*	usage:
		EXECUTE admin.dbo.DbActivityTrend customer
*			
*	Modifications   
*         developer name     date     brief description
*         ------------------ -------- ------------------------------------------------------------
*
********************************************************************************************************/
-- declare variables


-- set session
SET NOCOUNT ON


-- create temp tables


-- body of stored procedure
select p1.dbName,
	cast((cast(p1.LastTranRateSample as numeric(12,2)) - cast(p1.FirstTranRateSample as numeric(12,2)))/cast(datediff(s, p1.FirstSampleDt, p1.LastSampleDt) as numeric(12,2)) as numeric(12,2)) as avgTxnPerSec, 
	cast(p1.MinTxnPerSec as numeric(12,2)) as MinTxnPerSec, 
	cast(p1.MaxTxnPerSec as numeric(12,2)) as MaxTxnPerSec, 
	cast(cast(p1.TransActivityDetected as numeric(12,2))/cast(p2.TransActivityDetected as numeric(12,2)) * 100 as int) as PctActive,
	convert(varchar(21),p1.SampleDate,102) as [Date],
	datepart(dw, p1.SampleDate) as [Day of Week]
from admin.dbo.dbperfhistory p1
inner join admin.dbo.dbperfhistory p2
on p1.SampleDate = p2.SampleDate
where p1.DbName like @dbName + '%' 
and p2.DbName = '_Total'
order by p1.dbname, p1.sampledate



RETURN



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


use admin
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[DbLogSizeTrend] 
	@DbName sysname = null
	  
AS
/*******************************************************************************************************
*	admin.dbo.dbLogSizeTrend 
*	Creator:	Bill Wunder
*	Outline:	show log space over time

*	usage:
		EXECUTE admin.dbo.dbLogSizeTrend 
*			
*	Modifications   
*         developer name     date     brief description
*         ------------------ -------- ------------------------------------------------------------
*
********************************************************************************************************/
-- declare variables


-- create temp tables

-- set session
SET NOCOUNT ON

-- body of stored procedure

if @DbName is null -- all
	select DbName, cast(max(SampleDate) as varchar(11)) as [Latest Date at this size], LogFileSize_KB, LogFileSizeUsed_KB, datepart(dw, SampleDate) as [weekday]
	from admin.dbo.DbPerfHistory
	group by DbName, LogFileSize_KB, LogFileSizeUsed_KB, datepart(dw, SampleDate)
	order by DbName, max(SampleDate)
else -- only one db
	select Dbname, cast(max(SampleDate) as varchar(11)) as [Latest date at this size], LogFileSize_KB, LogFileSizeUsed_KB,  datepart(dw, SampleDate) as [weekday]
	from admin.dbo.DbPerfHistory
	where DbName = @DbName
	group by DbName, LogFileSize_KB , LogFileSizeUsed_KB, datepart(dw, SampleDate)
	order by max(SampleDate)

RETURN



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].[DbSizeTrend]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DbSizeTrend]
GO


CREATE PROCEDURE [dbo].[DbSizeTrend] 
	@DbName sysname = null
	  
AS
/*******************************************************************************************************
*	admin.dbo.dbSizeTrend 
*	Creator:	Bill Wunder
*	Outline:	show data space over time
*			does not include log space
*			
*			
*	usage:
		EXECUTE admin.dbo.dbSizeTrend 
*			
*	Modifications   
*         developer name     date     brief description
*         ------------------ -------- ------------------------------------------------------------
*
********************************************************************************************************/
-- declare variables


-- create temp tables

-- set session
SET NOCOUNT ON

-- body of stored procedure

if @DbName is null -- all
	select DbName, max(SampleDate) as [Latest Date at this size], DataFileSize_KB
	from admin.dbo.DbPerfHistory
	group by DbName, DataFileSize_KB 
	order by DbName, max(SampleDate)
else -- only one db
	select Dbname, max(SampleDate) as [Latest date at this size], DataFileSize_KB
	from admin.dbo.DbPerfHistory
	where DbName = @DbName
	group by DbName, DataFileSize_KB 
	order by max(SampleDate)

RETURN



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 


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].[GetSrvActivityTrend]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetSrvActivityTrend]
GO


CREATE PROCEDURE [dbo].[GetSrvActivityTrend] 
	@DbName sysname = null
	  
AS
/*******************************************************************************************************
*	admin.dbo.GetSrvActivityTrend 
*	Creator:	Bill Wunder
*	Outline:	show server activity over time

*	usage:
		EXECUTE admin.dbo.GetSrvActivityTrend 
*			
*	Modifications   
*         developer name     date     brief description
*         ------------------ -------- ------------------------------------------------------------
*
********************************************************************************************************/
-- declare variables


-- create temp tables

-- set session
SET NOCOUNT ON

-- body of stored procedure

select p1.SampleDate
	, (p1.TableScans - p2.TableScans)/(24*60*60) [TableScans/Sec]-- seconds in a day	
	, (p1.BatchRequestPerSec - p2.BatchRequestPerSec)/(24*60*60) [BatchRequest/Sec]-- seconds in a day
	, p1.DataCacheHitRatio/p1.DataCacheHitRatioBase [Data Cache Hit Ratio]
	, p1.ProcCacheHitRatio/p1.ProcCacheHitRatioBase [Procedure Cache Hit Ratio]
from admin.dbo.SrvPerfHistory p1
inner join admin.dbo.SrvPerfHistory p2
on p1.SampleDate = p2.SampleDate + 1
order by p1.SampleDate Desc

RETURN


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO