question

SunilP-5929 avatar image
0 Votes"
SunilP-5929 asked SaurabhSharma-msft commented

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

As per document: https://docs.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 ?

azure-synapse-analyticsazure-cost-management
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

SaurabhSharma-msft avatar image
0 Votes"
SaurabhSharma-msft answered SaurabhSharma-msft commented

Hi @SunilP-5929 ,
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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@sunilp-5929 Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.

Thanks
Saurabh

1 Vote 1 ·