Thursday 16 August 2018

Database Sizes and Growth Rates

I've been asked to write a few words about this thrillingly sexy topic.  Why?  Because I've been looking at some existing servers and finding quite a few issues which are Bill's Fault.

Microsoft thinks that a good initial size for a database is 1MB, and it should grow by 1MB every time it grows.   Logs should grow by 10%, which isn't utterly mad, but isn't a good idea either.   Since Microsoft sets these defaults, it's easy to set up a database.  Which is good.  But since they are lousy defaults, it's bad.

I suppose these figures are there because there is no right answer to the question  "How big should these settings be then, Mr Smartypants DBA?". 

So what's the problem?

Well, I recently found a database which had grown from 500MB to 11071MB in 1MB increments.  That's a LOT of growth events.  Each time it grows, it has to stop and think about it.  And if it has to grow ten times, that's a lot of hanging around, twiddling your thumbs, waiting for it to think. 

Hindsight is a wonderful thing.  I set that database to grow in 512MB increments.  Because I now know how big it is, and how big it was when it started. 

In a perfect world, you would carefully calculate how big the database is likely to be after a year or two of adding data, and size it to that size, plus maybe 10% for luck.  In the real world, you rely on SQL Server's capacity to grow when it needs to. 

So what's wrong with growing by a percentage? 

First answer:  Small database, growing by 10% each time, not a big issue.  Big database, growing by 10% each time, maybe 10GB every time it grows, is going to take a long time!
Second answer: Log files are composed of Virtual Log Files (VLFs) and ideally you don't want huge numbers of them, all different sizes - which they will be if they keep growing by 10% at a time. 

And if you forced me to set a default growth rate without knowing anything about the database, I would choose 16MB for the data, 8MB for the logs.  Better than Microsoft's default values, anyway.  That's what I put in the MODEL database. 

No comments:

Post a Comment