Optimizing SQL Server for SharePoint 2010 (part 3) - Model Database Settings

12/1/2010 5:58:53 PM

3. Model Database Settings

Every SharePoint database that you create inherits most of its database settings from the SQL Server system database called Model. After performing a default installation of SQL Server and then performing a default SharePoint installation, you may want to modify some of the Model database properties.

One of the first things you want to do after a successful installation of SharePoint 2010 is to access your SQL Server environment and make modifications to the Model database to help improve your SharePoint performance. Why is it important to do this after the installation of SharePoint? Although the two databases created during the installation—the configuration database and the Central Administration database—don’t have to be very large, your content databases will probably need to be larger than the default settings on the Model database. Modifying the settings of the Model database will ensure that your content databases are created using optimal settings.

3.1. Model Database Default Settings

All new SharePoint databases will inherit most Model database properties. For instance, if you create a new database from within Central Administration, the new database will inherit the following properties from the Model database.

  • Initial Size of primary data file

  • Initial Size of transaction log

  • Recovery model

However, some of these settings may not be adequate for the SharePoint databases that you will create. Figure 4 shows the default Files properties for the Model database.

Figure 4. Model database default files settings

There are a few performance concerns with these Files settings. The Initial Size value is small; the Autogrowth value is small; and the Path of the files is pointing to the default directory on drive C.

3.2. Modifying the Model Database Settings

This section describes how to improve performance by modifying the Initial Size setting for your new SharePoint content databases by modifying the Initial Size value of the SQL Server Model database. However, this modification shouldn’t be made without careful analysis and calculation.

3.2.1. Initial Size of Model Database

You may be wondering why it is important to consider the value of these settings before you create new databases. Because the Initial Size setting for new databases file is small and the Autogrowth option is enabled, whenever you try to add content to a database, SQL Server has to expand it using the incremental value in the Autogrowth setting. For instance, if you were to upload a 10 MB file into this database using the default Initial Size and Autogrowth settings, SQL Server would have to lock the database 8 to 10 times to grow the data file in 1-MB increments until there was enough room to accept the 10-MB file you wanted to upload. Furthermore, because the log file Initial Size is small and its Autogrowth setting is at 10 percent increments, this file would also have to grow to accept the file being uploaded. Also, each time these files are enlarged in 1-MB increments, it causes fragmentation of your hard drive. As you can imagine, this can have an enormous impact on your SharePoint performance.

This is why it is important that you carefully consider how much information will be contained within most of your SharePoint databases, as well as how much information will be added, modified, or deleted, before you modify the Initial Size setting in the Model database. After you make the change, all new databases created using the Model database will begin with that Initial Size value, which will eliminate—or at least reduce—the need for Autogrowth to occur. There is no magic number that is best for the Initial Size setting of the content databases; you must perform a careful analysis to make that determination yourself. However, the best practice is that the size of your content databases should not exceed 100 GB. This is a soft limit that will increase the chances of performing a recovery in less than four hours.


Your database transaction log initial size is normally 25 percent of the size of the associated data file.

3.2.2. Recovery Model of Model Database

You can also modify the recovery model on the Model database, and its setting will be inherited by all newly created databases.

Figure 5. Model database recovery model settings

3.2.3. Autogrowth Setting of Model Database

Unlike the previous two settings, even if you configure the Model database with specific Autogrowth settings, they will not be applied when a new database is created through the Central Administration interface.

Is this a bad thing? Not necessarily. It’s best to use the Autogrowth setting as more of an insurance policy on your databases. If the database does reach the maximum size, you want to make sure you have enough coverage; that is, you want to ensure it grows in larger increments rather than small increments (which can cause a negative performance impact on your server). Take the time to analyze and adjust the initial size of the Model database to reduce the likelihood of the Autogrowth occurring. However, if some massive entries are added to your SharePoint libraries, and this causes your content database to fill up, it won’t prevent users from accessing the database—it will just slightly impact the performance while SharePoint performs the necessary Autogrowth.

Like the Initial Size settings of your Model database, the default Autogrowth settings are not optimal for a SharePoint farm. The default Autogrowth setting for data files is 1 MB, and the setting for your transaction log files is 10 percent. You should modify these SharePoint database settings immediately after you create a new database.

That is how modifying these settings differs from making changes to the Initial Size settings. You can’t change the settings by increasing the values in the settings for the Model database; you need to change these settings after you create your SharePoint content databases. Speak to your DBA to confirm that the Autogrowth settings for your SharePoint databases have been increased to a more reasonable size—a value agreed on by everyone.

You also have a choice of having the Autogrowth occur using either a fixed incremental amount or a percentage of the existing size. You should use a fixed amount, like 1 GB, so if Autogrowth does occur, you will know how long the process will take. If you use a percentage, the Autogrowth time will vary depending on how large the file was originally.

