Keeping your SQL Server running smoothly is extremely important for ensuring optimal performance and preventing possible business disruptions, and that's where regular maintenance plans come into play. Although SQL servers offer many maintenance tasks (database integrity, shrink database, reorganize index, etc.), we'll focus on "Rebuild Index", "Update Statistics," and "Clean Up History." These tasks help improve query performance, ensure accurate data retrieval, and manage server resources effectively.
By following the steps below, you can proactively manage your server's health and avoid costly downtime, making maintenance plans an indispensable part of your database management strategy.
In SQL Management Studio, in the Object Explorer, click on the Server icon to expand the list, then click on "Management", then right-click on "Maintenance Plans". In the drop-down menu, choose “Maintenance Plan Wizard.” Click "Next."
The next step is to configure the job schedule, including name, schedule type, frequency, and duration. In the example below, I have entered the following:
The next step is to choose which tasks to run. As I mentioned in the beginning, we will be running the Rebuild Index, Update Statistics and Clean Up History maintenance tasks.
Now, you can choose the order in which the tasks will run. In the example below, I have chosen from first to last: Rebuild Index, Update Statistics, and then Clean Up History.
In this step, you have to select the options that you'd like the Rebuild Index Task to perform. In the example below, I have entered the following:
As you just finished in the previous task, now you're selecting the options for the Update Statistics Task. In the example below, I have entered the following:
And for the last maintenance task chosen in this example, we'll select the options for the History Cleanup Task. In the example below, I have entered the following:
In this step, you can choose whether to save or distribute/email a report generated by the maintenance plan actions. This report is run after the tasks are complete and is not required.
Now, you can save the maintenance plan you've just created.
Now we need to verify that the maintenance plan was saved and created successfully. Navigate back to the main menu, go to the Maintenance Plans folder, and choose the maintenance plan you've just created. Right-click on it and select "Execute."
After the plan completes, right-click the plan again and choose "View History" to review the maintenance plan and verify the success of your created maintenance plan.