The short answer
In executive dashboard environments, dozens of concurrent business analysts frequently execute identical or nearly identical queries throughout the morning (such as daily sales summaries or quarterly revenue metrics). Recomputing identical aggregations across billions of rows wastes compute resources and increases query queues.
Synapse Dedicated SQL Pool provides Result Set Caching.
How Result Set Caching works
When Result Set Caching is enabled on the database, the control node stores the final result set of executed queries in an internal memory cache.
-- Enable result set caching for the dedicated pool
ALTER DATABASE my_dedicated_pool
SET RESULT_SET_CACHING ON;When another user submits an identical query:
- The control node checks the cache.
- If a match is found and underlying tables have not changed, the control node returns the cached result immediately.
- Compute nodes and the 60 distributions are completely bypassed. Query execution time drops from 30 seconds to under 20 milliseconds.
Cache invalidation rules
The cache is managed automatically by Synapse. Any change to the underlying tables (including INSERT, UPDATE, DELETE, COPY INTO, or DDL alterations) automatically invalidates the cached results for all queries referencing those tables.
Result set caching does not cache queries that contain non-deterministic SQL functions (like GETDATE(), NEWID(), or RAND()), row-level security , or queries returning result sets larger than one gigabyte.
It is particularly effective for static reporting tables that refresh once nightly during batch windows, enabling hundreds of Power BI users to query dashboards without consuming DWU compute.