How to check materialized view data storage and maintenance cost in Azure Synapse?

Question

As per document: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/performance-tuning-materialized-views, there would be some cost for the materialized view storage and maintenance. How to check these cost breakup in Azure Portal - Cost analysis ?

Answer 1

Hi @Sunil P ,
Thanks for using Microsoft Q&A !!

Unfortunately, there is not direct answer to this ask as I am not sure if we can fully quantify this as this is very much implementation specific. Having MV greatly helps query performance and if designed correctly there is a wide range of user queries that could benefit from MV.
So, there are 2 aspects to the “cost” angle:

• Refreshing MVs as data gets ingested into base table - This is dependent on the number of MVs that need to be refreshed and amount of changes that happen in the base table (s). Given that MV can be built using one or multiple base tables (joins), I am not sure we can come up with a specific formula here. You may have to experiment with this and tries to see how your typical loading process performs w/ and w/o MVs being present.
• Cost of storage - While there is additional storage used when MVs are deployed, this should really not be a concern as storage prices significantly got reduced in recent times. In addition, MVs contain aggregated data sets so amount of data stored in MV is proportionally smaller compared to data stored in base table(s).

So, you may have to experiment and see system behavior to get. But, in general, query performance greatly outweighs any of the above (if MVs are designed correctly).

Additionally, you can provide your feedback at Synapse uservoice. Please let me know if you have any additional questions.

Thanks
Saurabh