VLDB and Zeroing out

Awhile ago I wrote a post SQL Server faster restores with instant file initialisation , which goes through the basics of instant file initialization  - the characteristics and how to set it up for your SQL Server databases.

Although the advice applies as a general rule, it can be tricky to incorporate with auto growth setting on VLDBs .

Transaction Logs on VLDBs . Before you implement anything in Production , test out in the Development environments. The last thing you need is a nasty surprise – while Production locks up and users complain.

For VLDB Transaction Logs , my general rule is to start with 8 GB, and set autogrowth at 8 GB. These are general guidelines.  The real key with implementing successfully is to predict the autogrowth – because that is a point where the zero-filling occurs and can have a severe performance impact

For VLDB Data files – they are instant initialized. Note: Only if instant initialization is enabled! These gives you loads more scope on autogrowth – meaning you can set much higher numbers.

Keep in mind if instant file initialization is not enabled – both data and transaction log files are zeroed out. In this situation – same rules apply for autogrowth levels – test in the lower environments.

Read More

Database autogrow and slow database recovery ... - SQL Server DBA

SQL Server faster restores with instant file initialisation

Storage Checklist FAQ - SQL Server DBA

SQL LOG FILE AUTOGROW performance ... - SQL Server DBA