Tag Archives: sql server management

Microsoft

Optimizing SQL

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

Article: HOWTO8589

Question
How can I create a maintenance plan in SQL Server 2005 or 2008 to optimize database performance?
Answer
A SQL maintenance plan for rebuilding indexes and setting the index free space percentage to 10% within the Symantec/Altiris databases should be scheduled to run at least monthly (preferably weekly). This maintenance plan should also be configured to update column statistics (index statistics are updated during the index rebuild process). It is recommended that this plan be scheduled to run at a time when database utilization by the Symantec applications is at its lowest. This could be on a Sunday during the day or a during a scheduled maintenance cycle, etc.

To build the SQL Maintenance plan for SQL 2005/2008:

  1. Make sure that the SQL Server Agent service is running.
  2. Open SQL Server Management Studio.
  3. Expand the Management folder.
  4. Right-click on Maintenance Plans and select Maintenance Plan Wizard.
  5. When the SQL Server Maintenance Plan Wizard info page opens up; click Next.
  6. Give the maintenance plan a name such as “Rebuild Indexes for Altiris Databases”.
  7. Leave the default option set to Single schedule for the entire plan… Click the Change button to put in the schedule for this plan.
  8. Enter in the chosen weekly time.
  9. Click OK.
  10. Click Next.
  11. Check the options to Rebuild Indexes and Update Statistics.
  12. Click Next.
  13. Make sure that “Rebuild Index” task is at the top.
  14. Click Next.
  15. Click the Database drop-down.
  16. In the These databases section, select all the databases you are trying to optimize, such as Altiris, Altiris_Incidents, eXpress, AeXRSdatabase, Symantec_CMDB and Symantec_CMDB_IntelAMT.
  17. Click OK.
  18. Select Change free space per page percentage to and set its value to 10% (20% if only rebuilding indexes monthly).
    • “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.
    • Make sure that Keep index online while reindexing is unchecked.  Altiris databases uses ntext fields which prevent clustered indexes from being rebuilt while online for those tables that have an ntext column.
  19. Click Next.
  20. Chose the same databases as before.
  21. In the Update section, select Column Statistics Only.
  22. In the Scan Type section, select Full scan Click Next.
  23. Check the option to Write a report to a text file and allow it to write to the default location.
  24. Click Next.
  25. Click Finish.

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

Additional Performance Considerations:

  • Make certain that the database files are not physically fragmented. Periodically check the fragmentation level on the volume where the database files are hosted and defragment when needed.
  • See KB49632: Optimizing SQL Server 2005 on Windows Server 2003 for more optimization information.


Article URL http://www.symantec.com/docs/HOWTO8589