This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
How to analyze compression options for Azure SQL Database?
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.)
asked 2019-01-04 by Mark 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.
answered 2019-01-04 by Andy Mallon