Answer: Cache used percentage

## Analysis of the Performance Issue The scenario describes a specific performance pattern in Azure Synapse Analytics: - **Frequently executed queries** are experiencing slow performance - **Infrequently used queries** show no performance changes - This pattern suggests the issue is related to **query reuse and caching mechanisms** rather than general resource constraints ## Why Cache Used Percentage (Option B) is the Correct Metric **Cache Used Percentage** directly measures how effectively the result set cache is being utilized in Azure Synapse Analytics. Here's why this is the optimal choice: 1. **Result Set Caching Behavior**: Azure Synapse Analytics automatically caches query results for frequently executed queries. When the same query is run multiple times, it can retrieve results from cache instead of re-executing the entire query. 2. **Performance Pattern Correlation**: The described issue (slow frequent queries, normal infrequent queries) aligns perfectly with cache-related problems: - If the cache is full or inefficiently managed, frequently run queries cannot benefit from cached results - Infrequent queries are less affected because they don't rely on cached results 3. **Direct Problem Identification**: Monitoring cache usage percentage helps identify: - Cache hit/miss ratios - Cache eviction patterns - Whether the cache is being effectively utilized for common workloads ## Why Other Metrics Are Less Suitable - **CPU Percentage (D)**: While important for overall performance, CPU utilization would affect all queries similarly, not just frequently executed ones. High CPU would impact both frequent and infrequent queries. - **Data IO Percentage (C)**: This measures data movement and storage operations. Performance issues related to data IO would typically affect queries based on their complexity and data volume, not their execution frequency. - **Local tempdb Percentage (A)**: This monitors temporary database usage, which is more relevant for complex sorting and joining operations rather than query frequency patterns. ## Recommended Monitoring Approach When monitoring Cache Used Percentage, look for: - Low cache hit rates for frequently executed queries - Rapid cache turnover indicating insufficient cache size - Inconsistent cache performance that correlates with user-reported slowdowns This metric provides the most direct insight into why commonly used queries are performing poorly while less frequent queries remain unaffected.

Author: LeetQuiz Editorial Team