Corey Beck on data compression:
Before we jump right into enabling either row or page compression, we can actually estimate the savings of each to determine which will provide us with the most savings on storage. Since page compression includes row compression, we will start with row compression and the estimated savings.
EXEC sp_estimate_data_compression_savings
‘Person’,’Person’,null,null,’row’
In practice, data compression is extremely valuable and in most circumstances, the benefits outweigh the costs. In certain workloads, you might even see CPU usage go down.