Question
What is the most efficient way to determine whether row or page or columnstore compression (or none) would be most effective for each index in an Azure SQL Database (standard or premium tier) used for OLTP, where sp_estimate_data_compression_savings is not supported?
I realize that whether doing so is good for performance would depend on the frequency and volume of writes for each index. What other considerations should be taken into account?
(I realize that whether to use columnstore for a partitioned index is a complex issue and I'll consider that out of scope for this request.)
Asked2019-01-04byMark Freeman
Answer
Kalen Delaney has created a version of sp_estimate_data_compression_savings specifically for this use case.
Its available for download from the corresponding blog post here.
Answered2019-01-04byAndy Mallon