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

Sunil P 136 Reputation points
2021-08-10T10:34:12.187+00:00

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 ?

Azure Cost Management
Azure Cost Management
A Microsoft offering that enables tracking of cloud usage and expenditures for Azure and other cloud providers.
2,791 questions
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
5,060 questions
0 comments No comments
{count} votes

Accepted answer
  1. Saurabh Sharma 23,821 Reputation points Microsoft Employee
    2021-08-11T18:59:58.18+00:00

    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


0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.