30 January,2014 by Jack Vamvas
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.
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
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |