If your SQL is newer than 2016 SP1, you could enable page compression. It tends to squeeze Veeam database down about 3 times. I've been using it for a while, about 2y I'd guess. No hard measurements but ancient SQL Server blog posts said that it's almost fully transparent to application (SCCM has some problems with replication) with possible performance improvements (less disk IO).
You should rerun if after upgrade as well because new tables and indexes don't get compression by default.
Example code cobbled together from various online examples:
Code: Select all
--Compress tables
declare commands cursor for
SELECT
'ALTER TABLE ['
+ schemas.[name]
+ '].['
+ tables.[name]
+ '] REBUILD WITH (DATA_COMPRESSION=PAGE);'
from sys.tables AS tables with (nolock)
INNER JOIN sys.schemas AS schemas WITH (NOLOCK) ON tables.[schema_id] = schemas.[schema_id]
inner JOIN sys.partitions AS partitions with (nolock) on tables.object_id = partitions.object_id
INNER JOIN sys.dm_db_partition_stats AS partitionstats WITH (NOLOCK) ON tables.object_id = partitions.object_id and partitions.partition_id = partitionstats.partition_id
WHERE tables.[type] = 'U' AND partitions.[data_compression] < 2 and partitions.index_id in (0,1)
ORDER BY partitionstats.[reserved_page_count]
declare @cmd varchar(max)
open commands
fetch next from commands into @cmd
while @@FETCH_STATUS=0
begin
print (@cmd)
exec(@cmd)
fetch next from commands into @cmd
end
close commands
deallocate commands
set @cmd = NULL
--Compress indexes
declare commands cursor for
SELECT
'ALTER INDEX "'
+ indexes.[name]
+ '" ON "'
+ schemas.[name]
+ '"."'
+ tables.[name]
+ '" REBUILD WITH (DATA_COMPRESSION=PAGE);'
FROM sys.tables AS tables WITH (NOLOCK)
INNER JOIN sys.indexes AS indexes WITH (NOLOCK) ON tables.[object_id] = indexes.[object_id]
INNER JOIN sys.partitions partitions with (nolock) ON tables.[object_id] = partitions.[object_id]
INNER JOIN sys.schemas schemas WITH (NOLOCK) ON tables.[schema_id] = schemas.[schema_id]
INNER JOIN sys.dm_db_partition_stats AS partitionstats WITH (NOLOCK) ON indexes.[object_id] = partitionstats.[object_id] AND partitionstats.[index_id] = indexes.[index_id] and partitions.partition_id = partitionstats.partition_id
WHERE tables.type = 'U' AND indexes.[index_id] >1 AND partitions.[data_compression_desc] <> 'PAGE'
ORDER BY partitionstats.[reserved_page_count]
open commands
fetch next from commands into @cmd
while @@FETCH_STATUS=0
begin
print (@cmd)
exec(@cmd)
fetch next from commands into @cmd
end
close commands
deallocate commands
set @cmd = NULL
--Shrink database
declare commands cursor for
SELECT 'DBCC SHRINKFILE (N''' + [name] + ''',0);' FROM sys.database_files;
open commands
fetch next from commands into @cmd
while @@FETCH_STATUS=0
begin
print (@cmd)
exec(@cmd)
fetch next from commands into @cmd
end
close commands
deallocate commands
set @cmd = NULL
--Rebuild indexes
declare @tableName nvarchar(500)
declare @indexName nvarchar(500)
declare @indexType nvarchar(55)
declare @percentFragment decimal(11,2)
declare @SchemaName nvarchar(500)
declare @pagelocks int
declare FragmentedTableList cursor for
SELECT
OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName,
indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent,
schemas.name as schemaname,
ind.allow_page_locks as pagelocks
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id
inner join sys.tables tables ON tables.object_id = ind.object_id
INNER JOIN sys.schemas schemas ON tables.schema_id = schemas.schema_id
WHERE
indexstats.avg_fragmentation_in_percent > 5
AND ind.Name is not null
ORDER BY indexstats.avg_fragmentation_in_percent DESC
OPEN FragmentedTableList
FETCH NEXT FROM FragmentedTableList
INTO @tableName, @indexName, @indexType, @percentFragment, @schemaname, @pagelocks
WHILE @@FETCH_STATUS = 0
BEGIN
print 'Processing ' + @indexName + 'on table ' + @tableName + ' which is ' + cast(@percentFragment as nvarchar(50)) + ' fragmented'
if(@percentFragment<= 30 or @pagelocks = 0)
BEGIN
EXEC( 'ALTER INDEX "' + @indexName + '" ON "' + @schemaname + '"."' + @tableName + '" REBUILD; ')
print 'Finished reorganizing ' + @indexName + 'on table ' + @schemaname + '"."' + @tableName
END
ELSE
BEGIN
EXEC( 'ALTER INDEX "' + @indexName + '" ON "' + @schemaname + '"."' + @tableName + '" REORGANIZE;')
print 'Finished rebuilding ' + @indexName + 'on table ' + + @schemaname + '"."' + @tableName
END
FETCH NEXT FROM FragmentedTableList
INTO @tableName, @indexName, @indexType, @percentFragment, @schemaname, @pagelocks
END
CLOSE FragmentedTableList
DEALLOCATE FragmentedTableList