I recently come across interesting tool called ‘SQL Server Compression Estimator’. I find this tool very interesting. You can download this tool from CodePlex. This tool is pretty decent tool and I used it on couple of my personal server and it gave me good estimate.
SQL Server Compression Estimator (SSCE) will allow you to estimate the best compression for your objects. It will estimate both ROW and PAGE compression, and will optionally report the Index Maintenance Ratio.
You can order the resultset based on compressed Size or % Saving. Based on the estimate you can check the checkbox in the last column. Clicking on Generate Script will generate T-SQL script to compress your object (Index, Table).
Just like every other tool, you have to use it carefully and use your own judgement on production server. However, I find this tool as good start on this subject. Thanks to Bob Taylor who has created this tool. He has blogged about this tool over here.
Reference : Pinal Dave (