-- Stored Procedure: dbo.GetPerfHistory
-- 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].[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   
*         bw 8-14-2002 add Batch Requests/sec
*         bw 2-23-2003 add call to admin.dbo.ShrinkLogs
********************************************************************************************************/
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

  --If a log columns were maintained, do log file cleanup (2-23-2003)
  if @@rowcount > 0
   exec admin.dbo.ShrinkLogs

 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 ON
GO
SET ANSI_NULLS ON 
GO