keep SharePoint operating smoothly and with optimal performance, it is
highly recommended that SharePoint administrators conduct regular
maintenance on each SQL Server database. Such maintenance tasks include
rebuilding indexes, checking database integrity, updating index
statistics, and performing internal consistency checks and backups.
Administrators can perform database maintenance tasks either by
executing Transact-SQL commands or by running the Database Maintenance
Wizard. This section provides information and recommendations for
maintaining the databases that host SharePoint data and configurations.
Later in this section, administrators will learn how to automate and
schedule the major maintenance tasks by creating database maintenance
plans via SQL Server Database Maintenance Wizard.
Checking and Repairing Database Integrity
DBCC CHECKDB is the most
frequently used validation command for checking the logical and physical
integrity of the whole database. Essentially, DBCC CHECKDB is a
superset command that actually runs CHECKALLOC, CHECKTABLE, and
Here are some recommendations for using DBCC CHECKDB:
should run DBCC CHECKDB rather than the individual operations because it
identifies most of the errors and is generally safe to run in a
After running DBCC CHECKDB, administrators should run it again with the REPAIR argument to repair any reported errors.
CHECKDB can be time-consuming, and it acquires schema locks that
prevent metadata changes; therefore, it is highly recommended that
administrators run it during nonproduction hours.
The command should be run on a table-by-table basis if it is used to perform consistency checks on large databases.
Monitoring and Reducing Fragmentation
Although indexes can speed up
the execution of queries, some overhead is associated with them. Indexes
consume extra disk space and involve additional time to update
themselves any time data is updated, deleted, or inserted in a table.
When indexes are first built,
little or no fragmentation should be present. Over time, as data is
inserted, updated, and deleted, fragmentation levels on the underlying
indexes may begin increase.
When a page of data is
completely full and further data must be added to it, a page split
occurs. To make room for the new arriving data, SQL Server creates
another data page somewhere else in the database (not necessarily in a
contiguous location) and moves some of the data from the full page to
the newly created one. The effect of this is that the blocks of data are
logically linear but physically nonlinear. Therefore, when searching
for data, SQL Server has to jump from one page to somewhere else in the
database looking for the next page it needs instead of going right from
one page to the next. This results in performance degradation and
inefficient space utilization.
level of an index is the percentage of blocks that are logically linear
and physically nonlinear. In SQL Server 2008 R2, SQL Server 2008, or SQL
Server 2005, administrators can use the sys.dm_db_index_physical_stats
dynamic management function and keep an eye on the
avg_fragmentation_in_percent column to monitor and measure the
fragmentation level. The value for avg_fragmentation_in_percent should
be as close to zero as possible for maximum performance. However, values
from 0 percent through 10 percent may be acceptable.
In the previous version of
SharePoint, it was recommended to track and reduce the fragmentation
level by running the database statistics timer job, which in turn
updates the query optimization statistics and rebuilds all indexes in
the content databases every time it runs. Another option was
reorganizing or rebuilding the indexes on a regular basis using the SQL
Server 2008 or SQL Server 2005 Maintenance Wizard.
In SharePoint 2010,
administrators no longer need to worry about fragmentation because
SharePoint can do that on their behalf via the health analyzer. The
health analyzer performs “health checks” based on timer jobs and
self-heals the database index fragmentation automatically.
Shrinking Data Files
SQL Server 2005 and SQL Server 2008/R2, administrators can reclaim free
space from the end of data files to remove unused pages and recover
However, shrinking data
files is not recommended unless the content database has lost at least
half of its content. This typically happens after some activities that
create white space in the content database, such as moving a site
collection from a content database to another one or deleting a massive
amount of data. Shrinking SharePoint databases other than content
databases is not recommended, because they do not generally experience
as many necessary deletions to contain considerable free space.
Shrinking a Database by Using SQL Server 2008 R2 Management Studio
The following steps show how to shrink a database by using SQL Server 2008 R2 Management Studio:
Click Start, All Programs, Microsoft SQL Server 2008 R2, SQL Server Management Studio.
Connect to the desired SQL Server database engine instance and expand that instance.
Expand Databases, right-click the database to be shrunk, click Tasks, click Shrink, and click Files.
Select the file type and filename from the dialog box shown in Figure 1.
Figure 1. Shrinking a database using SQL Server 2008.
select Release Unused Space. Selecting this option causes any unused
space in the file to be released to the operating system and shrinks the
file to the last allocated extent. This reduces the file size without
moving any data.
select Reorganize Files Before Releasing Unused Space. If this option
is selected, the Shrink File option must be set to value. Selecting this
option causes any unused space in the file to be released to the
operating system and tries to relocate rows to unallocated pages.
select Empty File by Migrating the Data to Other Files in the Same
Filegroup. Selecting this option moves all data from the specified file
to other files in the filegroup. The empty file can then be deleted.
This option is the same as executing DBCC SHRINKFILE with the EMPTYFILE
Creating SQL Server Maintenance Plans
backend databases can significantly improve the health and performance
of SharePoint servers. Unfortunately, administrators often do not
perform regular database maintenance because maintaining SharePoint 2010
environments involves a huge set of maintenance tasks.
Fortunately, Microsoft has provided maintenance plans
as a way to automate these tasks. A maintenance plan performs a
comprehensive set of SQL Server jobs that run at scheduled intervals.
Specifically, the maintenance plan conducts scheduled SQL Server
maintenance tasks to ensure that databases are performing optimally, are
regularly backed up, and are checked for anomalies. Administrators can
use the Maintenance Plan Wizard (included with SQL Server) to create and
schedule these daily tasks. In addition, the wizard can configure
database and transaction log backups.
It is also worth mentioning
that administrators should set any maintenance operations or maintenance
plans to run during off-hours to minimize the performance impact on
Configuring a SQL Server 2008 R2 Database Maintenance Plan
The following steps show how to configure a SQL Server 2008 R2 database maintenance plan:
Click Start, All Programs, Microsoft SQL Server 2008 R2, SQL Server Management Studio.
Connect to the desired SQL Server database engine instance.
Click Management, right-click Maintenance Plans, and click Maintenance Plan Wizard.
On the Welcome to the Database Maintenance Plan Wizard screen, click Next to continue.
On the Select Plan Properties screen, as shown in Figure 2, enter a name and description for the maintenance plan.
Figure 2. Creating a database maintenance plan.
Decide whether to configure one or more maintenance plans.
To configure a single maintenance plan, select Single Schedule for the
Entire Plan or No Schedule. This option is chosen in the sample here.
To configure multiple maintenance plans with specific tasks, select Separate Schedules for Each Task.
Click Change to set a schedule for the plan. The Job Schedule Properties dialog box appears, as shown in Figure 3.
Figure 3. Scheduling a database maintenance plan.
Complete the schedule, click OK, and then click Next to continue.
On the Select Maintenance Tasks screen (see Figure 4), select the maintenance tasks to include in the plan, and then click Next to continue.
Figure 4. Selecting database maintenance tasks.
the Select Maintenance Task Order page, change or review the order that
the tasks will be executed, select a task, and then click Move Up or
Move Down. When tasks are in the desired order, click Next. The wizard
guides you through setting the details for each task. For example, Figure 5 shows the configuration of the Database Check Integrity Task.
Figure 5. Configuring database maintenance tasks.
On the Select Report Options page, select Write a Report to a Text File, select a location for the files, as shown in Figure 6, and then click Next until the wizard is completed.
Figure 6. Saving and emailing maintenance plan reports.
It is highly
recommended that administrators include the Check Database Integrity
maintenance task for all SharePoint databases and the Maintenance
Cleanup Task maintenance task in their plans. It is also recommended not
to select the option to shrink the database, primarily because
automatically shrinking databases on a periodic basis leads to excessive
fragmentation and produces I/O activity, which can negatively influence
the performance of SharePoint.