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
