question

For OLAP databases the compresison is mostly a good option as the compression overhead for the processor isn't so high and you will gain performance as SQL Server will have to read much less pages from IO subsystem. And there are mosly read operations peformed on the OLAP databases (except the population which is mostly done in batches out of business hours) which process a large amount of rows. For OLTP it depends and will probably need some testing. If the CPU overhead caused by the OLTP load will be too high and will slow down the operations, then you should turn off the compression. If the load is not so high, you can keep the compression on. What more, if you have the tables partitioned, you can enable the compression for historical partitions and turn off the compression on actural active partitions.

It's been my experience that most systems (not all) have plenty of CPU power to spare. This means that the slight overhead of cost from the CPU is completely overwhelmed by the performance benefits you gain by having your pages compressed both on disk, and in memory. That's right, when a page is read from disk, it's not decompressed into memory. It stays compressed. This means you get better disk i/o through compression (fewer pages being read & written) but you also get better memory management (again, fewer pages being moved into memory). Compression is an excellent resource. If you have the Enterprise version of SQL Server, I strongly recommend it's use. But, be sure you're not already stressed on CPU on your server, as Pavel recommends.