After installing SQL, some considerations – part 2

Configure Model

In my last blog, I began to go through some changes that can and should be made to your SQL server instance, once the main install is complete. Changes that can only help you in the long run when you have 100+ servers to manage and re-visiting is something you can only dream of. By getting it right first time, you save yourself a whole heap of time down the road.

In this blog, as the title may have already given away (!), I am going to talk about the Model database.

The Model database, under system databases, is the template database used for all subsequently created databases. The settings used in this database are carried across to all newly created databases. You can overwrite these setting by explicitly including them in your create database statement (in particular file sizes and growth patterns), but if they are left out when you issue the CREATE DATABASE command then they are taken from Model.

You can also use Model to include common objects in all subsequently created databases or include security that you want to be common across your server. For example, if there is a senior developer you want to be a dbo for all databases, rather than setting it each time a new database is added, you can create it once in Model and it will be added automatically to all newly created databases.

The most important change that you need to make is related to the initial file sizes and more especially the growth increments of your data and log files.

By default the Model database data and log files take these growth properties

File Logical name Physical name File growth
Primary data modeldev model.mdf Autogrow by 10 percent until the disk is full.
Log modellog modellog.ldf Autogrow by 10 percent to a maximum of 2 terabytes.

Ref: http://msdn.microsoft.com/en-us/library/ms186388.aspx

Auto growth is bad (m’kay). It can certainly be avoided by managing your systems proactively, but in some cases unexpected activity or problems in your system may cause your data and log files to grow. When growth does happen, the settings in your database are what define how that growth is handled, and unless otherwise specified, the settings in your database are taken from the Model database at creation time. So, by changing the default settings in Model, you can more actively manage unexpected growth in your system.

Incremental growth in SQL files takes place when additional space is required. SQL does not wait for a quiet time to increase file sizes. If an active transaction requires additional space to be added, that transaction will sit and wait until the space is available, before it can commit. This means that if you have a percentage growth of 10% on a data file of 100GB then SQL will have to allocate and grow the data file by 10GB before the transaction completes. This can lead to a timeout on your active transaction and other performance problems.

There is a route round this timely file growth, and that is by using SQL Instant File Initialization (only available on Windows 2003 and above and SQL 2005 and above). This is a topic for another day, but Brad McGehee explains the topic very well here.

Should I grow by percent or by fixed size?

For me, this is a no brainer. As described above, when you use a % growth, the size that is added is dependent on the size of the file that is growing. This introduces an unknown variable into the equation. If you are not managing your data and log files proactively, then as the database files grow the size of the growth increments increase with it. While the file is small this may not be a problem, but as your database gets bigger and bigger the time taken to expand your files increases, eventually leading to noticeable performance impacts. By defining an incremental size in MB in the Model database you can preempt this problem.

Setting the growth to be a certain size in MB holds especially true for the Log File. I am not going to go into details about the proper sizing of Transaction Logs, as others have already covered much more thoroughly than I could (this article by Kimberly Tripp has helped me a lot).

There are many other config changes that you may decide to make, depending on your environment and what policies you would like to abide by, but the disk growth and sizes are IMHO the most important and deserving of a mention here, and by making these small changes to model you are making your life easier in the long run, and isn’t that what it is all about?

Advertisements
This entry was posted in SQL Server and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s