SQL Server indexing - when to reorganize or rebuild?

Re-organizing or rebuilding index would help the SQL optimizer to reduce the fragmentation for a better performance. In this case few users might have doubt that when to rebuild or reorganize (SQL Server 2005)?

IN the series of Performance blog posts here I would like to suggest - How to Detect Table Fragmentation in SQL Server 2000 and 2005  article  and IndexesTags related blog posts for index performance here. Further on this series you can run following TSQL with the help of DMV in SQL Server 2005:

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
WHERE avg_fragmentation_in_percent <> 0
ORDER BY avg_fragmentation_in_percent DESC;

That will get you the fragmented indexes, which I can defragment or rebuild and intermittent execution of update statistics on frequently used tables would help gain the optimum performance. For me a reason for not to use DBCC DBREINDEX() once a week is, reorganizing the indexes is much faster that are required or frequently used tables. Also think about performance hit when rebuilding indexes on tables that have many millions of rows and several indexes.  Instead of auto-rebuilding, I prefer to be notified when defragmenting has failed to produce the desired results, then I can schedule rebuilding around the requirements of the business.

In adding up more about when to choose what, the Defragment/Reorganize commands existed as a lightweight index maintenance tool  and DBREINDEX() was for situations when the index has degraded to the point where it can no longer be helped by Defragmenting. In this case think about I/O spikes in addition to the table fragmentation. For instance for very small db -- 128K or less -- as long as extra extent reads are not more than one, I don't worry about other fragmentation on those. So for the large databases in terms of Disk I/O relation where every activity is hugely affected, then even a 10% overall fragmentation (beyond whatever your normal freespace is) will force 100M of additional bytes to be forced thru the data buffers for a full scan. Additionally  reorganization is much more limited than a rebuild.  It's almost like rearranging a closet by moving a few things around vs pulling everything out and putting it back in fully in order.