Home :: Knowledge Base :: Technical Troubleshooting and Installation Guides

Microsoft SQL Server Tuning and Performance - Best Practices

It is recommended that a nightly maintenance task schedule be setup using SQL Management Studio. The following article describes how to setup a SQL Maintenance Plan to perform some of this maintenance on a scheduled basis. It takes approximately 10 minutes to set up. Do not perform this work if you are not comfortable with Microsoft SQL Server and Windows 2005/2008 server administration.

The maintenance plan we recommend is to rebuild the database indexes and update database statistics. Over time these can become out of synch with the databases and cause performance issues. You will want to update statistics and indexes on all 5 FileHold databases.

The following article is for Microsoft SQL Server 2005 but the same methods can be used for Microsoft SQL Server 2008. This article relates to the FileHold Server Tuning FAQ: http://www.filehold.com/help/system/filehold-server-tuning-faq

To tune Microsoft SQL Server

  1. Enable SQLSERVERAGENT in Services.msc.
  2. Set to Startup type to Automatic.

Microsoft SQL Tuning article

  1. Open SQL Management Studio.
  2. Expand Management > Maintenance and right click on Maintenance and select Maintenance Plan Wizard.

Microsoft SQL Tuning article

  1. The SQL Server Maintenance Plan Wizard window opens. Click Next.

SQL Server Maintenance Plan Wizard

  1. Enter a Name for the Maintenance Plan such as "FileHold Rebuild Index + Update Statistics Maintenance Plan".
  2. This example will set up a recurring daily plan at midnight. Select Single schedule for the entire plan or no schedule and click Change to set up a schedule.

Select Plan Properties

  1. In the Schedule Job Properties window, set the schedule type to Recurring.
  2. Set the frequency to Daily at 1 time a day.
  3. In the Daily Frequency area, set the time to 12 AM.
  4. Set the Start date to today's date.
  5. Click OK.

Job Schedule Properties

  1. In the Maintenance Plan Wizard window, click Next.
  2. Select the Rebuild Index and Update Statistics check boxes in the Maintenance Tasks list.

Select Maintenance Tasks

  1. Click Next to configure Rebuild Index Maintenance Tasks.
  2. To define the settings for the Rebuild Index portion of the maintenance plan, click the arrow in the All User Databases field and then choose All User Databases (excluding master, model, msdb, tempdb).
  3. Click OK.

Define rebuild index task

  1. In the Advanced Options area of the Define Rebuild Index Task window, select the Keep index online check box.
  2. Click Next to configure the Update Statistics Maintenance Task settings.
  3. Click the arrow in the All User Databases field and then choose All User Databases (excluding master, model, msdb, tempdb).

Define Update Statistics Task

  1. Click Next to set up the report and email options. Choose which options you want.

Select Report Options

  1. In the Complete the Wizard window, review a summary of your settings and click Finish.

Complete the Wizard

  1. The tasks will be added and scheduled in the Maintenance Plan Wizard. Click Close when complete.

Maintenance Plan Wizard Progress

  1. The maintenance plan will then be updated in Microsoft SQL Management Studio and the SQL Server Agent Jobs will be updated.

Maintenance Plan

  1. Monitor the maintenance plan on a periodic basis to make sure that the plan is functioning normally.

 

 

See Also:

Technical Documentation and Troubleshooting