I have already mentioned quite a few times since now in fewof myprevious articles
that Index could be the key to boost up performance ofyour “SELECT” query but highly fragmented Index could degrade the performanceof your query.
When you created an index, sorting are done and in case ofclustered index, physical data get stored page wise but after regularInsert/Update/Delete in the same table, fragmentation comes into the picturewhere your physical data order doesn’t match up with your logical data orderexists in data page. If there is anyheavy fragmentation you find for any index, you should try to remove this byusing Index reorganize or Index rebuild.
Let us understand what the exact meaning of Reorganize andRebuild is.
Reorganize Index defragthe fragmented pages at leaf level, in simple language, it arrange data in leafpage level and if it find any empty space in any page, it removes it so freespace could be claimed. This action is online action; means while doing thisaction on live server, it won’t block any objects (like table) for long timeand you can query your object (database Table) while this operation inprogress. Reorganize Index consume less resource as compared with Rebuild Indexbut Reorganize Index is ideal for low fragmented Index, let us say if yourIndex fragmented percentage is between 5% to 40%, you can use Reorganize Indexotherwise go for Rebuild Index to gain proper benefit.
BTW, if you index fragmentation is less than 5%, then don’tneed to do anything as even after reorganizing or rebuilding, you won’t get anymore boost up as less than 5% fragmentation is not really a big deal and thatis why, you don’t need to add any overhead on your server by doing reorganizingor rebuilding.
Rebuild Indexdrops current index and recreate Index again, this consumes high resources ofthe servers but it is worth doing if you Index fragmentation percentage ishigher, let’s say more than 40%. While doing Rebuilding, objects get locked soyou won’t be able to query (if you have not used WITH (ONLINE=ON) option).
Based on my personal experience I personally prefer to “RebuildIndex” 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 decidethe fragmentation of the Index? Well it is fairly very simple, you have toquery system function “sys.dm_db_index_physical_stats”and you have to pass your database name and table id in this function and itwill return with very crucial information about your indexes on the specifiedtable but it will not return Index name but it will return, Index ID and thatis why, we have to make one JOIN of this “sys.dm_db_index_physical_stats”function with “sys.Indexes” systemcatalog.
sysin.name as IndexName
sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N’orders’),NULL, NULL, NULL) AS func
sys.indexes AS sysIn
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 Iused my table name but you can replace the table name you want.
Hope you are now clear when to use rebuild and when to usereorganize. 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 rebuildand if less than 5%, get back to your chair and have rest, there is nothing todo in this matter. LOL
Once you decide whether to do Rebuild or Reorganize, youhave use very simple query to do this operation on your table. Have a look atbelow queries.
–I wanted toreorganize my index, named “idx_refno” on orders table
ALTER INDEX idx_refno ONOrders REORGANIZE
–I wanted toreorganize all my indexes which are there for orders table
ALTER INDEX ALL ON Orders REORGANIZE
–I wanted toREBUILD my index, named “idx_refno” on orders table
ALTER INDEX idx_refno ONOrders REBUILD
–I wanted toREBUILD all my indexes which are there for orders table
ALTER INDEX ALL ON Orders REBUILD
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.
Ask me any SQL Server related question at my “ASK Profile”