Answer: Cache hit percentage

## Analysis of the Performance Issue The scenario describes a situation where **commonly used queries** are experiencing slow performance, while **infrequently used queries** show no performance degradation. This pattern strongly suggests an issue with the **adaptive cache** in Azure Synapse Analytics dedicated SQL pools. ## Why Cache Hit Percentage is the Correct Metric **Cache Hit Percentage** directly measures how effectively the workload is leveraging the built-in cache: - **High cache hit percentage** indicates that queries are efficiently retrieving data from cache rather than from storage - **Low cache hit percentage** suggests that frequently used queries are not benefiting from cached results, forcing them to read from storage each time - This metric specifically addresses the pattern described: commonly used queries should have high cache utilization, but if they're not, performance will degrade ## Why Other Metrics Are Less Suitable - **DWU Percentage**: This measures overall resource utilization but doesn't specifically indicate cache performance issues. High DWU percentage could indicate many different resource constraints, not specifically cache-related problems. - **DWU Limit**: This represents the maximum capacity of the data warehouse, not current utilization or cache effectiveness. - **Data IO Percentage**: While related to storage operations, this metric doesn't distinguish between cache hits and misses, making it less specific for identifying cache-related performance issues. ## Best Practice Recommendation When troubleshooting slow performance for frequently used queries in Azure Synapse Analytics, **Cache Hit Percentage** should be the primary metric to investigate first, as it directly measures whether the adaptive cache is being effectively utilized. A low cache hit percentage for commonly executed queries indicates that the system is not benefiting from cached results, leading to unnecessary storage operations and degraded performance.

Author: LeetQuiz Editorial Team