Stephen’s answer pretty much sums up the information you’re looking for, just adding my two cents in, hopefully without being redundant.
Backups shouldn’t take any longer because of Page or Row Compression. Worst case they take about the same, and they can possibly be faster because part of the normal backup process is compressing the data. I would presume (haven’t tested yet) that already compressed data will measurably speed up that step of the backup process, since the work has already been done ahead of time.
With compression, you’re essentially trading more CPU resource consumption for improved Disk and Memory performance. Data is persisted on Disk and loaded to / from Memory compressed. As Stephen pointed out, it isn’t until it is delivered to the consuming application that it’s decompressed, which is when you may experience higher CPU resource usage. Or as you guessed, when new data needs to be written to the database, it also becomes compressed first.
You should monitor your CPU throughout the day, and with common and heavy tasks your environment’s workload typically endures. If you find your CPU is constantly pegged near 100% (and it previously wasn’t), then you may want to further investigate into if your compression settings are the main contributor, and possibly try testing loosening the settings on some of your most frequently used or larger tables / objects.
I always refer to
sp_estimate_data_compression_savings (as Stephen mentioned) before changing compression, as it’s pretty helpful in telling you how beneficial it may be to compress an object one way or another vs not compressing all. You can use that to help you debug and see if you guys are unnecessarily compressing some tables at no benefit in return.
Though generally I’ve found modern CPUs work pretty well, even under high paced workloads (from my experiences with working with semi-big data), and have seen many opportunities to utilize wasted unused CPU resources. Regarding this, I like one of Brent Ozar’s mantras that unused server resources (to a degree) is a waste, when you’re paying for something you’re not using.