Creating a maintenance plan in SQL Server 2005 or 2008 to optimize database performance

Owned by Anonymous
Last updated: Jan 12, 2016 by Max Pinion (Deactivated)
2 min read

Schedule a SQL maintenance plan on your Symantec or Arellia databases that will complete the following tasks:

  • Rebuild indexes
  • Set the index free space percentage to ten percent
  • Update column statistics (Note: Index statistics are updated during the index rebuild process)

Info

Schedule maintenance to run weekly (preferred) or monthly during a time when database utilization by the Symantec or Arellia applications are at their lowest (for example, on a Sunday during the day or a during a scheduled maintenance cycle).

Build the SQL Maintenance plan for SQL 2005/2008 by doing the following steps:

  1. Make sure that the SQL Server Agent service is running.
  2. Open SQL Server Management Studio.
  3. Double-click the Management folder.
  4. Right-click Maintenance Plans and click Maintenance Plan Wizard.
  5. In the SQL Server Maintenance Plan Wizard, click Next.
  6. Give the maintenance plan a name such as "Rebuild Indexes for Symantec CMDB."
  7. Leave the default option set to Single schedule for the entire plan...
  8. Click the Change button to put in the schedule for this plan.
  9. Enter in the chosen weekly time.
  10. Click OK.
  11. Click Next.
  12. Select the Rebuild Indexes and Update Statistics check boxes.
  13. Click Next.
  14. Make sure that Rebuild Index task is at the top.
  15. Click Next.
  16. Click the Database drop-down.
  17. In the These databases section, select all the databases you are trying to optimize, such as Symantec_CMDB.
  18. Click OK.
  19. Select Change free space per page percentage to and set its value to ten percent (twenty percent if you are only rebuilding indexes monthly).
    1. Sort results in tempdb should generally not be used; however, if SQL memory resources are low, then this will help, but it does cause rebuilding to take a lot longer.
    2. Make sure that the Keep index online while reindexing check box is clear. Symantec CMDB uses ntext fields which prevent clustered indexes from being rebuilt while online for those tables that have an ntext column.
  20. Click Next.
  21. Choose the same databases as before.
  22. In the Update section, select Column Statistics Only.
  23. In the Scan Type section, select Full scan.
  24. Click Next.
  25. Check the option to Write a report to a text file and allow it to write to the default location.
  26. Click Next.
  27. Click Finish.

Once the wizard is finished with creation, you can click Close. To execute the maintenance plan, right-click the left hand Object Browser pane and click Execute.

Additional performance considerations