index fragmentation in sqlserver, When to reorganize and when to rebuild indexes? How to defragment indexes?

Query shows index fragmentation information:

SELECT object_name(dt.object_id) Tablename,si.name
IndexName,dt.avg_fragmentation_in_percent AS
ExternalFragmentation,dt.avg_page_space_used_in_percent AS
InternalFragmentation
FROM
(
    SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
    FROM sys.dm_db_index_physical_stats (db_id('AdventureWorks'),null,null,null,'DETAILED'
)
WHERE index_id <> 0) AS dt INNER JOIN sys.indexes si ON si.object_id=dt.object_id
AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10
AND dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC 


--> Analyzing the result, you can determine where the index fragmentation has occurred, using the following rules:

    ExternalFragmentation value > 10 indicates external fragmentation occurred for the corresponding index
    InternalFragmentation value < 75 indicates internal fragmentation occurred for the corresponding index


When to reorganize and when to rebuild indexes?

You should "reorganize" indexes when the External Fragmentation value for the corresponding index is between 10-15 and the Internal Fragmentation value is between 

60-75. Otherwise, you should rebuild indexes.


How to do it?
Go to Indexes --> Right click on specific indexes --> we can see Rebuid and Reorganize options.


How to defragment indexes?

1.Reorganize the fragmented indexes: execute the following command to do this
ALTER INDEX ALL ON TableName REORGANIZE

2.Rebuild indexes: execute the following command to do this
ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR=90,ONLINE=ON) 

You can also rebuild or reorganize individual indexes in the tables by using the index name instead of the 'ALL' keyword in the above queries. Alternatively, you can 

also use SQL Server Management Studio to do index defragmentation.