Creating a maintenance plan in SQL Server 2005 or later to optimize database performance
Last Updated May 08, 2019
Question How can I create a maintenance plan in SQL Server 2005 or later to optimize database performance?
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 or later:
Make sure that the SQL Server Agent service is running.
Open SQL Server Management Studio.
Expand the Management folder.
Right-click on Maintenance Plans and select Maintenance Plan Wizard.
When the SQL Server Maintenance Plan Wizard info page opens up; click Next.
Give the maintenance plan a name such as “Rebuild Indexes for Altiris Databases”.
Leave the default option set to Single schedule for the entire plan… Click the Change button to put in the schedule for this plan.
Enter in the chosen weekly time.
Check the options to Rebuild Indexes and Update Statistics.
Make sure that “Rebuild Index” task is at the top.
Click the Database drop-down.
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.
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.
Chose the same databases as before.
In the Update section, select Column Statistics Only.
In the Scan Type section, select Full scan
Check the option to Write a report to a text file and allow it to write to the default location.
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.
Imported Document ID: HOWTO8589
Subscribing will provide email updates when this Article is updated. Login is required to Subscribe