How to Verify Result Set Cache Usage in Azure Synapse SQL Pools?

DP-203 By islever Editorial Last updated

Short answer

BC Review the sys.dm_pdw_exec_requests dynamic management view in Pool1. Use the Monitor hub in Synapse Studio.

Question

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.
  1. Review the sys.dm_pdw_sql_requests dynamic management view in Pool1.
  2. Review the sys.dm_pdw_exec_requests dynamic management view in Pool1. Correct Answer
  3. Use the Monitor hub in Synapse Studio. Correct Answer
  4. Review the AzureDiagnostics table in la1.
  5. Review the sys.dm_pdw_request_steps dynamic management view in Pool1.

Key Takeaways

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.

Common mistake: 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.

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

These links open the official vendor documentation in a new tab.

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.

Community Votes

BC
59%
BE
41%

59% of anonymous learners picked answer BC. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 10 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 10

  1. jongert 👍 5
    Correct https://learn.microsoft.com/en-us/azure/synapse-analytics/monitoring/how-to-monitor-using-azure-monitor https://learn.microsoft.com/en-us/sql/t-sql/statements/set-result-set-caching-transact-sql?view=azure-sqldw-latest https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-pdw-exec-requests-transact-sql?view=aps-pdw-2016-au7
  2. de_examtopics 👍 2
    A. Review the sys.dm_pdw_sql_requests dynamic management view in Pool1. This view includes information about the SQL requests executed in the data warehouse, including whether the result set cache was used. C. Use the Monitor hub in Synapse Studio. The Monitor hub provides detailed insights and metrics for your Synapse Analytics activities, including the usage of result set caches for queries.
  3. Happynewyear1001 👍 2
    The sys.dm_pdw_exec_requests dynamic management view provides details about currently or recently executed requests, and the Monitor hub in Synapse Studio can offer insights into the query execution and caching.
  4. Alongi 👍 2
    B and E provides information about query caching
  5. yourilam 👍 1
    A. Review the sys.dm_pdw_sql_requests dynamic management view in Pool1. This view provides detailed information about queries executed on the dedicated SQL pool. It includes a result_cache_hit column, which indicates whether the query used the result set cache. A value of 1 indicates that the result set cache was used. D. Review the AzureDiagnostics table in la1. The AzureDiagnostics table in the Log Analytics workspace (la1) contains telemetry data from the dedicated SQL pool, including query execution details. You can query this table to check for indicators of whether the result set cache was used for a specific query.
Show 5 more comments
  1. FortyZa 👍 1
    sys.dm_pdw_request_steps: "Run this to check if a query was executed with a result cache hit or miss" sys.dm_pdw_exec_requests: "Run this for the time taken by result set caching operations for a query" Quoted from https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/performance-tuning-result-set-caching
  2. e56bb91 👍 1
    ChatGPT 4o To identify whether a recently executed query on Pool1 used the result set cache, you can use the following methods: B. Review the sys.dm_pdw_exec_requests dynamic management view in Pool1. D. Review the AzureDiagnostics table in la1.
  3. renan_ineu 👍 1
    I'll go with B and E because they're both mentioned in this documentation from Microsoft: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/performance-tuning-result-set-caching#key-commands
  4. rlnd2000 👍 1
    The Monitor hub in Synapse Studio does not provide information to verify cache utilization.
  5. DixonDavis 👍 1
    I think it should use the dynamic management view for query performance so B and E

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

Which Diagnostic Setting Tracks Query Start and End Times in Synapse SQL Pool?

S-grade

Which Integration Runtime Connects On-Premises Linux Databases to Azure Data Factory?

S-grade

Which Log Analytics table stores ADF pipeline runs in resource-specific mode?

S-grade

Can ARM template export save unpublishable ADF pipelines?

S-grade

← Back to DP-203 Study Guide