For this example, we’re going to simulate a single large transaction and view its effects on database file sizes and performance. We’re going to ignore some managerial and developmental best practices to really emphasize the importance of proactively sizing your database. In this example, we’re going to create a database with the default file size and auto growth settings, and insert 100 million records. This is going cause the database to rely on auto growth to size the data and log files for a database.
|WaitType||Wait_Sec||Wait Count||Wait Percentage||AvgWait_Sec|
So what happens if we presize the database to these sizes? We’re going to drop and recreate the database, this time, we’re going to proactively grow the database to 1.5GB and the log to 35GB. Then we’re clearing our wait stats and reloading that table. The results of the second test are below.
So just how do you size your database files? Unfortunately, there’s no easy answer here. You have to know what to expect in terms of growth and normal, abnormal transactional activity in the database. Are there large transactions that run frequently? Are there very large transactions that run infrequently? What about index maintenance? Rebuilding large indexes, etc. You really need to know the workings of the database.
There are a few guidelines that you can use if you really don’t know what to expect. Start by looking at historic growth rates. If you’re not tracking this, you can look at historic full backup sizes. Full backups only backup pages with data and, after some time of regular backups, you can trend growth. Size your data files according to a few months growth. Transaction logs, look at the largest index in the database. Index rebuilds are single transactions, your transaction log will need to accommodate the index size. Start here and monitor your environment. Set up alerts to tell you when file sizes are close to being maxed out. You should absolutely set auto grow on for your files, but do not rely on it. These are meant to emergency processes in case your files fill up before you can manually grow them. Follow these guidelines and you can definitely reduce wait times for auto grow events.
SQL Server Architect