When you configure your Autogrowth settings, you also have the opportunity to configure either unlimited file growth for the database files or to set a specific maximum file size. You should specify a maximum file size to prevent the entire drive that contains the database files from filling up. This is particularly important if the drives contain mission-critical data, because that data will not be accessible again until space is freed up on the drive.

3.3. Shrinking Your Content Databases

By default, SQL Server does not create an optimal configuration for your SharePoint integration. You must configure SQL Server to improve your SharePoint performance, by increasing the size of databases, adjusting database file location, and allowing for automatic growth of databases as needed.

Conversely, you also can configure your databases to automatically decrease in size by using the Auto Shrink option, as shown in Figure 6, or you can manually shrink them. This is done to free up space currently being used by your SharePoint databases. Although this option is available, you should use it sparingly. In fact, you should only shrink a database if you are confident that the database size won’t have to be increased again to accommodate SharePoint content. Altering the size of a database is costly from a performance perspective, so you want to create an environment that avoids the need for a constant increase and decrease in the size of your databases.


The SQL Server database Auto Shrink option should be used only in your SharePoint development environment, to assist in maintaining free space for your developers and minimize the size of the files they are working with during development.

The Auto Shrink operation causes fragmentation and is a resource-intensive operation that runs about every 30 minutes. It will perform a shrink only on databases that have more than 25 percent of free space in them.


Be extremely careful when shrinking databases so that you don’t cause performance problems during the shrink operation or cause the database to have to grow back to a larger size using the Autogrowth settings.

Figure 6. SQL Server Database Auto Shrink setting

The general recommendation is to perform a manual shrink on a database if more than 50 percent of the database size is free space and you need to recover that space. The manual shrink operation can be lengthy, so you should perform this operation during nonpeak hours. The following Transact-SQL command can be run from within Management Studio or from the command prompt using Sqlcmd.exe to manually shrink a database called UserDB to the amount of current data plus 10 percent.


The following Transact-SQL command can be run from within Management Studio or from the command prompt using Sqlcmd.exe to manually shrink a database file called DataFile1 in the UserDB database to a total size of 7 MB.



A database cannot be shrunk below its original file size or manually altered file size using the DBCC SHRINKDATABASE command. You can shrink the file that belongs to the database to a size smaller than the original or manually altered size by using the DBCC SHRINKFILE command.

Other -----------------
- Installing and Configuring SQL Server 2008 for SharePoint 2010
- SharePoint 2010 : Enforce Custom Validation on a Column
- SharePoint 2010 : Add a Site Column to a List or Document Library
- SharePoint 2010 : Add a Column to a List or Document Library
- SharePoint 2010 : Create a New Survey
- SharePoint 2010 : Create a New List
- SharePoint 2010 : Create a New Folder in a Document Library
- SharePoint 2010 : Create a New Document Library
- SharePoint 2010 : Open the Create Dialog for Lists and Libraries
- SharePoint 2010 : Use a Slide Library
- SharePoint 2010 : See What Files or List Items Are Waiting for Your Approval
- SharePoint 2010 : See What Files or List Items Are Waiting for Your Approval
- SharePoint 2010 : Approve or Reject a File or List Item
- SharePoint 2010 : Restore an Earlier Version of a File or List Item
- SharePoint 2010 : See What Files or List Items Are Checked Out to You
- SharePoint 2010 : Publish a File or List Item
- SharePoint 2010 : Check In and Check Out a File or List Item
- SharePoint 2010 : Use the Datasheet View to Add, Edit, or Delete Items and Files
- SharePoint 2010 : Edit the Properties of a File or List Item
- sharepoint 2007 : Search Options in MOSS
Most View
- Sharepoint 2010 : Content Management - Creating a Term Set
- SharePoint 2010 : Change the Document Template for the New Button in a Document Library
- SQL Server 2008 : Developing Custom Managed Database Objects (part 3) - Developing Managed User-Defined Functions
- Managing Windows Server 2012 Storage and File Systems : Storage Management (part 2) - Installing and configuring file services - Configuring the File And Storage Services role
- Windows Server 2008 : Recovering Role Services and Features (part 4)
- Windows7: General Solutions to Network Problems (part 1) - Turning On Network Discovery
- jQuery 1.3 : Working with numeric form data (part 1) - Shopping cart table structure
- Windows 7 : Configuring Hardware (part 1) - Device Stage & Using Device Manager
- Windows Server 2008 : Understanding the Windows AIK (part 1)
- Windows 7 : Setting Security Permissions on Files and Folders (part 3) - Assigning a User to Multiple Security Groups
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS