How can I create a maintenance plan in SQL Server 2005 or later to optimize database performance for SEPM?

Products

• Endpoint Security Complete
• Endpoint Protection

Issue/Introduction

A maintenance plan is required to optimize database performance for SQL Server.

Environment

Release : 14.x

Resolution

A SQL maintenance plan for rebuilding indexes and setting the index free space percentage to 10% within the SEPM databases should be scheduled to run at least monthly (preferably weekly). 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 or later:

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 the SEM5 (or other named SEPM databases) you are trying to optimize.
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.
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
23. Click Next.
24. Check the option to Write a report to a text file and allow it to write to the default location.
25. Click Next.
26. 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.

* If you are scheduling this job to run periodically ensure you have the SQL Server Agent service running.

 Additionally: TECH212291 contains a script that can be run to perform the Maintenance on a more regular basis.

 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 HOWTO10723: Optimizing SQL Server 2005 on Windows Server 2003 for more optimization information.