When you delete, update or insert the data, the SQL Server Database Engine updates the indexes automatically. Over time such changes lead to the data in the index being scattered all over the database, in other words – fragmented. Fragmentation occurs when indexes contain pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Such indexes (fragmented) can lead to slow application response and decreased query performance.

This issue can be solved by reorganizing or rebuilding the index. Before using it, note that “Rebuild” and “Reorganize” are two different operations, even though they both reduce fragmentation in the index. It is practically the same operation, which gives you the same result.

What does Index Rebuilding do?

The “Rebuild” index option creates a new structure for the index. If the index is disabled, the “Rebuild” operation, returns it to the working state. When you rebuild the index, there is an opportunity to apply a new fill factor. If during the “Rebuild” operation, you have changed your mind and have canceled it halfway, all changes will be rolled back.

What does Index Reorganization do?

The “Reorganize” index option is more facilitated than the “Rebuilt” index. This operation checks the index, and if there is a need, it fixes the physical ordering of pages. Also, the “Reorganize” operation compacts pages to apply any previously set fill factor settings. Unlike in the “Rebuilt” index, the “Reorganize” index option is always online. If you have changed your design during the operation, all changes that were made will be saved.

How to Reorganize and Rebuild Indexes using SqlBak

If you want to automate index reorganization/rebuilding, you can take advantage of the Maintenance Jobs feature in SqlBak.

Just press the “Add new job” button on your “Dashboard” page and choose “Maintenance job” as a “Job type” at the popup window. Then click on the “Create Job” button.

Follow these simple steps to create a scheduled maintenance job.

 

In step #5 you can add script manually, just click on the “Add script” button and choose “SQL script”

then add the needed script into the appropriate box.

Or simply add it from the list of predefined scripts:

Then you will need to set up a script execution schedule and (optionally) enable email notifications. That’s it.

How to Reorganize and Rebuild Indexes using T-SQL

Here we would like to introduce you to the three most common ways of how to Reorganize and Rebuild Indexes. With the help of T-SQL Commands, it should look like this:

“Rebuilt” index option

ALTER INDEX ALL ON Adventureworks.[<table_name>] REBUILD WITH (FILLFACTOR = <fill_factor>, SORT_IN_TEMPDB = <ON|OFF>, STATISTICS_NORECOMPUTE = <ON|OFF>);

“Reorganize” index option

ALTER INDEX ALL ON Adventureworks.[<table_name>] REORGANIZE

How to Reorganize and Rebuild Indexes using SSMS

Also, it is possible to do it with the help of SSMS (SQL Server Management Studio):

Choose the database and then the table where you want to Reorganize and Rebuild Indexes. Then right-click to the Indexes folder and choose “Reorganize All” or “Rebuild All”.