What is Index in SQL Server?
In RDBMS, there is a data structure that is used to improve the performance of SQL server at time of retrieving any data and this data structure is known as database index in SQL server. Instead of searching each row in table, one can use indexes that will provide a way of locating data and hence, accessing the database table.
What is Index Rebuilding?
The process of recreating a new structure of index in SQL Server for maintaining health of database is known as Index Rebuilding. There is a Rebuild option in SQL Server through which one can recreate the database index. If index is disabled from database, then one can use Rebuild operation for returning back to its working state.
NOTE: If user interrupts/cancel the working of rebuilding operation, then all the modification made will be rolled back to its previous state.
Need to Rebuild Index in SQL Server
Generally, while performing changes or modification index fragmentation takes place in form of wasted memory (termed as Gaps in data pages) and logical fragmentation. Gaps in data pages result in reduction of rows, which are archived in cache of the server. On the other hand, logical fragmentation occupies the disk storage space with useless data. Therefore, the only way to prevent rows reduction and save disk storage space is to rebuild indexes in SQL server. This will highly boost up the speed and performance of the database.
When to Rebuild Indexes?
If one constantly rebuild index in SQL server, then it will effect the physical storage of machine or will result in any other hazardous situation. Therefore, it is important to learn that when to rebuild indexes.
- If there is rapid index fragmentation, and user eagerly wants to execute Rebuild Index tasks with all other tasks related to database maintenance, then they can do so for boosting server performance.
- For weekly maintaining database and boosting server performance, user can perform rebuilding operation once in a week. However, it would be risky if user do not rebuild index since a week.
- Another condition is when user has not performed rebuilding from long time. In this condition, users will have to take following two alternatives into consideration:
- Use Reorganize Index tasks with Update Statistics tasks
- Use online version of Rebuild Index tasks
Measures to Rebuild Indexes
A very common question among SQL server users that How to rebuild index in SQL server? Well, by making use of Maintenance Plan Wizard of SQL server in following way, user can perform rebuild operation:
- Launch the Maintenance Plan Wizard on your machine
- Click on the scroll down button of Databases field & then select databases on which you want to perform rebuilding operation. You will have to select any of the displayed databases and then click on OK button to continue.
- Now you will return back to previous wizard. In this wizard you will come across two options:
- Object: This option is only enabled with some selective database. In other words, it will not be enabled with all the databases of the server. However, there are three options provided to you in this field i.e. Tables, Views, and Tables and Views. If you select Tables and Views option, then Rebuild operation will be applied to indexes related to tables and indexed views of the selected database. Well as per your need, you can also choose Tables or Views option.
- Selection: By clicking on selection drop-down button, you can choose that whether you want to perform rebuilding operation on all objects or selected objects. If you want to perform selective operation, then enable These objects option and select the objects that you want to take in operation; else activate All objects option. After finishing with all your selection procedure click on OK button.
- Next come Free space options field in which you are provided with two options and by default ‘Reorganize pages with default amount of free space’ option is enabled. You can make use of any one of the two options to clean the Gaps in data pages.
- Now comes an advance options field. With help these two option your will be able to determine that how much benefit will you have during rebuilding procedure.
- The last step of rebuilding indexes in SQL server is to define a valid schedule that can run your Rebuild Index job.
- Fill all the mandatory fields of this window with correct details and click on OK button.
How to rebuild index in SQL server is query that arises when user has to deal with index fragmentation problem. After going through whole blog, one can say that rebuilding index is an effective resource intensive tasks. In case, if the above workaround failed to rebuild indexes in SQL Server then you can also go with a SQL Recovery tool to fix the index fragmentation issues.