This post would like to describe how to rebuild indexes in MS SQL 2008 R2.
There are many ways to rebuild indexes in SQL. Here we will see how to use Maintenance Plan tool to rebuild whole indexes in a particular database or whole databases in SQL server instance. It’s simple but need to take care little bit.
SQL Server Management Studio–> Management –> Maintenance Plans –> Maintenance Plan Wizard
Launch the wizard and click next. Give the Name of the Maintenance Plan wizard. Set schedule if you want to run every week or according to your choice.
Click Next and select Check Database Integrity and Rebuild Index tasks.
Check Database integrity is an useful choice before do the rebuild because if database has any integrity problem then no need to do the rebuild of index.
One thing you have to notice that Rebuild index task is high CPU and very I/O intensive and it locks the database resources as well. So better to do this on off business hours.
Rebuild index task will do the update statistics and reorganize task. So no need to select these two tasks if you are doing a rebuild index. It’s a common mistake we could see while using Maintenance Plan Wizard that select all the tasks even if not necessary. It will slow down the performance of your SQL server.
Click Next –> Select which database you want to do the Database Integrity. Or select whole database. This step will repeat to Rebuild index as well.
Select the log file location
Next screen will finish the task
This task will run based on the schedule you have mentioned. Or you can trigger the task when you want from Maintenance Plan section.