October 29, 2008 at 12:15 am
Hi Folks,
I was just wondering how people go about capacity planning for there databases.
I read that logging the output for sp_spaceused is a good way to do it as over a period of time you can see how big your dbs are getting everyday.
October 29, 2008 at 1:15 am
There are various ways to go about for capacity planning.
Once one of my ex-boss called me and said that he wanted to upgrade the database server and had finalised one but was not sure what size of HDD's to go in for. I thought it would be help him a lot if I gave him a growth rate for the last 3 yrs. So I looked up some system tables and found out tables like backupmediaset and backupmediafamily. These and a couple of more tables store every information there is about backups including the size of the data files at the time of backup. I used this information and came up with numbers which displayed the size of the data files at the time of backup and walla..... in no time everything was pasted in excel....a few clicks here and a pivot there and my boss had everything in front of him. The rest was like a walk in the park. we analysed the growth was somewhere between linear and exponential and planned the datbase size for the next 2 years.
In other cases where u are starting everything from scratch and dont have any data to work with... heres a starting point....
You fire a simple select top1 * from and view the execution plan. in the execution plan u get the size/row. note it down somewhere.
Then go up to the functional team and ask them what is he number of transactions that they expect over the next say 1 yr. Being a DBA u know how many tables are affected by 1 transaction. multiple the size/row of that table with the number of transactions expected and u have the size for that table after a year. repeat this for all tables and you should be good to go. Always mention that these numbers are based on estimations by the functional team (so u have your exit route in case the your estimations going wrong).
There are a few more tips. reach me if you have any doubts at meekee26 at yahoo dot com
October 29, 2008 at 6:07 pm
From my understanding transactions are dynamic and different transactions effect different table... so how can you know what table a future transaction can effect?
October 29, 2008 at 11:15 pm
lets say as a DBA I know a new customer addition transaction should affect the "customer master" table and the "customer detail" table, and for every master entry the functional team tell us there are 2 entries in the detail table. then for 1 million transactions, I multiply the row size for master into 1 million and for 1 million transactions, I multiply the row size for detail into 2 million.
now lets say the functional team expects 10 million sale transactions. again u have a master table and a detail table. again for every master entry u have 5 corresponding detail entries
do the same math again and u should be good.
October 30, 2008 at 7:28 am
Here's a script I use to monitor you databasesize
print '*** Creating objecten voor DBSize'
print ''
if not exists (select 'yes' from sysobjects where name = 'dbsize')
BEGIN
CREATE TABLE DBSize(
[Servername] sysname DEFAULT @@SERVERNAME
, [Servicename] sysname DEFAULT @@SERVICENAME
, [databaseid] int NOT NULL
, [databasesize] bigint null
, [logsize] bigint null
, [rows] bigint NULL
, [reserved] BIGINT NULL
, [data] bigint NULL
, [index_size] bigint NULL
, [unused] bigint NULL
, [timestamps] SMALLDATETIME NOT NULL DEFAULT GETDATE()
)
CREATE UNIQUE INDEX IDX__DBSIZE__01 ON dbsize(servername,servicename, databaseid, timestamps) WITH IGNORE_DUP_KEY
END
GO
if exists (select 'yes' from sysobjects where name = 'vw_dbsize')
DROP VIEW vw_DBSize
GO
CREATE VIEW vw_dbsize as
SELECT *
from dbsize
where timestamps = (select max(timestamps) from DBSize)
GO
if exists (select 'yes' from sysobjects where name = 'usp_collectDBsize')
DROP PROCEDURE usp_collectDBSize
GO
CREATE PROCEDURE USP_CollectDBSize @verbose bit = 0, @debug bit = 0 AS
SET NOCOUNT ON
BEGIN
DECLARE @SQLCmd nvarchar(2048)
if @verbose = 1 or @debug = 1
if LEFT(CAST(SERVERPROPERTY('productversion') AS CHAR),1) = '8'
print 'Using the MSSQL 2000 method'
else
print 'Using the MSSQL 2005 method'
if LEFT(CAST(SERVERPROPERTY('productversion') AS CHAR),1) = '8'
set @SQLCMD = N'
USE [?]
GO
DECLARE @rows bigint
DECLARE @reserved bigint
DECLARE @data bigint
DECLARE @index_size bigint
DECLARE @unused bigint
SELECT @dbsize = sum(convert(bigint, case when status & 64 = 0 then size else 0 end))
, @logsize = sum(convert(bigint, case when status & 64 <> 0 then size else 0 end))
FROM dbo.sysfiles
SELECT @rows = sum(rows) FROM sysindexes WHERE indid < 2
SELECT @reserved = sum(reserved) * 8 FROM sysindexes WHERE indid in (0, 1, 255)
SELECT @data = (SELECT sum(dpages) FROM sysindexes WHERE indid < 2 ) * 8 + (SELECT sum(used)FROM sysindexes WHERE indid = 255) * 8
SELECT @index_size = (SELECT sum(used) FROM sysindexes WHERE indid in (0, 1, 255)) * 8 - @data
SELECT @unused= @reserved - (SELECT sum(used) FROM sysindexes WHERE indid in (0, 1, 255)) * 8
INSERT INTO ' + DB_NAME() + '.dbo.DBSize([databaseid],[databasesize],[logsize],[rows],[reserved],[data],[index_size],[unused])
VALUES (DB_ID(),@dbsize, @logsize,@rows,@reserved,@data,@index_size,@unused)'
else
set @SQLCMD = N'
USE [?]
DECLARE @dbsize bigint
DECLARE @logsize bigint
DECLARE @rows bigint
DECLARE @reservedpages bigint
DECLARE @pages bigint
DECLARE @Usedpages bigint
DECLARE @index_size bigint
DECLARE @unused bigint
SELECT @dbsize = sum(convert(bigint, case when status & 64 = 0 then size else 0 end))
, @logsize = sum(convert(bigint, case when status & 64 <> 0 then size else 0 end))
FROM dbo.sysfiles
select @reservedpages = sum(a.total_pages) *8,
@usedpages = sum(a.used_pages) *8,
@pages = sum(
CASE
-- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size"
When it.internal_type IN (202,204) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
) *8
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
SELECT @rows = sum(rows) FROM sysindexes WHERE indid < 2
INSERT INTO ' + DB_NAME() + '.dbo.DBSize([databaseid],[databasesize],[logsize],[rows],[reserved],[data],[index_size],[unused])
VALUES (DB_ID(),@dbsize, @logsize,@rows,@reservedpages,@pages,@usedpages-@pages,@reservedpages-@usedpages)'
if @debug = 0
EXEC sp_msforeachdb @SQLCmd
else
print @SQLCmd
END
GO
Create this table/procedure in a "dba database" and schedule this procedure several times a day (I do this every 4 hour)
After some time, you can :
- predict database growth
- notify unexpected growth
- make impressive charts with these data 🙂
Wilfred
The best things in life are the simple things
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply