Result set cache is a performance feature for Azure Synapse dedicated SQL pools that remembers the output of eligible queries. When the same query is run again and the underlying data has not changed, Synapse can return the cached result instead of scanning and computing everything again. It is helpful for repeated dashboard, reporting, and analyst queries. It is not a general data cache for every workload, and it is not supported in Synapse serverless SQL pool.
result set caching, Synapse result set cache, SET RESULT_SET_CACHING, cached query results, dedicated SQL pool result cache
Difficulty
intermediate
CLI mappings
7
Last verified
2026-05-22
Microsoft Learn
Microsoft Learn describes result set caching in Azure Synapse dedicated SQL pool as a feature that stores query results in the user database for reuse. When a repeated query can use cached results, it avoids recomputation, reduces compute use, and does not consume concurrency slots.
In Azure architecture, result set cache belongs to the Synapse dedicated SQL pool query engine and user database, not to Spark, serverless SQL, or Azure Cache for Redis. It can be enabled at the database level and controlled at the session level with Transact-SQL. Cached results are used only for eligible queries, respect data-access permissions, and are invalidated when underlying data changes. Operators combine it with workload management, statistics, materialized views, distribution design, monitoring, and DBCC cleanup commands to tune repeated analytical workloads.
Why it matters
Result set cache matters because analytics users often run the same expensive queries repeatedly: dashboard refreshes, month-end reports, executive scorecards, exploratory filters, and validation checks. Without caching, each run can consume compute, concurrency slots, and user patience even when the answer has not changed. In Synapse dedicated SQL pool, cached results can reduce compute work and avoid concurrency pressure, which helps busy reporting windows. It also gives architects another option before scaling up DWUs or redesigning every query. The tradeoff is correctness and governance: teams must understand which queries are eligible, when cache is invalidated, and when security rules require disabling it.
⌁
Where you see it
Signals, screens, and Azure surfaces where this term usually becomes operational.
Signal 01
In Synapse dedicated SQL pool settings and T-SQL sessions, result set caching appears through database options, SET RESULT_SET_CACHING, and query behavior. for repeated reports.
Signal 02
In performance investigations, it appears when repeated dashboard queries return faster, use fewer resources, and avoid consuming dedicated SQL pool concurrency slots. during peak reporting periods.
Signal 03
In SQL administration scripts, it appears with cache-size checks, DBCC DROPRESULTSETCACHE cleanup, database-level enablement, and session-level disablement for sensitive queries. before administrator cleanup actions. and performance tuning.
✦
When this becomes relevant
Specific situations where this term helps solve real Azure design, operations, migration, security, reliability, cost, or governance problems.
Speed up repeated executive dashboards where many viewers run the same Synapse dedicated SQL pool query against stable reporting data.
Reduce concurrency pressure during finance close by serving unchanged report results without consuming dedicated SQL pool concurrency slots.
Avoid unnecessary DWU scale-ups when the bottleneck is repetitive report recomputation rather than genuinely new analytical work.
Disable caching for sensitive sessions that use cryptographic functions or require every query to recompute against current data.
Clear cached results after major data correction or access-control review so users do not rely on outdated performance assumptions.
◆
Real-world case studies
Different enterprise-style examples that show the term being used to hit measurable objectives.
Case study 01
Airline revenue team accelerates month-end dashboards
Scenario, objectives, solution, measured impact, and takeaway.
📌Scenario
An airline revenue-management group refreshed the same Synapse dedicated SQL pool dashboards hundreds of times during month-end close. Analysts asked for a DWU scale-up after queries queued for minutes.
🎯Business/Technical Objectives
Reduce repeated dashboard query time without buying unnecessary capacity.
Avoid concurrency pressure during executive reporting windows.
Keep cache behavior transparent to data and finance teams.
Create a cleanup process after major fare-data corrections.
✅Solution Using Result set cache
The data platform team evaluated result set cache before scaling the dedicated SQL pool. They confirmed the workload ran on dedicated SQL pool, identified stable dashboard queries, and enabled database-level result set caching during the close cycle. Session-level settings were documented for analysts who needed fresh recomputation. Operators used SQL checks for cache size and query behavior, while Azure CLI exported workspace, pool SKU, diagnostic settings, and firewall evidence for the tuning record. A DBCC DROPRESULTSETCACHE runbook was created for post-correction cleanup, and dashboards were reviewed to keep SQL text consistent across refreshes.
📈Results & Business Impact
Median dashboard refresh time dropped from 96 seconds to 14 seconds for repeated close reports.
Concurrency waits during the peak finance window fell by 61 percent.
A planned DWU scale-up was avoided, saving an estimated $18,000 for the quarter.
Finance users received a documented cache-miss plan for data-correction nights.
💡Key Takeaway for Glossary Readers
Result set cache can solve repeated reporting pain before teams spend money scaling a dedicated SQL pool.
Scenario, objectives, solution, measured impact, and takeaway.
📌Scenario
A genomics consortium used Synapse dedicated SQL pool for cohort discovery across de-identified study data. Review committees repeatedly ran the same eligibility queries before approving research access.
🎯Business/Technical Objectives
Shorten repeated cohort-query review sessions.
Preserve permission boundaries for sensitive study data.
Disable caching where cryptographic functions were required.
Document who could clear cache entries before data refreshes.
✅Solution Using Result set cache
The analytics engineering team introduced result set cache for approved repeated cohort queries. They verified that users could access cached results only with matching data permissions and separated sessions that used cryptographic functions from cache-enabled reporting sessions. SQL scripts checked database and session caching settings, while CLI inventory confirmed the Synapse workspace, dedicated pool, private endpoint, and diagnostic configuration. DB_OWNER membership for cache cleanup was restricted to two database administrators. Before each monthly data refresh, the team cleared cached results and notified committees that first-run queries would be slower until cache warmed again.
📈Results & Business Impact
Average committee query review time fell from 43 minutes to 16 minutes.
No cached result was accessible to reviewers lacking the underlying study permissions.
Cache-disabled cryptographic review sessions passed security validation without workflow redesign.
Data refresh complaints decreased because users were warned about expected cache misses.
💡Key Takeaway for Glossary Readers
Result set cache is useful in sensitive analytics only when permission, cryptography, and cleanup rules are explicit.
Case study 03
City budget office reduces report recomputation
Scenario, objectives, solution, measured impact, and takeaway.
📌Scenario
A city budget office published daily spending dashboards from a Synapse dedicated SQL pool. Morning refresh traffic from departments repeatedly recomputed the same summarized budget queries.
🎯Business/Technical Objectives
Improve dashboard response time during the 8 a.m. usage spike.
Reduce pressure on the dedicated SQL pool without delaying data loads.
Keep cache invalidation aligned with nightly budget updates.
Give database administrators a safe manual cleanup path.
✅Solution Using Result set cache
The municipal data team enabled result set caching for the reporting database after confirming the dashboards used stable SQL against nightly loaded data. They documented that cache would be invalidated after underlying data changed and scheduled data-load completion before office hours. Azure CLI checks captured workspace, SQL pool state, SKU, and diagnostic settings, while SQL scripts measured cache size, cache-enabled sessions, and query duration. DBCC DROPRESULTSETCACHE was reserved for database administrators after emergency corrections. Dashboard developers standardized query text so repeated department views could reuse cached results instead of creating accidental misses.
📈Results & Business Impact
Morning p95 dashboard response time improved from 74 seconds to 18 seconds.
Dedicated SQL pool CPU pressure during the spike dropped by 37 percent.
Departments stopped exporting stale spreadsheet copies because the portal became responsive.
Database administrators cleared cache twice after corrections, with no unplanned reporting outage.
💡Key Takeaway for Glossary Readers
Result set cache helps public-sector reporting teams keep repeated dashboards fast without masking data-refresh discipline.
Why use Azure CLI for this?
Azure CLI does not replace the Transact-SQL commands that enable, inspect, or clear result set caching, but it is still useful around the feature. I use CLI to inventory Synapse workspaces, dedicated SQL pools, SKUs, pause state, private endpoints, firewall rules, and diagnostic settings before tuning a reporting workload. CLI also helps export configuration and cost context when teams claim they need more DWU capacity. The actual cache setting is usually checked through SQL, while CLI proves the surrounding Azure resource state. That separation prevents engineers from debugging query behavior without knowing which pool, region, or network path they are using.
CLI use cases
List Synapse workspaces and dedicated SQL pools before investigating which environment has result set caching enabled through SQL.
Check SQL pool SKU, state, and resource group when reporting teams request more capacity due to repeated slow queries.
Export diagnostic settings and firewall rules to confirm query-performance evidence can be collected from the right workspace.
Pause or resume the dedicated SQL pool deliberately after understanding whether reporting users depend on cached result behavior.
Combine CLI inventory with SQL cache-size and DBCC checks to build a complete performance-tuning runbook.
Before you run CLI
Confirm tenant, subscription, resource group, Synapse workspace, dedicated SQL pool name, region, permissions, and intended output format.
Remember that the exact cache controls are Transact-SQL commands, so CLI checks resource context rather than toggling cache directly.
Check firewall, private endpoint, SQL permissions, DB_OWNER requirements for cache cleanup, cost risk, and whether the pool is paused.
What output tells you
Workspace and pool output shows the SQL pool name, location, SKU, state, and resource IDs needed before running SQL cache checks.
Networking output indicates whether analysts, automation, or monitoring tools can reach the dedicated SQL endpoint to inspect cache behavior.
Diagnostic settings show whether query performance, failures, and resource pressure can be correlated with result set cache use.
Mapped Azure CLI commands
Synapse dedicated SQL pool context CLI commands
adjacent-operational
az synapse workspace show --name <workspace> --resource-group <resource-group>
az synapse workspacediscoverAnalytics
az synapse sql pool list --workspace-name <workspace> --resource-group <resource-group>
az synapse sql pooldiscoverAnalytics
az synapse sql pool show --name <sql-pool> --workspace-name <workspace> --resource-group <resource-group>
az synapse sql pooldiscoverAnalytics
az synapse sql pool resume --name <sql-pool> --workspace-name <workspace> --resource-group <resource-group>
az synapse sql pooloperateAnalytics
az synapse sql pool pause --name <sql-pool> --workspace-name <workspace> --resource-group <resource-group>
az synapse sql pooloperateAnalytics
az monitor diagnostic-settings list --resource <sql-pool-resource-id>
az monitor diagnostic-settingsdiscoverAnalytics
az network private-endpoint list --resource-group <resource-group>
az network private-endpointdiscoverAnalytics
Architecture context
Architecturally, result set cache is a tactical accelerator for repeated analytical reads in a dedicated SQL pool. It should be considered alongside materialized views, distribution keys, statistics, workload groups, data freshness requirements, and dashboard design. The best fit is stable reporting data where many users ask the same question repeatedly. It is a poor fit for constantly changing data, highly personalized queries, cryptographic functions that require disabling cache, or workloads that must prove every result was recomputed. Architects should document cache eligibility, invalidation expectations, cleanup authority, and whether session-level settings are allowed for analysts or only for controlled workloads. clearly.
Security
Security is direct because cached results still represent data users queried. Microsoft Learn states that users can access cached results only when they have the same data access permissions as the user who created the cached results. Even so, operators must consider row-level security, dynamic data masking, encryption-related functions, and sensitive reporting patterns. Result set caching should not be used with DECRYPTBYKEY, and some security features can disable or limit caching behavior. DBCC cleanup requires privileged access. Treat cache administration as database governance work: restrict who enables it, who clears it, and how cache behavior is documented for regulated data.
Cost
Cost impact can be positive because cached result reuse reduces compute work for repeated queries and may delay a dedicated SQL pool scale-up. During busy reporting cycles, avoiding recomputation can reduce DWU pressure and analyst wait time. There is also storage overhead because cached results live in the user database, and poor governance can preserve unnecessary cached data until invalidation or cleanup. The indirect cost risk is misusing cache to hide bad modeling, poor distribution, stale dashboards, or oversized reports. FinOps teams should compare cache hit behavior, query frequency, pool scale, and reporting value before buying more capacity. in budgeting discussions.
Reliability
Reliability impact is indirect. Result set cache can make repeated reports more stable under load because cached queries avoid recomputation and do not consume concurrency slots, but it does not protect against workspace outages, bad data loads, or failed pipelines. Cache entries are invalidated when underlying data changes, and turning off caching can remove cached results. If users expect cached speed during month-end close, operators need a plan for cache misses, data refresh timing, and DBCC cleanup side effects. Reliability also depends on dedicated SQL pool availability, pause and resume behavior, firewall access, and consistent query text from dashboard tools.
Performance
Performance is the main reason to use result set cache. Eligible repeated queries can return from persisted cached results instead of scanning tables, joining data, and consuming concurrency slots. That can dramatically improve dashboard refresh time and reduce queueing during peak analytical usage. Performance still depends on eligibility, unchanged underlying data, query text, permissions, and database or session settings. Cache misses can surprise users if data refreshes invalidate results or dashboard tools generate slightly different SQL. Operators should measure query duration with and without caching, cache size, concurrency waits, and whether materialized views or query tuning would provide a stronger long-term fix.
Operations
Operators manage result set cache with a mix of SQL and Azure resource checks. They verify whether caching is enabled for the database or session, inspect cache size, clear cache with DBCC when needed, and monitor query latency, concurrency, and resource utilization. In Azure CLI, they check the Synapse workspace, SQL pool state, SKU, network rules, and diagnostic settings before changing tuning assumptions. Runbooks should state who can enable caching, when it must be disabled, how to clear stale or oversized cache, and what dashboards depend on it. Include SQL evidence in performance reviews. during scheduled tuning reviews. and reviews.
Common mistakes
Trying to use result set caching in Synapse serverless SQL pool, where the documented feature does not apply.
Assuming all repeated queries are cached while ignoring eligibility rules, data changes, session settings, permissions, or query text differences.
Leaving cache enabled for workloads involving sensitive cryptographic functions or scenarios where recomputation is required for assurance.