Monitor materialized views

Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer

Monitor the materialized view's health in the following ways:

• Monitor materialized view metrics in your Microsoft Fabric workspace. Use the materialized view age metric, MaterializedViewAgeSeconds as the primary metric to monitor the freshness of the view. For more information, see Enable monitoring in your workspace.

• Monitor the IsHealthy property using .show materialized-view.

• Check for failures using .show materialized-view failures.

Note

Materialization never skips any data, even if there are constant failures. The view is always guaranteed to return the most up-to-date snapshot of the query, based on all records in the source table. Constant failures significantly degrade query performance, but don't cause incorrect results in view queries.

Troubleshooting unhealthy materialized views

If the MaterializedViewAge metric constantly increases, and the MaterializedViewHealth metric shows that the view is unhealthy, follow these recommendations to identify the root cause:

• Check if there are failures during the materialization process using .show materialized-view failures.

  • If the error is permanent, the system automatically disables the materialized view. To check if it's disabled, use the .show materialized-view command and see if the value in the IsEnabled column is false. Then check the Journal for the disabled event with the .show journal command. An example of a permanent failure is a source table schema change that makes it incompatible with the materialized view. For more information, see .create materialized-view command.
  • If the failure is transient, the system automatically retries the operation. However, the failure can delay the materialization and increase the age of the materialized view. This type of failure occurs, for example, when hitting memory limits or with a query time-out. See the following recommendations for more ways to troubleshoot transient failures.

