Determining Index fragmentation / when to rebuild / when to reorganize

Index fragmentation:
When data is inserted, updated or deleted in a table, if clustered or non-clustered indexes exist on that table, they must be maintained to reflect the changes. The maintenance of these indexes eventually will cause the indexes less efficient. This inefficiency leads to index fragmentation.

We have two types of index fragmentation:
Internal Fragmentation
External Fragmentation

In result of DELETE operation, spaces occurs in the underlying index page, so when index page is not full as it should be, it leads to internal fragmentation.

Sometimes when INSERT or UPDATE operation occurs, database engine creates additional index page to accommodate this operation, a page split occurs and a new page created by maintaining the logical order, as it does not maintain the physical order of index pages. So this variation in logical and physical ordering of index pages called external fragmentation.

SQL Server maintains the counters or values to keep track indexes internal and external fragmentation, so you can evaluate by using these indicators, when to rebuild and when to reorganize the indexes to increase its efficiency.

You can get these stats from a dynamic management function that is replacement of DBCC SHOCONTIG statement:

SYS.DM_DB_INDEX_PHYSICAL_STATS(database_id/Null, object_id/Null, index_id/Null/0, partition_number/Null, mode/Null/Defualt)

database_id/Null: ID of the database, you can use DB_ID(‘database_name’) or DB_ID() to get the current database id. If you specify Null then it will return the information for all databases, and you must supply object_id, index_id and partition_number with Null values.
object_id/Null: ID of base table or view. You can use Object_ID(‘table/view name’) to get object id. If you specify Null, it will return the information of all table/view of current database, you must supply index_id and partition_number with Null values.
index_id/Null/0: ID of index. You can specify Null, it will return the information of all indexes for specified base table or index, you must supply partition_number with Null value. 0 index_id is for heap tables.
partition_number/Null: partition number of index/heap or Null to return the information for all partitions.
mode/Null/Default: mode specifies the scan level that is used to obtain statistics. Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED or DETAILED. The default is LIMITED.


Now you have to decide when to rebuild (recreating the current indexes) the indexes and when to reorganize (de-fragmentation of the current indexes) them, you have to observe the values of avg_fragmentation_in_percent and avg_page_space_used_in_percent columns returned by sys.dm_db_index_physical_stats DMF.


If avg_fragmentation_in_percent greater than 15 OR avg_fragmentation_in_percent less than 60 then you have to rebuild or recreate the indexes as:

ALTER INDEX ALL ON Table_Name REBUILD


If avg_page_space_used_in_percent between 60 and 75 Or avg_fragmentation_in_percent between 10 and 15 then you have to reorganize the indexes as:

ALTER INDEX ALL ON Table_Name REORGANIZE

Example:

SELECT
DB_NAME(DATABASE_ID)
,OBJECT_NAME(OBJECT_ID)
,INDEX_ID
,INDEX_TYPE_DESC
,AVG_FRAGMENTATION_IN_PERCENT
,AVG_PAGE_SPACE_USED_IN_PERCENT
,CASE WHEN AVG_FRAGMENTATION_IN_PERCENT >15 OR AVG_PAGE_SPACE_USED_IN_PERCENT<60 THEN 'REBUILD INDEX' END AS INDEX_REBUILD_STATE
,CASE WHEN (AVG_FRAGMENTATION_IN_PERCENT >10 AND AVG_FRAGMENTATION_IN_PERCENT<15) OR
(AVG_PAGE_SPACE_USED_IN_PERCENT>60 AND AVG_PAGE_SPACE_USED_IN_PERCENT<75) THEN 'RE-ORGANIZE INDEX' END AS INDEX_REORGANIZE_STATE
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(), OBJECT_ID('TEST'), NULL, NULL, 'DETAILED')
WHERE INDEX_ID<>0