1.877.833.1202

Microsoft SQL Server Tuning Best Practices

It is recommended that a nightly maintenance task schedule be setup using Microsoft SQL Management Studio. The following article describes how to setup a Microsoft SQL maintenance plan to perform some of this maintenance on a scheduled basis. 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 4 FileHold databases.

TIP: Do not perform this work if you are not comfortable with Microsoft SQL Server and Windows server administration.

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.
  2. Enter a Name for the Maintenance Plan. In this example, will set up a recurring daily plan at midnight.
  3. Select Single schedule for the entire plan or no schedule and click Change to set up a schedule.

Microsoft SQL Maintenance Plan - 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 Databases field. 
  3. Select These databases and select the four FileHold databases: ch_documentrepository, ch_fulltextsearch, ch_librarymanager, ch_userrolemanager (NOTE: your prefix may differ from ch_ for the database names) and 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 Databases field.
  4. Select These databases and select the four FileHold databases: ch_documentrepository, ch_fulltextsearch, ch_librarymanager, ch_userrolemanager (NOTE: your prefix may differ from ch_ for the database names) and click OK.

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.
  1. The maintenance plan will then be updated in Microsoft SQL Management Studio and the SQL Server Agent Jobs will be updated.

Maintenance Plan in SQL Management Studio

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