Yesterday, I drove the Smart down to Burlington/Colchester, Vermont to meet up with MVP Roman Rehak and speak to the local user group about SQL Server Row, Page and VarDecimal compression, originally touched during this post.
The final version of the presentation is here.
I had much more performance gains on the SAN before (vardecimal), so for those of you with SSDs, perhaps the test scripts may not really show a big difference for the SELECT times...however, at least major disk space at least will be gained. We started with a table of 260MB and ended up dropping its size down to 80MB.
Here are the essential parts fo the script for you to test out compression on your own databases:
-- all SQL Server internal compression is done at the table level
-- testing started on a table that was 260MB, with a check on the storage used each time
-- I/O , according to the Actual Execution Plan for a normal SELECT started at 24.17 (no compression)
--first round of compression
ALTER TABLE schema.TableName REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW) -- table became 180MB
--second round of compression
ALTER TABLE schema.TableName REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE) -- table now down to 80MB
-- I/O cost, according to the Actual Execution Plan for a SELECT down to 7.62 (just under a third)
-- after all your compression work is done, run a single console command
DBCC SHRINKDATABASE(name,0)
– replacing the zero with the amount you want to leave free
This is a great query to find out which objects are not compressed and could be used to reduce the DB footprint in your infrastructure:
ReplyDeleteSELECT distinct Schema_name(schema_id) + '.' + name AS tablename,
'ALTER TABLE [' + Schema_name(schema_id) + '].[' + name + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);' AS command
FROM sys.objects
INNER JOIN sys.partitions
ON sys.partitions.object_id = sys.objects.object_id
WHERE TYPE = 'u'
AND data_compression = 0
AND Schema_name(sys.objects.schema_id) <> 'SYS'