Sharepoint 2010 : Maintaining SQL Server in a SharePoint Environment

To 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 CHECKCATALOG.

Here are some recommendations for using DBCC CHECKDB:

• Administrators should run DBCC CHECKDB rather than the individual operations because it identifies most of the errors and is generally safe to run in a production environment.

• After running DBCC CHECKDB, administrators should run it again with the REPAIR argument to repair any reported errors.

• DBCC 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.

Monitoring Fragmentation

The fragmentation 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.

Reducing Fragmentation

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

In 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 disk space.

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:

1.	Click Start, All Programs, Microsoft SQL Server 2008 R2, SQL Server Management Studio.
2.	Connect to the desired SQL Server database engine instance and expand that instance.
3.	Expand Databases, right-click the database to be shrunk, click Tasks, click Shrink, and click Files.
4.	Select the file type and filename from the dialog box shown in Figure 1. Figure 1. Shrinking a database using SQL Server 2008.
5.	Optionally, 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.
6.	Optionally, 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.
7.	Optionally, 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 option.
8.	Click OK.

Creating SQL Server Maintenance Plans

Maintaining SharePoint 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 users.

Configuring a SQL Server 2008 R2 Database Maintenance Plan

The following steps show how to configure a SQL Server 2008 R2 database maintenance plan:

1.	Click Start, All Programs, Microsoft SQL Server 2008 R2, SQL Server Management Studio.
2.	Connect to the desired SQL Server database engine instance.
3.	Click Management, right-click Maintenance Plans, and click Maintenance Plan Wizard.
4.	On the Welcome to the Database Maintenance Plan Wizard screen, click Next to continue.
5.	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.
6.	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.
7.	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.
8.	Complete the schedule, click OK, and then click Next to continue.
9.	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.
10.	In 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.
11.	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.

Note

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.