March 23, 2011 at 3:30 am
Hi Folks
I have just applied data compressions on a couple of tables, and I would like to know how much I have gain in savings Compressed vs uncompressed. I have searched the internet and system tables and views for finding this information but with no luck.
I know of the stored procedure sp_estimate_data_compression_savings, but it seems to be for before you have applied the compression
Any ideas?
/Michael Søndergaard
March 23, 2011 at 5:21 am
Add your data type sizes (per row) and multiple by the number of rows in each table. This will get you the uncompressed size.
Don't forget to add the overhead byte for each variable column (varchar, nvarchar, etc.)
And (before Gail kicks me) don't forget that LOB items aren't stored in the table. I'm not sure what the cost is for the pointer, though.
March 23, 2011 at 5:45 am
There's always the old-fashioned way. Create the database uncompressed and compressed side-by-side and compare. That's what I did when I was first checking it out. But you know, the storage savings are nice and all, but the real savings are in performance. Do a comparison between compressed and uncompressed indexes. As long as you're not CPU bound on your server, the difference is blinding. Compression is wonderful.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply