06 April,2011 by Jack Vamvas
A disadvantage of some modelling simulation applications , which use database servers ,is the undetermined database size at the point of CREATE DATABASE. Typically , a user will issue a CREATE DATABASE statement , and the default sizing values are applied. In SQL Server , the model database will act as the template for the sizing. The DBA can influence this sizing in two ways : 1)They can change the model configurations 2)They can resize the database after it’s created
In most situations , a DBA would consult with the user and on the basis of projected workload pre size the database.
The main benefits of pre sizing are:
a)Pre allocating disk space – disk usage is projected
b)When a database grows during a workload – at the point where the database grows – a performance hit is experienced – this will vary according to the current size of the database.
For example, if a database is currently 100 GB – and there is a request to grow the database by 10% then resources will be redirected to growing the database, to 110 GB .
Best practise in that situation is to change the 10% growth to a hard number , such as 500MB. Therefore regardless of the database size it will only expand in increments of 500 MB , not 10% of the current size . For example if the database is currently 100 GB – then 10 % is 10 GB. You get the idea.
The disadvantages are:
a)A process would need to be in place to pre size the database , a consultation with a user and DBA,
b)The user would need to forecast the database size
In general there are 3 options
a)Don’t change anything
b)Create a procedure to pre size the database
c)Change the default setting of the database growth rate from 10% to a definite size . This could be in conjunction with b).