Script – Implement SQL Server Data Compression
SQL Server Data Compression feature lets you compress tables and individual indexes. The compression does incur a few extra CPU cycles every time you read or write to that table or index, but this overhead is hugely compensated by much better IO performance. In most cases, increasing a little more work for the CPU for significantly lesser IO needs will result in faster query return times.
You will obviously need to test compression to see if it works well in your environment, and also compare the differences between PAGE vs. ROW compression.
If you plan to implement compression for every table and index in the environment then below is the script you can use. It will generate a T-SQL which you can execute for rebuilding every table (including heaps) and index with PAGE compression. I have added a delay of 1 second after every command so as to reduce the risk of database running out of log space.
WITH cte_TableList ( RowNum ,TableName ,IndexName ,Index_ID ,Data_Compression ) AS ( SELECT ( ROW_NUMBER() OVER ( ORDER BY ('[' + s.name + '].[' + o.name + ']') ) ) ,'[' + s.name + '].[' + o.name + ']' AS TableName ,i.name AS IndexName ,i.index_id ,p.data_compression FROM sys.objects o INNER JOIN sys.schemas s ON o.schema_id = s.schema_id INNER JOIN sys.partitions p ON o.object_id = p.object_id INNER JOIN sys.indexes i ON o.object_id = i.object_id AND p.index_id = i.index_id WHERE o.type = 'U' AND p.data_compression != 2 ) ,cte_Cmds ( RowNum ,Cmd ) AS ( SELECT ((Rownum - 1) * 2) ,'ALTER TABLE ' + TableName + ' REBUILD WITH (DATA_COMPRESSION = PAGE) ;' FROM cte_TableList WHERE index_id = 0 UNION ALL SELECT ((Rownum - 1) * 2) + 1 ,'ALTER INDEX ALL ON ' + TableName + ' REBUILD WITH (DATA_COMPRESSION = PAGE) ;' FROM cte_TableList WHERE index_id < 2 ) ,cte_Final ( RowNum ,Cmd ) AS ( SELECT (RowNum * 4) ,'PRINT ''' + Cmd + '''; ' FROM cte_Cmds UNION SELECT (RowNum * 4) + 1 ,Cmd FROM cte_Cmds UNION SELECT (RowNum * 4) + 2 ,'PRINT ''Completed at: '' + CONVERT(VARCHAR(32), SYSDATETIME(), 120);' FROM cte_Cmds UNION SELECT (RowNum * 4) + 3 ,'WAITFOR DELAY ''00:00:01''; ' FROM cte_Cmds ) SELECT Cmd FROM cte_Final ORDER BY RowNum
You can obviously modify this to implement ROW data compression instead.