automating rebuild or reorg index on specific large tables of the database in SQL server which are really fragmented

 Following Script can be helpful to automate the Rebuild/Reorg Index on tables with in database which are huge and really fragmented.

Scenario :  While working with few clients I observed there was huge database with large table sizes and the indexes of the table were not much fragmented , but still there was maintenance plan configured to run rebuild index and update stats on all the tables of the database on every weekend

observations : performing index rebuild and update stats can cause performance issue (like CPU being pegged continuously) and log file growing huge.

Thus it makes sense to run the rebuild/reorg index only if its needed

following code serve the purpose :

Below code make use of dmv to identify fragmentation on the indexes with in the tables of the database , and perform either rebuild or reorg index only on affected indexes , later ran update stats only on the tables whose indexes were rebuild/reorganized

USE [provide dbname where table indexes are fragmented]

declare @dbid smallint = DB_ID();

declare @IndexInfo table (
  SchemaName SYSNAME NOT NULL,
  TableName SYSNAME NOT NULL,
  IndexName SYSNAME NOT NULL,
  IndexPageCount BIGINT NOT NULL,
  FragmentationPercent FLOAT NOT NULL,
  CurrentFillFactor TINYINT NOT NULL,
  StatsName NVARCHAR(256) NULL,
  StatsLastUpdate DATETIME2 NULL,
  StatsModificationCount BIGINT NULL,
  PRIMARY KEY (SchemaName, TableName, IndexName)
);

insert @IndexInfo (SchemaName, TableName, IndexName, IndexPageCount, FragmentationPercent, CurrentFillFactor, StatsName, StatsLastUpdate, StatsModificationCount)
select ss.name, so.name, si.name, ips.page_count, ips.avg_fragmentation_in_percent, si.fill_factor, st.name, stmeta.last_updated, stmeta.modification_counter
from sys.indexes si
join sys.[objects] so
  on so.[object_id] = si.[object_id]
join sys.schemas ss
  on ss.[schema_id] = so.[schema_id]
join sys.dm_db_index_physical_stats(@dbid, NULL, NULL, DEFAULT, DEFAULT) ips
on ips.database_id = @dbid
and ips.[object_id] = so.[object_id]
and ips.index_id = si.index_id
left join sys.stats st
  on st.[object_id] = so.[object_id]
  and st.[stats_id] = si.[index_id]
  and st.[is_temporary] = 0
  and st.[auto_created] = 0
  and st.[user_created] = 0
outer apply sys.dm_db_stats_properties(so.[object_id], st.stats_id) stmeta
where si.type_desc in ('clustered', 'nonclustered')
and so.is_ms_shipped = 0
  and ips.alloc_unit_type_desc = 'IN_ROW_DATA'
order by ips.page_count desc;

declare @line nvarchar(max) = null;
declare cur cursor local static for
select t.MaintenanceCommand
from (
  select 1 [Ordinal], p.IndexPageCount,
    N'SET QUOTED_IDENTIFIER ON;ALTER INDEX [' + p.IndexName + N'] ON [' + p.SchemaName + N'].[' + p.TableName + N'] ' +
    case when p.FragmentationPercent >= 30.0 then N'REBUILD PARTITION=ALL  WITH (ONLINE = ON);' else N'REORGANIZE PARTITION=ALL;' end + N'' [MaintenanceCommand]
  from @IndexInfo p
  where p.IndexPageCount >= 50
    and p.FragmentationPercent >= 5.0
union all
  select 2 [Ordinal], p.IndexPageCount,
    N'UPDATE STATISTICS [' + p.SchemaName + N'].[' + p.TableName + N'] [' + p.IndexName + N'] WITH FULLSCAN;' [MaintenanceCommand]
  from @IndexInfo p
  where p.StatsModificationCount > 100
    and (p.IndexPageCount < 50
      or p.FragmentationPercent < 30.0)
    and ((p.StatsModificationCount >= (p.IndexPageCount / 3))
      or datediff(day, p.StatsLastUpdate, sysdatetime()) >= 14)
) t
order by t.Ordinal asc, t.IndexPageCount desc;


open cur;
fetch next from cur into @line;
while @@fetch_status = 0 begin

exec sp_executesql @line

fetch next from cur into @line;
end;

close cur;
deallocate cur;