asked
Capacity planning for creating new SQL 2012 databases
I have just been placed in a DBA role and have been asked by a user to create a new client database. I know some people just blindly create an initial size and leave it at that for each new database instance. I would like to use some initial information and size the database the most efficient way possible. Info is below. Can you help me with what size the Database and Log files should be with autogrowth option as well? Thanks 1. Quarterly – we’ll receive approx 35 million records with a record length of 1540 and 415 fields. 2. Will be receiving a suppression file – should be a small record length but do not know the # of records – I would assume it will be relatively small as far as # records. 3. We expect to get a Customer file monthly that will contain approx 4.2 million records – don’t have the record length yet, but don’t expect it to be overly long. 4. We should get one TDA Suppression file per month that should contain approx 150,000 records. Record length should be small – mostly n/a. 5. Campaign processing will occur quarterly. 6. We’ll create tables to output but it will only be approx 5 million recs per qtr. 7. We’ll keep a promotion history table (don’t know the approx # of records to be mailed per qtr yet).
To accurately work out how much space you need for a given number of records, you need to know the space each row takes up - so in your point one you specify a record length of 1540, but to work out the total space you would need to know the byte size (e.g. is it varchar fields? char? nchar?) these are all stored differently. However, it may be possible to estimate a practicable size from an existing database if you have something which looks similar. Disk storage is relatively cheap at the moment, and it seems like your level of uncertainty is going to be quite high from the above. Consider also how to split the space - this might depend on factors such as physical infrastructure and whether your inserts/processing is done as one transaction. Try to size big enough that you don't need to autogrow (it's a safety net) - and manually review the sizes periodically.
17 People are following this question.
