A couple of weeks ago, I was working on a Microsoft PDW proof-of-concept (POC) and had to measure compression ratios. In order to do this, I fired up SSMS and wrote a little script. The script will iterate through all tables in a database and run the sp_estimate_data_compression_savings stored procedure. This will only work in SQL Server 2008+ versions running Enterprise edition.
If you’re not familiar with this stored procedure, it basically will tell you what effect PAGE or ROW compression will have on your table/index/partition, etc. There are pro’s and con’s with compression. What I’ve tended to see is that compression has very positive results on space, IO, and query duration, with a negative impact on CPU and write speed. Like most things, it’s a trade-off and the results will vary by environment, so I recommend you do some testing before you apply compression to all tables. I tend to use compression mostly for my historical tables and partitions and leave my recent data uncompressed. And, back to the script, I use this stored procedure to estimate the impact of compression and to determine whether to use PAGE or ROW compression. PAGE is a higher level of compression, which means it’s also more expensive in terms of CPU, so if the difference between the two results is negligible, I’m more apt to just use ROW compression.
Now that my impromptu compression discussion is done, let’s get to the actual script. One final word of caution, however. This is an IO intensive process, so you may want to run it after peak business hours.
SET NOCOUNT ON; DECLARE @printOnly BIT = 0 -- change to 1 if you don't want to execute, just print commands , @tableName VARCHAR(256) , @schemaName VARCHAR(100) , @sqlStatement NVARCHAR(1000) , @tableCount INT , @statusMsg VARCHAR(1000); IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE '%#tables%') DROP TABLE #tables; CREATE TABLE #tables ( database_name sysname , schemaName sysname NULL , tableName sysname NULL , processed bit ); IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE '%#compression%') DROP TABLE #compressionResults; IF NOT EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE '%#compression%') BEGIN CREATE TABLE #compressionResults ( objectName varchar(100) , schemaName varchar(50) , index_id int , partition_number int , size_current_compression bigint , size_requested_compression bigint , sample_current_compression bigint , sample_requested_compression bigint ); END; INSERT INTO #tables SELECT DB_NAME() , SCHEMA_NAME([schema_id]) , name , 0 -- unprocessed FROM sys.tables; SELECT @tableCount = COUNT(*) FROM #tables; WHILE EXISTS(SELECT * FROM #tables WHERE processed = 0) BEGIN SELECT TOP 1 @tableName = tableName , @schemaName = schemaName FROM #tables WHERE processed = 0; SELECT @statusMsg = 'Working on ' + CAST(((@tableCount - COUNT(*)) + 1) AS VARCHAR(10)) + ' of ' + CAST(@tableCount AS VARCHAR(10)) FROM #tables WHERE processed = 0; RAISERROR(@statusMsg, 0, 42) WITH NOWAIT; SET @sqlStatement = 'EXECUTE sp_estimate_data_compression_savings ''' + @schemaName + ''', ''' + @tableName + ''', NULL, NULL, ''PAGE'';' -- ROW, PAGE, or NONE IF @printOnly = 1 BEGIN SELECT @sqlStatement; END ELSE BEGIN INSERT INTO #compressionResults EXECUTE sp_executesql @sqlStatement; END; UPDATE #tables SET processed = 1 WHERE tableName = @tableName AND schemaName = @schemaName; END; SELECT * FROM #compressionResults;