Monitor materialized views

Switch services using the Version drop-down list. Learn more about navigation.
Applies to: ✅ Azure Data Explorer

Monitor the health of materialized views in the following ways:

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

• Inspect extent, hot-cache, size, and effective policy details by using .show materialized-view details.

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

Troubleshooting unhealthy materialized views

If the MaterializedViewAgeSeconds metric constantly increases and the MaterializedViewHealth metric shows that the view is unhealthy, use the following symptoms to identify the root cause.

.show capacity
| where Resource == "MaterializedView"
| project Resource, Total, Consumed

Materialization failures

Use .show materialized-view failures to inspect failures.

• For a permanent error, the system automatically disables the materialized view. Use .show materialized-view to check whether IsEnabled is false, and use .show journal to find the disabled event. A source table schema change that makes the table incompatible with the materialized view is an example of a permanent error. For more information, see .create materialized-view.
• For a transient error, the system automatically retries the operation. Repeated memory-limit or query-timeout failures delay materialization and increase the materialized view age. For ways to reduce transient failures, see Optimize materialized views.

Materialization exceeds the memory limit

Use .show commands-and-queries to inspect MemoryPeak. Replace DatabaseName and ViewName with your values:

.show commands-and-queries
| where Database == "DatabaseName" and ClientActivityId startswith "DN.MaterializedViews;ViewName;"
| project StartedOn, LastUpdatedOn, Duration, State, FailureReason,
          TotalCpu, MemoryPeak

A failure that reaches the memory limit can appear as a runaway query. Raise the materialization memory limit or reduce the memory required by each cycle. For more information, see Increase the materialization memory limit.

Materialization is hitting cold cache

Use the cache statistics from .show commands-and-queries to determine whether materialization is retrieving data from the cold cache. The following query summarizes cache activity over the past day for 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))

Cold-cache hits or retrieved bytes indicate that materialization is reading data outside the hot cache, which can significantly slow the process. Extend the caching policies to cover the data that materialization scans. For more information, see Adjust caching policies.

Materialization scans old records

Inspect ScannedExtentsStatistics with .show queries. A high number of scanned extents and an old MinDataScannedTime indicate that the cycle is scanning all or most of the materialized part to find intersections with the delta. Reduce the scan by using an appropriate datetime group-by key, lookback period, or caching policy. For more information, see Optimize materialized views.

Not enough ingestion capacity

Recurring InsufficientCapacity values indicate that available ingestion capacity is limiting materialization. Increase the resources available for materialization. For more information, see Increase available resources.

Materialized view remains unhealthy

If the preceding diagnostics don't identify a specific cause, the service might not have sufficient capacity or resources to materialize all data on time. Increase available resources or split a memory-intensive view only after applying less disruptive optimizations. For more information, see Optimize materialized views.

MaterializedViewResult metric

The MaterializedViewResult metric provides the result of each materialization cycle. Use it to identify problems with materialized view health. The metric includes the Database, MaterializedViewName, and Result dimensions.

Materialized views in follower databases

You can define materialized views in follower databases. However, you should monitor these materialized views from the leader database, where you define the materialized view. Specifically:

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

Related content

• Materialized views
• Materialized views use cases
• Materialized views optimization