Index Reorganize and Rebuild in SQL Server

Saturday, June 25, 2011 |


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

1 comments:

Anonymous said...

We have a database that was performing badly. I checked it out and the indexes where badly fragmented. I ran a utility to rebuild the indexes. But even afterward there were severaly tables that did not reduce the fragmentation %. I used SQL Server STudio Manager to rebuild and the fragmentation % still didn't change. I went into the design of the tables. Removed the primary Key (it was an identity primary key index). Closed. Reopened and added it back in and it still didn't change. I've never seen a database do this before. I've checked fill% and they are all set to 0 for these indexes. Anyone know what could cause this?