Query - When to rebuild or reorganize Index in SQL Server

Using below
query we will get fragmentation percentage, then based on fragmentation percentage we will decide index should be rebuild or reorganize.

• Fragmentation is between 5-30% – Perform index reorganization
• Fragmentation is higher than 30% – Perform index rebuild

select
 
 OBJECT_SCHEMA_NAME(idxs.object_id) As SchemaName
 
              
,object_name(idxs.object_id) As TableName
 
              
,idxs.name As IndexName
 
              
,idxs.type_desc As IndexType
 
              
,i.avg_fragmentation_in_percent
 
              
,i.partition_number  -- incase table is partitioned
 
              
,i.avg_page_space_used_in_percent
 
              
,i.record_count
 
from sys.indexes idxs
 
inner join sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,'sampled') i  on i.object_id = idxs.object_id and i.index_id = idxs.index_id
 
where idxs.type in (0 /*HEAP*/,1/*CLUSTERED*/,2/*NONCLUSTERED*/,5/*CLUSTERED
COLUMNSTORE*/
COLUMNSTORE*/
COLUMNSTORE*/,6/*NONCLUSTERED COLUMNSTORE*/)
 
and (alloc_unit_type_desc
= 'IN_ROW_DATA' /*avoid LOB_DATA or
ROW_OVERFLOW_DATA*/
ROW_OVERFLOW_DATA*/
ROW_OVERFLOW_DATA*/ or
alloc_unit_type_desc is null /*for
ColumnStore indexes*/
ColumnStore indexes*/
ColumnStore indexes*/)
 
and OBJECT_SCHEMA_NAME(idxs.object_id) != 'sys'
 
and idxs.is_disabled=0

 
order by
avg_fragmentation_in_percent Desc