How to Verify Result Set Cache Usage in Azure Synapse SQL Pools?
You have a Log Analytics workspace named la1 and an Azure Synapse Analytics dedicated SQL pool named Pool1. Pool1 sends logs to la1. You need to identify whether a recently executed query on Pool1 used the result set cache. What are two ways to achieve the goal? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.
Community Votes
59% of anonymous learners picked answer BC. Votes are pick records left by other test-takers — they are not the verified answer.
Community Insight
Tests knowledge of Synapse monitoring capabilities, specifically that sys.dm_pdw_exec_requests and Synapse Studio's Monitor hub directly expose cache hit status, while other DMVs or Log Analytics tables do not provide this specific metric for recent queries.
This question assesses methods to check if a Synapse Dedicated SQL Pool query utilized the result set cache, with community consensus favoring the Monitor hub and specific dynamic management views. Correct identification relies on understanding Synapse's built-in monitoring tools versus Log Analytics diagnostic logs.
Candidates often select BE (sys.dm_pdw_request_steps) assuming step-level details include cache info, but Microsoft documentation explicitly assigns the result_cache_hit column to sys.dm_pdw_exec_requests. Others mistakenly choose AD, over-indexing on the Log Analytics mention without realizing DMVs and Monitor are the direct answers.
Community Discussion (10 comments)
Comments & Corrections
No comments yet — spotted an error or have a note? Share it below.
Expert Analysis
Why the Answer Is Correct
Both B and C are officially supported methods. The sys.dm_pdw_exec_requests DMV includes a result_cache_hit column that returns 1 for cache hits and 0 for misses, making it ideal for T-SQL-based verification. Synapse Studio’s Monitor hub provides a graphical interface to track query execution details, including cache utilization metrics for recently run queries.Why the Other Options Are Wrong
Option A (sys.dm_pdw_sql_requests) focuses on individual SQL request stages and lacks the cache hit indicator. Option E (sys.dm_pdw_request_steps) tracks granular execution steps like data movement and compute phases, not cache status. Option D (AzureDiagnostics) stores diagnostic logs but requires parsing and is not optimized for immediate verification of recent query cache usage compared to live DMVs or the Monitor hub.Community Comment Notes
Multiple voters initially debated between BC and BE, citing older DW documentation that mentioned step-level queries. However, official MS docs confirm sys.dm_pdw_exec_requests is the authoritative source for cache hits. Several commenters correctly pointed out that while LA receives logs, the Monitor hub and DMV are the intended exam answers for quick verification. Comments [1], [4], and [8] align closely with official guidance.Official Reference
- https://learn.microsoft.com/en-us/sql/t-sql/statements/set-result-set-caching-transact-sql
- https://learn.microsoft.com/en-us/azure/synapse-analytics/monitoring/how-to-monitor-using-azure-monitor
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-pdw-exec-requests-transact-sql
Exam Strategy
When Synapse monitoring questions mention Log Analytics, don't automatically default to it; prioritize native tools like DMVs and Synapse Studio Monitor for real-time or recent query analysis. Always cross-reference official Microsoft documentation for exact column names in system views, as exam writers frequently test subtle differences between similar DMVs.