Script to find large indexes of Tables in database and rebuilding indexes for better performance

Script to find large indexes of tables in specific database.

--------Script Starts

declare @MaxPercentOfTotal numeric(6, 2) = 100.0

declare @MinPercentOfTotal numeric(6, 2) = 0.5

DECLARE @AddCompressionEstimate BIT = 0

declare @IndexInfo TABLE

(

    SchemaName sysname,

    TableName sysname,

    IndexName sysname NULL,

    IndexId INT NULL,

    Rows INT,

    TotalSpaceMB BIGINT,

    UsedSpaceMB BIGINT,

    ReservedSpaceMB BIGINT,

    PercentOfTotal numeric(6, 2),

    InUse BIT,

    FragmentationPercent numeric(6, 2),

    CompressionStatus TINYINT,

    CompressionStatusDesc sysname NULL,

    PageCompressionSize BIGINT NULL,

    RowCompressionSize BIGINT NULL

)

;with SpaceInfo(SchemaName, TableName, IndexName, IndexId, Rows, TotalSpaceMB, UsedSpaceMB)

as

( 

    select  

        s.name as SchemaName,

        t.Name as [TableName],

        i.name as [Index Name],

        i.index_id as [IndexId],

        sum(p.[Rows]) as [Rows],

        sum(au.total_pages) * 8 / 1024 as [Total Space MB],

        sum(au.used_pages) * 8 / 1024 as [Used Space MB]

    from sys.tables t 

        join sys.schemas s on s.schema_id = t.schema_id

        join sys.indexes i on t.object_id = i.object_id

        join sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id

        cross apply

        (

            select 

                sum(a.total_pages) as total_pages,

                sum(a.used_pages) as used_pages

            from sys.allocation_units a 

            where p.partition_id = a.container_id 

        ) au

    where i.object_id > 255

    group by t.object_id, i.index_id, s.name, t.name, i.name

)

INSERT INTO @IndexInfo(SchemaName, TableName, IndexName, IndexId, Rows, TotalSpaceMB, UsedSpaceMB, ReservedSpaceMB)

select 

    SchemaName, TableName, IndexName, IndexId, Rows, TotalSpaceMB, UsedSpaceMB

    ,TotalSpaceMB - UsedSpaceMB as [ReservedSpaceMB]

from SpaceInfo

where TotalSpaceMB > 0

order by TotalSpaceMB desc

option (recompile)

DECLARE @TotalSpaceMB BIGINT

select @TotalSpaceMB = SUM(TotalSpaceMB)

from @IndexInfo

update @IndexInfo

set PercentOfTotal = (TotalSpaceMB * 100.0) / @TotalSpaceMB

DELETE FROM @IndexInfo

WHERE PercentOfTotal < @MinPercentOfTotal OR PercentOfTotal > @MaxPercentOfTotal

update ii

set InUse = IIF(

    (s.user_seeks IS NULL OR s.user_seeks = 0)

    AND (s.user_lookups IS NULL OR s.user_lookups = 0)

    and (s.user_scans IS NULL OR s.user_scans = 0),

    0, 1),

    CompressionStatus = p.data_compression,

    CompressionStatusDesc = p.data_compression_desc

from @IndexInfo ii

    INNER join sys.indexes i ON OBJECT_NAME(i.OBJECT_ID) = ii.TableName AND OBJECT_SCHEMA_NAME(i.OBJECT_ID) = ii.SchemaName AND i.index_id = ii.IndexId

    LEFT JOIN sys.partitions p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id

    LEFT JOIN sys.allocation_units a ON a.container_id = p.partition_id

    LEFT JOIN sys.dm_db_index_usage_stats s ON s.object_id = i.object_id and i.index_id = s.index_id

update ii

set FragmentationPercent = ph.avg_fragmentation_in_percent

from @IndexInfo ii

    INNER join sys.indexes i ON OBJECT_NAME(i.OBJECT_ID) = ii.TableName AND OBJECT_SCHEMA_NAME(i.OBJECT_ID) = ii.SchemaName AND i.index_id = ii.IndexId

    LEFT JOIN sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, NULL) ph on i.object_id = ph.object_id and i.index_id = ph.index_id

declare @CompressionSavings TABLE

(

    ObjectName NVARCHAR(128),

    SchemaName NVARCHAR(128),

    IndexId INT,

    PartitionNumber INT,

    CurrentSizeKB BIGINT,

    CompressedSizeKB BIGINT,

    CurrentSampleSizeKB BIGINT,

    RequestedSampleSizeKB BIGINT,

    NewSizePercent NUMERIC(6, 2)

)

IF @AddCompressionEstimate = 1

BEGIN

    DECLARE @err int

    declare @TableName sysname

    declare @SchemaName sysname

    DECLARE @IndexId INT

    DECLARE loopCursor CURSOR FOR

    SELECT SchemaName, TableName, IndexId

    FROM @IndexInfo

    where CompressionStatus = 0

    OPEN loopCursor

    WHILE 1 = 1

    BEGIN

        FETCH loopCursor INTO @SchemaName, @TableName, @IndexId

        SELECT @err = @@error

        IF @err <> 0 OR @@fetch_status <> 0

            BREAK

        DELETE FROM @CompressionSavings

        INSERT INTO @CompressionSavings(ObjectName,SchemaName,IndexId,PartitionNumber,CurrentSizeKB,CompressedSizeKB,CurrentSampleSizeKB,RequestedSampleSizeKB)

        EXEC sys.sp_estimate_data_compression_savings @SchemaName, @TableName, @IndexId, NULL, 'PAGE';  

        update ii

        set PageCompressionSize = (select top 1 ISNULL((CAST(CompressedSizeKB AS NUMERIC(16, 2)) * 100.0) / CAST(CurrentSizeKB AS NUMERIC(16, 2)), 0.0) from @CompressionSavings)

        from @IndexInfo ii

        where SchemaName = @SchemaName AND TableName = @TableName AND IndexId = @IndexId

        DELETE FROM @CompressionSavings

        INSERT INTO @CompressionSavings(ObjectName,SchemaName,IndexId,PartitionNumber,CurrentSizeKB,CompressedSizeKB,CurrentSampleSizeKB,RequestedSampleSizeKB)

        EXEC sys.sp_estimate_data_compression_savings @SchemaName, @TableName, @IndexId, NULL, 'ROW';  

        update ii

        set RowCompressionSize = (select top 1 ISNULL((CAST(CompressedSizeKB AS NUMERIC(16, 2)) * 100.0) / CAST(CurrentSizeKB AS NUMERIC(16, 2)), 0.0) from @CompressionSavings)

        from @IndexInfo ii

        where SchemaName = @SchemaName AND TableName = @TableName AND IndexId = @IndexId

    END

    DEALLOCATE loopCursor

END

select * from @IndexInfo

order by TotalSpaceMB desc 

---------Script Ends

Then rebuild the indexes which has more fregmentation percentage as suggested in microsoft document. 

Commerce Data Exchange best practices - Commerce | Dynamics 365 | Microsoft Docs

Hope this helps