Will the performance of the live system decrease or increase in the process after performing Page Data Compression? Does anyone know the answer to this question?
Thanks for your help in advance.
I don't think there's any way to know. You'll use less disk space. You'll have less disk I/O. You'll use more CPU to compress and decompress the data.
It depends on what's limiting your performance now.
After the compression, I've gotten vastly better performance. We are I/O bound not CPU bound, as are most shops and instances. However, the initial compression does take rather a long time, be sure to allow for that.
And, keep in mind:
a rebuild of a compressed table takes much longer than a rebuild of a non-compressed table.
After compressing a table, be careful with rebuilds, especially on a clustered index.