• Analyze the materialization process using the .show commands-and-queries command. Replace Databasename and ViewName to filter for a specific view:

  .show commands-and-queries 
  | where Database  == "DatabaseName" and ClientActivityId startswith "DN.MaterializedViews;ViewName;"
  

  • Check the memory consumption in the MemoryPeak column to identify any operations that failed due to hitting memory limits, such as, runaway queries. By default, the materialization process is limited to a 15-GB memory peak per node. If the queries or commands executed during the materialization process exceed this value, the materialization fails due to memory limits. To increase the memory peak per node, alter the $materialized-views workload group. The following example alters the materialized views workload group to use a maximum of 64-GB memory peak per node during materialization:

    .alter-merge workload_group ['$materialized-views'] ```
    {
      "RequestLimitsPolicy": {
        "MaxMemoryPerQueryPerNode": {
          "Value": 68719241216
        }
      }
    } 
    

    Note

    MaxMemoryPerQueryPerNode can't exceed 50% of the total memory available on each node.

  • Check if the materialization process is hitting cold cache. The following example shows cache statistics over the past day for the materialized view, ViewName:

    .show commands-and-queries 
    | where ClientActivityId startswith "DN.MaterializedViews;ViewName"
    | where StartedOn > ago(1d)
    | extend HotCacheHits = tolong(CacheStatistics.Shards.Hot.HitBytes), 
             HotCacheMisses = tolong(CacheStatistics.Shards.Hot.MissBytes), 
             HotCacheRetrieved = tolong(CacheStatistics.Shards.Hot.RetrieveBytes), 
             ColdCacheHits = tolong(CacheStatistics.Shards.Cold.HitBytes), 
             ColdCacheMisses = tolong(CacheStatistics.Shards.Cold.MissBytes), 
             ColdCacheRetrieved = tolong(CacheStatistics.Shards.Cold.RetrieveBytes)
    | summarize HotCacheHits = format_bytes(sum(HotCacheHits)), 
                HotCacheMisses = format_bytes(sum(HotCacheMisses)),
                HotCacheRetrieved = format_bytes(sum(HotCacheRetrieved)), 
                ColdCacheHits =format_bytes(sum(ColdCacheHits)), 
                ColdCacheMisses = format_bytes(sum(ColdCacheMisses)),
                ColdCacheRetrieved = format_bytes(sum(ColdCacheRetrieved))
    

    Output

    HotCacheHits	HotCacheMisses	HotCacheRetrieved	ColdCacheHits	ColdCacheMisses	ColdCacheRetrieved
    26 GB	0 Bytes	0 Bytes	1 GB	0 Bytes	866 MB

    • If the view isn’t fully in the hot cache, materialization can experience disk misses, significantly slowing down the process.

    • Increasing the caching policy for the materialized view helps avoid cache misses. For more information, see hot and cold cache and caching policy and .alter materialized-view policy caching command.

  • Check if the materialization is scanning old records by checking the ScannedExtentsStatistics with the .show queries command. If the number of scanned extents is high and the MinDataScannedTime is old, the materialization cycle needs to scan all, or most, of the materialized part of the view. The scan is needed to find intersections with the delta. For more information about the delta and the materialized part, see How materialized views work. The following recommendations provide ways to reduce the amount of data scanned in materialized cycles by minimizing the intersection with the delta.

• If the materialization cycle scans a large amount of data, potentially including cold cache, consider making the following changes to the materialized view definition:

  • Include a datetime group-by key in the view definition. This can significantly reduce the amount of data scanned, as long as there is no late arriving data in this column. For more information, see Performance tips. You need to create a new materialized view since updates to group-by keys aren't supported.
  • Use a lookback as part of the view definition. For more information, see .create materialized view supported properties.

• Check whether there's enough ingestion capacity by verifying if the MaterializedViewResult metric shows InsufficientCapacity values. You can increase ingestion capacity by scaling the available resources.

• If the materialized view is still unhealthy, then the service doesn't have sufficient capacity or resources to materialize all the data on time. Consider the following options:

  • Scale out the Eventhouse to provide it with more resources to accommodate materialized views. For more information, see Enable minimum consumption.
  • Divide the materialized view into several smaller views, each covering a subset of the data. For instance, you can split them based on a high cardinality key from the materialized view's group-by keys. All views are based on the same source table, and each view filters by SourceTable | where hash(key, number_of_views) == i, where i is part of the set {0,1,…,number_of_views-1}. Then, you can define a stored function that unions all the smaller materialized views. Use this function in queries to access the combined data.

  While splitting the view might increase CPU usage, it reduces the memory peak in materialization cycles. Reducing the memory peak can help if the single view is failing due to memory limits.

MaterializedViewResult metric

The MaterializedViewResult metric provides information about the result of a materialization cycle and can be used to identify issues in the materialized view health status. The metric includes the Database and MaterializedViewName and a Result dimension.

The Result dimension can have one of the following values:

• Success: The materialization completed successfully.

• SourceTableNotFound: The source table of the materialized view was dropped, so the materialized view is disabled automatically.

• SourceTableSchemaChange: The schema of the source table changed in a way that isn’t compatible with the materialized view definition. Since the materialized view query no longer matches the materialized view schema, the materialized view is disabled automatically.

• InsufficientCapacity: The instance doesn't have sufficient capacity to materialize the materialized view, due to a lack of ingestion capacity. While insufficient capacity failures can be transient, if they reoccur often, try scaling out the instance or increasing capacity. For more information, see Plan your capacity size.

• InsufficientResources: The database doesn't have sufficient resources (CPU/memory) to materialize the materialized view. While insufficient resource errors might be transient, if they reoccur often, try scaling up or scaling out. For more ideas, see Troubleshooting unhealthy materialized views.

Materialized views in follower databases

Materialized views can be defined in follower databases. However, the monitoring of these materialized views should be based on the leader database, where the materialized view is defined. Specifically:

• The .show materialized-view failures command only works in the leader database.

Track resource consumption

Materialized views resource consumption: the resources consumed by the materialized views materialization process can be tracked using the .show commands-and-queries command. Filter the records for a specific view using the following (replace DatabaseName and ViewName):

.show commands-and-queries 
| where Database  == "DatabaseName" and ClientActivityId startswith "DN.MaterializedViews;ViewName;"

Related content

• Materialized views
• Materialized views use cases