Log Shipping – NORECOVERY vs. STANDBY mode

This will very useful, if you have implemented the partitioning functionality .

 

SELECT  OBJECT_NAME(a.object_id) AS object_name
       ,a.index_id
       ,b.name
       ,b.type_desc
       ,a.partition_number
       ,a.avg_fragmentation_in_percent
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') a
JOIN    sys.indexes b ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE   a.object_id = OBJECT_ID('tableName')-- need to change the tablename here
ORDER BY OBJECT_NAME(a.object_id)
       ,a.index_id
       ,b.name
       ,b.type_desc
       ,a.partition_number 

once the fragmentation has been identified use the following script to rebuild the index on a single partition. in this example I am rebuilding the partition 18

ALTER INDEX PK_tableName
ON dbo.tableName
REBUILD PARTITION = 18 ;
GO