RSA Scripts

Check Index for Rebuild or Reorganize

Check Index for Rebuild or Reorganize. If below 30% then Reorganize else Rebuild.

  1. Sql Server
  2. Performance
  3. Intermediate
  4. Vaknin Sammy

Last updated:

Warning: Review and test in a non-production environment before running.

Back to results

SELECT OBJECT_NAME(OBJECT_ID), index_id, index_type_desc, index_level,
       avg_fragmentation_in_percent, avg_page_space_used_in_percent, page_count
  FROM sys.dm_db_index_physical_stats (DB_ID( N'AdventureWorks2016CTP3') , object_id('Product'), NULL, NULL , 'SAMPLED') 
 ORDER BY avg_fragmentation_in_percent DESC;

/*
%5 to %30 = REORGANIZE
> 30%     = REBUILD 
*/

ALTER INDEX [IX_OrderTracking_CarrierTrackingNumber] ON [Sales].[OrderTracking] REORGANIZE  WITH ( LOB_COMPACTION = ON );

ALTER INDEX [IX_OrderTracking_CarrierTrackingNumber] ON [Sales].[OrderTracking] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

-- We can use WITH (ONLINE = ON) to alter the index in an online status. request disk space while running, because the index is duplicated.