The below procedure will return the size occupied by the indexes in a table. This will be helpful to measure the size before and after the maintenance activities.
Create Proc SQLZealot_FindIndexSize( @OBJECT_NAME VARCHAR(255) ) As Begin DECLARE @temp TABLE ( indexID BIGINT, objectId BIGINT, index_name NVARCHAR(MAX), used_page_count BIGINT, pages BIGINT ) --Insert into temp table INSERT INTO @temp SELECT P.index_id, P.OBJECT_ID, I.name, SUM(used_page_count), SUM( CASE WHEN (p.index_id < 2) THEN ( in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count ) ELSE lob_used_page_count + row_overflow_used_page_count END ) FROM sys.dm_db_partition_stats P INNER JOIN sys.indexes I ON I.index_id = P.index_id AND I.OBJECT_ID = P.OBJECT_ID WHERE p.OBJECT_ID = OBJECT_ID(@OBJECT_NAME) GROUP BY P.index_id, I.Name, P.OBJECT_ID; SELECT index_name INDEX_NAME, LTRIM( STR( ( CASE WHEN used_page_count > pages THEN (used_page_count - pages) ELSE 0 END ) * 8, 15, 0 ) + ' KB' ) INDEX_SIZE FROM @temp T End /*-----Usage:*/ exec SQLZealot_FindIndexSize 'mas_user'