Sunday 29 October 2006

Bad Autogrowth

The default Autogrowth for a new database is 'By 1 MB, unrestricted growth'

Imagine this default is still set on a production server, and you import a 50MB file when the datafile is at capacity.
The result is that 50 occurences of 1MB autogrow operations occur.
This is costly in terms of performance.

Now imagine multiple databases, all growing at varying rates (now that sounds like EVERY server doesn't it?)
As each of these datafiles 'expand' they take more disk space, grabbing the next available area on the disk. In this way, fragmentation of database files occurs.
As time goes on, simple database operations will rely more heavily on the disk as the server fetches data from logical files that are physically fragmented i.e. split.

Morals of this story...

1) Manually set data file sizes, allowing for expansion.
2) Review Disk/File fragmentation and perform disk maintenance
3) Do 1) & 2) regularly (stick reminders in your outlook!)

No comments: