Index Reorganize and Rebuild in SQL Server

I have already mentioned quite a few times since now in few of my previous articles that Index could be the key to boost up performance of your “SELECT” query but highly fragmented Index could degrade the performance of your query.

When you created an index, sorting are done and in case of clustered index, physical data get stored page wise but after regular Insert/Update/Delete in the same table, fragmentation comes into the picture where your physical data order doesn’t match up with your logical data order exists in data page.  If there is any heavy fragmentation you find for any index, you should try to remove this by using Index reorganize or Index rebuild.

Let us understand what the exact meaning of Reorganize and Rebuild is.

Reorganize Index defrag the fragmented pages at leaf level, in simple language, it arrange data in leaf page level and if it find any empty space in any page, it removes it so free space could be claimed. This action is online action; means while doing this action on live server, it won’t block any objects (like table) for long time and you can query your object (database Table) while this operation in progress. Reorganize Index consume less resource as compared with Rebuild Index but Reorganize Index is ideal for low fragmented Index, let us say if your Index fragmented percentage is between 5% to 40%, you can use Reorganize Index otherwise go for Rebuild Index to gain proper benefit.

BTW, if you index fragmentation is less than 5%, then don’t need to do anything as even after reorganizing or rebuilding, you won’t get any more boost up as less than 5% fragmentation is not really a big deal and that is why, you don’t need to add any overhead on your server by doing reorganizing or rebuilding.

Rebuild Index drops current index and recreate Index again, this consumes high resources of the servers but it is worth doing if you Index fragmentation percentage is higher, let’s say more than 40%. While doing Rebuilding, objects get locked so you won’t be able to query (if you have not used WITH (ONLINE=ON) option). 

Based on my personal experience I personally prefer to “Rebuild Index” on off hours or may be in weekend if table is very big because sometime, it may take few hours or a day.

Now, question comes into the picture that how can I decide the fragmentation of the Index? Well it is fairly very simple, you have to query system function “sys.dm_db_index_physical_stats” and you have to pass your database name and table id in this function and it will return with very crucial information about your indexes on the specified table but it will not return Index name but it will return, Index ID and that is why, we have to make one JOIN of this “sys.dm_db_index_physical_stats” function with “sys.Indexes” system catalog.

SELECT

      sysin.name as IndexName

      ,func.avg_fragmentation_in_percent

FROM

      sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'orders'),NULL, NULL, NULL) AS func

JOIN

      sys.indexes AS sysIn

ON

      func.object_id = sysIn.object_id AND func.index_id = sysIn.index_id;

I wanted to look the index details of my table “Orders” so I used my table name but you can replace the table name you want.

Hope you are now clear when to use rebuild and when to use reorganize. It is very simple, if you see values between 5% to 40% in your “avg_fragmentation_in_percent” field of above query, go for reorganize, if you see >40% then go for rebuild and if less than 5%, get back to your chair and have rest, there is nothing to do in this matter.  LOL

Once you decide whether to do Rebuild or Reorganize, you have use very simple query to do this operation on your table. Have a look at below queries.

--I wanted to reorganize my index, named "idx_refno" on orders table

ALTER INDEX idx_refno ON Orders REORGANIZE

GO

--I wanted to reorganize all my indexes which are there for orders table

ALTER INDEX ALL ON Orders REORGANIZE

GO

--I wanted to REBUILD my index, named "idx_refno" on orders table

ALTER INDEX idx_refno ON Orders REBUILD

GO

--I wanted to REBUILD all my indexes which are there for orders table

ALTER INDEX ALL ON Orders REBUILD

GO

BTW, I have created Index "idx_refno" and "orders" table in one of my previous article of Index, if you want to use the same object, have a look at the table and index script at here.

Do drop your comments about this concept!!!

if you want to refer all other articles related to index, click here.

Happy Indexing!!!

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”