-- Stored Procedure: dbo.DbHistoricalTrends
-- Bill Wunder

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


CREATE PROCEDURE [dbo].[DbHistoricalTrends] 
 @DbName varchar(30) = '',
 @DaysToShow int = 60
   
AS
/*******************************************************************************************************
* admin.dbo.DbHistoricalTrends 
* Creator: Bill Wunder
*
* Outline: Query the stored samples from master..sysperfinfo
*   to display the transaction rate for a database
*   
* usage:
    EXECUTE admin.dbo.DbHistoricalTrends customer
* Notes:
*   
* Modifications   
* name     date        brief description
* -------  ----------  ------------------------------------------------------------
* bw       09-17-2002  add server performance profile info from sp_monitor_log
* bw       04-10-2004  change performance profile to use bigint based table  
********************************************************************************************************/
-- declare variables
declare @StartDate datetime

-- set session
SET NOCOUNT ON

set @StartDate = getdate() - @DaysToShow

-- create temp tables

-- body of stored procedure
print '(see below for space utilization trend and server performance profile)'
print ''
print 'Average Daily Transaction Rate (Transactions/Second) by database'
print '  based on calculations for each half hour of the day' 
print ''

select p1.dbName,
       Datepart(yyyy, p1.SampleDate) as year, 
 Datepart(mm, p1.SampleDate) as month,
 cast(avg(p1.MinTxnPerSec) as numeric(12,2)) as [AvgDailyMinTxn/Sec], 
 cast(avg(p1.MaxTxnPerSec) as numeric(12,2)) as [AvgDailyMaxTxn/Sec], 
 avg(cast(cast(p1.TransActivityDetected as numeric(12,2))/cast(p2.TransActivityDetected as numeric(12,2)) * 100 as int)) as [Avg%Active/Day]
from admin.dbo.dbperfhistory p1
inner join admin.dbo.dbperfhistory p2
on p1.SampleDate = p2.SampleDate
where p1.dbName like @dbName + '%'
and Datepart(d, p1.SampleDate) = 1
and p2.DbName = '_Total'
and p1.SampleDate > @StartDate
group by p1.dbName,Datepart(yyyy, p1.SampleDate),Datepart(mm, p1.SampleDate)
order by case p1.DBName when '_Total' then 1 else 0 end, p1.dbname, Datepart(yyyy, p1.SampleDate) desc,Datepart(mm, p1.SampleDate) desc


print ''
print ''
print 'Performance profile (perfmon and sp_monitor details)'
print ''
print 'Recent server level perfmon performance details'
print ''

select convert(varchar(11),h1.SampleDate,121) [Date]
 , (h1.BatchRequestPerSec - h2.BatchRequestPerSec)/(24*60*60) [Batch Requests/Second]
 , (h1.TableScans - h2.Tablescans)/(24*60*60) [Table Scans/Second]  
 , h1.DataCacheHitRatio/h1.DataCacheHitRatioBase [Data Cache Hit Ratio]
 , h1.ProcCacheHitRatio/h1.ProcCacheHitRatioBase [Proc Cache Hit Ratio]
from admin.dbo.srvperfhistory h1
join admin.dbo.srvperfhistory h2
on h1.SampleDate - 1 = h2.SampleDate
where h1.SampleDate > @StartDate
order by h1.SampleDate desc


print ''
print 'See SQL Server Books On Line documentation of system stored procedure sp_monitor for a full 
description and explaination of all columns in this query.'
print ''
print ''

exec admin.dbo.SPTMonitorHistoryReport @StartDate

print ''
print ''
print 'Space utilization'
print ''

select DbName, cast(max(SampleDate) as varchar(11)) as [Latest Date at this size], 
 DataFileSize_KB,
        LogFileSize_KB
from admin.dbo.DbPerfHistory
where DbName like @DbName + '%'
and SampleDate > @StartDate
group by DbName, DataFileSize_KB, LogFileSize_KB
order by case DBName when '_Total' then 1 else 0 end, DbName, max(SampleDate) desc
  

RETURN


GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO