Friday, December 20, 2013

SQL Server database capacity planning notes

It is best not to rely on auto-growth of the database because it negatively affects the performance and also causes database files to become fragmented because during auto-growth large chunks of disk space are being allocated.

One can set an auto-growth just as a way to prevent any issues but still monitor database growth proactively. Also it is best to set auto-growth percentage to a reasonable number in order to keep the database from using more space than it needs.

The best time to grow a database is during offline maintenance window. Allocate new space to the database files (mdf and ldf), close the dtaabase and defragment the filesystem on which database resides.

Rebuilding indexes also helps to combat fragmentation by deleting an old index and building a fresh one, leaving space for new data.

No comments:

Post a Comment