Should tiny dimension tables be considered for row or page compression on servers with ample CPU room?

Question

An old Microsoft paper says to consider using ROW compression by default if you have lots of CPU room (emphasis mine).

If row compression results in space savings and the system can accommodate a 10 percent increase in CPU usage, all data should be row-compressed

Today, we have even more CPU space than we did back when the paper was written. Wise men have said that we should consider using PAGE compression everywhere unless we have a compelling reason not to.

This is all good advice and I often see sp_estimate_data_compression_savings agree. However, what should be done with tiny tables that are frequently accessed? For example, I have some extremely small dimension tables. 100 rows at most and very few columns. Because they are so small, the space-saving benefit from any compression is minimal. What is considered the best practice for applying ROW or PAGE compression to such tiny tables on boxes that have a huge amount of free CPU room?

For the purposes of this question, ignore columnstore. We are only talking about old-school rowstore indexes on disk-based tables.

Answer 1

From a performance perspective, no, you should focus on tables where compression savings help you make better use of the hardware your SQL Server has assigned to it.

But from a standards and practices perspective, you should compress everything, ideally using page compression. It sets the culture for creating indexes in general.

If you really wanted to enforce that, you could write a DDL trigger that stops indexes being created without compression.