Creating Baselines for P & O part 2
The most important part of performance tuning is to be able to establish a baseline. Without a baseline and on-going statistics and trending information you just cannot make informed judgements concerning performance degredation or improvement or on future growth.
I have three standard sets of data I collect as a minimum, here is the second.
This concerns database growth, the cause of many a heated word I suspect when databases are volatile! It’s important to be able to see how databases are growing and to be able to predict growth for future capacity, this seems even more critical with a wider deployment of SANs where the aim is usually to achieve 85% or greater capacity utilisation. ( The whole question of SAN storage is another matter however )
I have a routine which populates a table once a day, the table calculates %age growth and I generate a report for easy viewing. The table is available for historical reporting. Like the filestats collection I make use of functions to do the maths for me, these are embedded in the table as calculated columns. There is also a stored procedure which must be placed within master as a system object.
Step 1 .. create the functions and table in your DBA database, called ServerAdmin here.
Step2 .. create the procedure sp_DatabaseSpace in the master database, mark this as a system procedure.
Step3 .. create a scheduled job to run the script shown below.
I have a separate routine which generates a report which I’m not covering here.
CREATE function dbo.fn_WeekEight (@p1 smalldatetime,@p2 numeric(10,2),@p3 sysname)
-- =============================================================
-- Function: fn_WeekEight
-- Written by: Colin Leversuch-Roberts
-- www.kelemconsulting.co.uk
-- (c) 6th Jan 2004
--
-- Purpose: Populates a computed column in table DatabaseGrowth
--
-- System: DBA maintenance
--
-- Input Paramters: @p1 smalldatetime, @p2 numeric, @p3 sysname
--
-- Returns : numeric
--
-- Usage: Computed column in table { select dbo.fn_WeekEight(@p1,@p2,@p3) }
--
-- Notes: Will automatically generate a %age change in database size based upon an 8 week period
-- Using functions in computed table columns allows results by a simple select
--
--
-- VERSION HISTORY
-- Version No Date Description
-- 1 6 jan - 2004 Initial Release
-- 2 14 jan - 2004 divide by zero error when database size unchanged clr
-- =============================================================
returns numeric(10,2)
as
begin
declare @Return numeric(10,2),@DBSize numeric(10,2)
select @DBSize=sizemb
from dbo.DatabaseGrowth with (nolock)
where convert(varchar,TheDate,112)=convert(varchar,dateadd(week,-8,@p1),112)
and DatabaseName=@p3
-- have to cope with divide by zero problem -- rats! makes code less neat!!
IF @p2=@DBSize
set @return=0
ELSE
set @Return=100.00/(@DBSize/(@p2-@DBSize))
--endif
return(@Return)
end
GO
CREATE function dbo.fn_WeekFour (@p1 smalldatetime,@p2 numeric(10,2),@p3 sysname)
-- =============================================================
-- Function: fn_WeekFour
-- Written by: Colin Leversuch-Roberts
-- www.kelemconsulting.co.uk
-- (c) 6th Jan 2004
--
-- Purpose: Populates a computed column in table DatabaseGrowth
--
-- System: DBA maintenance
--
-- Input Paramters: @p1 smalldatetime, @p2 numeric, @p3 sysname
--
-- Returns : numeric
--
-- Usage: Computed column in table { select dbo.fn_WeekFour(@p1,@p2,@p3) }
--
-- Notes: Will automatically generate a %age change in database size based upon an 4 week period
-- Using functions in computed table columns allows results by a simple select
--
--
-- VERSION HISTORY
-- Version No Date Description
-- 1 6 jan - 2004 Initial Release
-- 2 14 jan - 2004 divide by zero error when database size unchanged clr
-- =============================================================
returns numeric(10,2)
as
begin
declare @Return numeric(10,2),@DBSize numeric(10,2)
select @DBSize=sizemb
from dbo.DatabaseGrowth with (nolock)
where convert(varchar,TheDate,112)=convert(varchar,dateadd(week,-4,@p1),112)
and DatabaseName=@p3
-- have to cope with divide by zero problem -- rats! makes code less neat!!
IF @p2=@DBSize
set @return=0
ELSE
set @Return=100.00/(@DBSize/(@p2-@DBSize))
--endif
return(@Return)
end
GO
CREATE function dbo.fn_WeekOne (@p1 smalldatetime,@p2 numeric(10,2),@p3 sysname)
-- =============================================================
-- Function: fn_WeekOne
-- Written by: Colin Leversuch-Roberts
-- www.kelemconsulting.co.uk
-- (c) 6th Jan 2004
--
-- Purpose: Populates a computed column in table DatabaseGrowth
--
-- System: DBA maintenance
--
-- Input Paramters: @p1 smalldatetime, @p2 numeric, @p3 sysname
--
-- Returns : numeric
--
-- Usage: Computed column in table { select dbo.fn_WeekOne(@p1,@p2,@p3) }
--
-- Notes: Will automatically generate a %age change in database size based upon a 1 week period
-- Using functions in computed table columns allows results by a simple select
--
--
-- VERSION HISTORY
-- Version No Date Description
-- 1 6 jan - 2004 Initial Release
-- 2 14 jan - 2004 divide by zero error when database size unchanged clr
-- =============================================================
returns numeric(10,2)
as
begin
declare @Return numeric(10,2),@DBSize numeric(10,2)
select @DBSize=sizemb
from dbo.DatabaseGrowth with (nolock)
where convert(varchar,TheDate,112)=convert(varchar,dateadd(week,-1,@p1),112)
and DatabaseName=@p3
-- have to cope with divide by zero problem -- rats! makes code less neat!!
IF @p2=@DBSize
set @return=0
ELSE
set @Return=100.00/(@DBSize/(@p2-@DBSize))
--endif
return(@Return)
end
GO
CREATE function dbo.fn_WeekTwo (@p1 smalldatetime,@p2 numeric(10,2),@p3 sysname)
-- =============================================================
-- Function: fn_WeekTwo
-- Written by: Colin Leversuch-Roberts
-- www.kelemconsulting.co.uk
-- (c) 6th Jan 2004
--
-- Purpose: Populates a computed column in table DatabaseGrowth
--
-- System: DBA maintenance
--
-- Input Paramters: @p1 smalldatetime, @p2 numeric, @p3 sysname
--
-- Returns : numeric
--
-- Usage: Computed column in table { select dbo.fn_WeekTwo(@p1,@p2,@p3) }
--
-- Notes: Will automatically generate a %age change in database size based upon an 2 week period
-- Using functions in computed table columns allows results by a simple select
--
--
-- VERSION HISTORY
-- Version No Date Description
-- 1 6 jan - 2004 Initial Release
-- 2 14 jan - 2004 divide by zero error when database size unchanged clr
-- =============================================================
returns numeric(10,2)
as
begin
declare @Return numeric(10,2),@DBSize numeric(10,2)
select @DBSize=sizemb
from dbo.DatabaseGrowth with (nolock)
where convert(varchar,TheDate,112)=convert(varchar,dateadd(week,-2,@p1),112)
and DatabaseName=@p3
-- have to cope with divide by zero problem -- rats! makes code less neat!!
IF @p2=@DBSize
set @return=0
ELSE
set @Return=100.00/(@DBSize/(@p2-@DBSize))
--endif
return(@Return)
end
GO
use ServerAdmin
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DatabaseGrowth]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DatabaseGrowth]
GO
CREATE TABLE [dbo].[DatabaseGrowth] (
[DatabaseName] [sysname] NOT NULL ,
[SizeMB] [numeric](10, 2) NOT NULL ,
[TheDate] [smalldatetime] NOT NULL default getdate(),
[WeekOne] AS ([dbo].[fn_WeekOne]([TheDate], [SizeMB], [DatabaseName])) ,
[WeekTwo] AS ([dbo].[fn_WeekTwo]([TheDate], [SizeMB], [DatabaseName])) ,
[WeekFour] AS ([dbo].[fn_WeekFour]([TheDate], [SizeMB], [DatabaseName])) ,
[WeekEight] AS ([dbo].[fn_WeekEight]([TheDate], [SizeMB], [DatabaseName]))
) ON [PRIMARY]
GO
use master
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_DatabaseSpace]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_DatabaseSpace]
GO
CREATE procedure dbo.sp_DatabaseSpace
-- ============================================================================
-- Stored Procedure: sp_DatabaseSpace
-- Written by: Colin Leversuch-Roberts
-- www.kelemconsulting.co.uk
-- (c) 18th November 2003
--
-- Purpose: List the space used and free for a database
-- ( similar to sp_spaceused but better!! )
--
-- System: has to live in master database
--
-- Input Paramaters none
--
-- Output Parameters: None
-- Return Status: None
--
-- Usage: Exec dbo.sp_DatabaseSpace
--
-- Calls: n/a
--
-- Notes: This procedure has to live in the master database
-- It should then be called from the database to be reported upon
-- However, after compilation this procedure must be made a system object
-- to allow it to act just like a system procedure
-- use sp_MS_marksystemobject 'sp_DatabaseSpace'
-- This procedure is intended to be run within a scripted web assistant task
-- using the following script:-
-- create table #table1(c1 char(35),c2 char(15),c3 char(15),c4 char(15),c5 char(15),c6 char(15))
-- insert into #table1 exec dbo.sp_msforeachdb @command1='use "?";exec sp_DatabaseSpace '
-- select * from #table1
--
-- VERSION HISTORY
-- Version No Date Description
-- 1 18th Nov 2003 Initial Release
--=======================================================================================
as
set nocount on
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
declare @size int
declare @used int
declare @logfree varchar(12)
-- log details held in sysperfinfo
select @size=cntr_value from master.dbo.sysperfinfo with (nolock)where instance_name=db_name() and counter_name='Log File(s) Size (KB)'
select @used=cntr_value from master.dbo.sysperfinfo with (nolock)where instance_name=db_name() and counter_name='Log File(s) Used Size (KB)'
select @logfree=convert(varchar,(@size-@used)/1024)+' MB'
--
select @dbsize = sum(convert(dec(15),size)) from dbo.sysfiles with (nolock)where (status & 64 = 0)
select @logsize = sum(convert(dec(15),size)) from dbo.sysfiles with (nolock)where (status & 64 <> 0)
select @bytesperpage = low from master.dbo.spt_values with (nolock) where number = 1 and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage