Home > Alter, Functions and DMVs, SQL Queries, SQL Server, Storage Space, T-SQL > How to decide between “Rebuild vs Re-organize” for an index on a table.

How to decide between “Rebuild vs Re-organize” for an index on a table.

September 23, 2009 Leave a comment Go to comments

How do you know whether to rebuild or to reorganize index by using ALTER INDEX…REORGANIZE or ALTER INDEX…REBUILD against user tables?  you can reorganize indexes, if it is not heavily fragmented, which uses less system resources and runs online. but for heavily fragmented indexes, you often need to use the rebuild functionality. Usually it is difficult to determine this.
To help you determine which action to perform , periodically run a SELECT statement against the sys.dm_db_index_physical_stats DMF and use the following thresholds to make your decision:

A sample query is as given below

SELECT 
 OBJECT_NAME(OBJECT_ID) TABLENAME
 , INDEX_ID
 ,INDEX_TYPE_DESC                                                                                    
 ,AVG_FRAGMENTATION_IN_PERCENT
 ,FRAGMENT_COUNT       
 ,AVG_FRAGMENT_SIZE_IN_PAGES
 ,AVG_PAGE_SPACE_USED_IN_PERCENT
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID('MYDATABASE'), NULL, NULL, NULL,NULL)
WHERE OBJECT_ID > 99 AND ALLOC_UNIT_TYPE_DESC != 'LOB_DATA'

use ALTER INDEX…REORGANIZE to defragment indexes that fall under the following fragmentation thresholds: avg_fragmentation_in_percent > 10 and < 15 or avg_page_space_used_in_percent < 75 .

use ALTER INDEX…REBUILD to defragment indexes that fall under the following fragmentation thresholds: avg_fragmentation_in_percent > 15 or avg_page_space_used_in_percent < 60.

Advertisements
  1. ram
    December 16, 2010 at 8:58 am

    hello

    i tried the query, but getting error like this.

    FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(‘MYDATABASE’), NULL, NULL, NULL,NULL)
    *
    ERROR at line 9:
    ORA-00933: SQL command not properly ended

    what is the issue?

    thanks
    ramkumar

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: