Rebuilding Indexes using SQL Server Maintenance Wizard !

Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the existing fill factor setting, and reorders the index rows in contiguous pages. [Source technet.microsoft.com]

Note – If tables are small then rebuilding index will be fast and there won’t be any noticeable performance impact. But if the tables are big then rebuilding index using online mode can somewhat have negative impact on the performance. So it is advisable to do it in offline mode if the maintenance window allows it. If maintenance window is not enough to run rebuild index task in offline mode then follow the below mentioned steps to do it in Online mode using Maintenance Wizard. Try to run this task during less load hours.

Steps-

  • Open Object Explorer in SQL Server Management Studio, expand the Management folder and go to Maintenance Plans folder.
  • Right click on the Maintenance Plans folder and select New Maintenance Plan from the drop down.
  • Enter a name for the plan and click on Ok. The new Maintenance plan will open with no tasks.
  • Go to View toolbar option and select Toolbox so that ToolBox window will show from where we can select the task to be added to the maintenance plan.
  • Double click on the ‘Rebuild Index Task’ from the ToolBox. The task will be added to the maintenance plan.

Index_Task

  • Now Right Click on the task and select Edit. 

Task_Edit

  • Select the databases dropdown and check the databases on which you wish to run the indexing. Remember to check the ‘Ignore databases where the status is not online’. Click on Ok.

Rebuild_Index_edit

  • Select the ‘Reorganize pages with the default amount if free space’ radio button and in advance options section check the ‘Keep index online while reindexing’. Click on Ok.

IndexOptions

  • Save the plan and close it. Now go to Object Explorer and right on the created Maintenance Plan. Click on Execute. This will run the indexing task as per the configured settings. Also we can schedule the plan.

Execute_task

Task will run and show the success message when completed.

Thanks for checking out.

Please share, like and comment !

 

 

Categories
Comments
All comments.
Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.