A developer asked me today what the difference was between ALTER INDEX … REBUILD and ALTER INDEX … REORGANIZE. One of the scripts he was running was first doing a rebuild of all indexes on a table, then doing a reorg, and he wanted to know if the reorg was necessary. I took his question as a sign from the universe that I needed to write a post explaining the difference between REBUILD and REORGANIZE.
Fragmentation
Before I jump right into a comparison of what each process does, I should provide a little background on why you would want to rebuild/reorg in the first place. The reason for it is a little something called fragmentation. When you first create an index everything is ordered nicely, the logical order of the index pages matches the physical order inside the datafile and life is good. And if no one ever changed any data, it would stay this way. Users tend to want to update data and insert data and even delete data if you can believe it, and over time this causes the logical ordering of the index pages to get out of synch with the physical ordering. For example, I have a Customers table and I create an index on the LastName column. When I first create the index, the index is ordered by the LastName value, physically and logically.
But then I add a new customer, Cary Grant. SQL Server tries to add an entry for Mr. Grant to the index between Clark Gable and Anthony Hopkins but there’s no room on the page. So SQL Server allocates a new page for the index and moves half of the entries on the original page to this new page. Now it can insert Mr. Grant into the original page.
This process is called a page split. The bad thing about page splits is that SQL Server got that new page from a completely different part of the datafile and now the physical order of the pages doesn’t match the logical order: fragmentation. The more this happens, the more fragmented your indexes become. The more fragmented your indexes become, the more SQL Server has to work to retrieve data. So when we have enough fragmentation in an index, we correct it by either reorganizing it or rebuilding it.
Reorganize
The first method for handling index fragmentation is to reorganize the index. Reorganizing an index is the same as running a DBCC INDEXDEFRAG. It is a minimally invasive procedure that always occurs online, so it won’t block your applications. SQL Server reads through the leaf-level pages of an index and reorders them so that the physical order matches the logical order. However, if the index spans multiple datafiles, reorganizing will only reorder pages within the same file, it won’t move pages between files. So it’s possible to still have some fragmentation even after reorganizing an index.
Another thing to keep in mind is that reorganizing an index does not update statistics for the index (unlike rebuilding), so if you’re performing reorgs as part of your routine index maintenance, you’ll still want to update statistics as a separate process.
Rebuild
Rebuilding is the big gun of index maintenance, it’s the equivalent of DBCC DBREINDEX or CREATE INDEX…WITH DROP_EXISTING. You want to use rebuild when you’re indexes are heavily fragmented, 30 percent or more. The rebuild process is just what it sounds like, SQL Server drops the old index and builds a new one. It removes fragmentation, compacts pages (thus reclaiming disk space), it also allocates new pages as needed to build the index as a set of contiguous pages. A nice thing about rebuilding an index is that you can also change the setting for index properties like the fillfactor and index padding, which, if set correctly, will help prevent fragmentation in the first place. Another plus is that, because you’re creating a completely new index, SQL Server automatically updates statistics for the index.
By default, this is an offline process, so it will cause some blocking. However you can also use the ONLINE=ON option to rebuild the index online and reduce blocking during the process. SQL Server does this by building a temporary mapping index to handle any writes that occur while the target index is being rebuilt.
Conclusion
So getting back to the original question, the developer wanted to know if it was necessary to reorganize a specific index right after performing a REBUILD ALL. The answer, of course, is no. After a rebuild your indexes are as close to perfect as they’re going to get.
Further reading
For more information on index maintenance take a look at the following resources.
Reorganizing and Rebuilding indexes
How Online Index Operations Work
Guidelines for Performing Online Index Operations
A nicely written post, clear and informative.
Thank you, sir.