Microsoft Fabric Result-Set Caching: How to Avoid False Performance and Capacity Confidence

Executive summary

Microsoft Fabric now enables result-set caching by default for Fabric Warehouses and Lakehouse SQL analytics endpoints. For suitable repeated analytical queries, that is valuable: Fabric can return a persisted final result instead of repeating compilation and data processing. But the same feature can make a proof of concept look more representative than it is.

A dashboard query repeated by one developer may hit the result cache and return quickly. The production version may run under row-level security, include a runtime date, return more rows, use a different connection context, or execute after an upstream data change. Those conditions can make it ineligible for cache creation or reuse. A capacity recommendation based on the cached test can therefore understate the work that production must perform.

The answer is not to switch caching off everywhere. It is to govern performance evidence. Enterprises should classify queries, benchmark three distinct execution lanes, test production security personas, link query telemetry to capacity consumption, and approve sizing only when the workload mix meets business service levels under representative concurrency and data change.

This article presents that operating model.

The enterprise problem: a fast test can support the wrong decision

Microsoft lists result-set caching as a September 2026 Fabric Data Warehouse release and documents it as enabled by default. The feature persists the final result of an eligible `SELECT` query; a later qualifying execution can process the final result rather than repeat the original query's full work. Fabric manages the cache opportunistically and invalidates earlier cache entries when referenced data changes.[^1][^2]

That behavior creates two valid but different performance experiences:

• Computed execution: Fabric compiles and processes the query against the relevant data.
• Result-cache reuse: Fabric returns a previously persisted final result for an eligible repeat.

Neither number is dishonest. The governance failure occurs when a team reports one without identifying which path produced it, then uses that number to promise report latency, choose an F capacity, or approve a migration.

The risk is amplified because cache eligibility is not universal. Microsoft documents common disqualifiers including row-level security (RLS), dynamic data masking, runtime constants such as `GETDATE()` or `CURRENT_USER`, time travel, results estimated above 10,000 rows, cross-database queries, some windowed or nondeterministic functions, explicit transactions, and several query-shape conditions. Reuse can also fail after referenced data changes, when permissions are insufficient, when the query runs through a different Warehouse or Lakehouse connection, or after the cached query has gone unused for 24 hours.[^1]

For an executive, the core issue is simple: the fastest observed run may describe only one eligible repeat path, not the production workload.

Representative scenario: the proof of value that cannot survive its security model

*The following is a representative scenario, not a YuniQ customer case or a statement of achieved results.*

A multi-region services company is moving its operational reporting estate to a Fabric Warehouse. Its proof-of-value team loads a production-scale snapshot, builds a management dashboard, and runs the same group of queries repeatedly from one technical account. After the first execution, several dashboard queries become dramatically faster.

The team sizes the target capacity from those tests and presents the response times as the expected user experience. During production validation, three facts appear:

1. Regional managers query through an RLS policy that restricts rows by organizational assignment.
2. The daily operations page uses the current date in generated SQL.
3. An analyst export returns more than 10,000 rows.

Those query classes do not receive the same result-cache benefit as the repeated proof-of-value queries. At the morning peak, secured interactive traffic overlaps ingestion and management reporting. Latency rises and the capacity accumulates more work than the sizing model anticipated.

The caching feature did not fail. The benchmark failed to represent identities, query shapes, concurrency, data change, and the ratio of cache-eligible to cache-ineligible requests.

Root causes and business consequences

Root cause 1: one latency number hides three execution states

Teams often average the first and subsequent executions, or retain only the best run. That merges uncached computation, cache creation, and cache reuse into a number that has no stable operational meaning.

Root cause 2: the test identity differs from the user identity

A broad technical account can execute a query without the security predicates applied to a regional or departmental user. Microsoft explicitly lists queries using RLS, dynamic data masking, or other security features among common result-set cache disqualifiers.[^1] A benchmark that omits real personas may test a faster query path and a different authorization path at the same time.

Root cause 3: a tidy query pack does not resemble production

Real applications vary aliases, output columns, filters, timestamps, connection contexts, and result sizes. Some queries repeat exactly; many do not. A test that loops a small set of identical SQL statements can overstate the production hit rate.

Root cause 4: performance and capacity evidence are separated

Elapsed time alone cannot support a capacity decision. Fabric provides the Capacity Metrics app for item-level capacity consumption and Query Insights for execution details. Microsoft also documents how to correlate a capacity billing interval with requests in `queryinsights.exec_requests_history`.[^3] If those records are not joined into one evidence set, leaders cannot see whether a fast experience is efficient, whether a slow interval is driven by concurrency, or which queries consume the work.

Root cause 5: teams treat managed caching as an application contract

Result-set caching is opportunistic. Microsoft notes that eligibility rules are internally assessed and may be updated. Cache space is also managed automatically.[^1] An architecture may benefit from caching, but a business service-level objective (SLO) should not depend on every execution receiving a hit unless the team has measured and continuously verifies that assumption.

The consequences are material:

• undersized capacity and avoidable throttling during business peaks;
• overpromised dashboard response times;
• migration approvals based on a nonrepresentative proof of value;
• performance regressions when security controls are introduced late;
• incorrect attribution of a fast repeat to table design or query tuning;
• unnecessary scale-up when the underlying problem is query shape, statistics, file layout, or concurrency; and
• loss of confidence when users cannot reproduce the demonstration.

Why common approaches fall short

"Run it several times and take the average"

This obscures the execution path. An average of one computed run and four result-cache hits is neither a computed-execution baseline nor a cache-reuse baseline.

"Disable caching for the whole Warehouse"

That discards a legitimate production optimization and produces a workload unlike the intended operating state. Result-set caching can be controlled at item level, but Microsoft also provides a query hint specifically for debugging and A/B testing.[^1] Use that precision.

"Benchmark as an administrator"

An administrator proves that an administrator can run the query. It does not prove regional, departmental, external, or restricted personas receive correct data or equivalent performance.

"Use Power BI visual load time as the only measure"

End-to-end user timing matters, but it combines semantic model behavior, generated SQL, network time, rendering, concurrency, and Warehouse execution. Without query-level evidence, the team cannot distinguish a result-cache hit from an actual engine improvement or a front-end bottleneck.

"Size from the quiet proof-of-value workspace"

Production combines interactive queries, transformations, ingestion, system activity, and other Fabric workloads on the capacity. Microsoft's Warehouse monitoring guidance points to the Capacity Metrics app, workspace monitoring, Data Warehouse Monitor, Query Insights, and dynamic management views because no single view explains all layers.[^4]

A governed Microsoft Fabric solution

The goal is a repeatable performance evidence pipeline, not a one-time stopwatch exercise.

1. Define a workload contract before testing

For every decision-facing workload, record:

Define p50 as the median user experience and p95 as the duration under which 95% of runs complete. The p95 value is especially useful for exposing tail latency that an average conceals.

2. Run a three-lane benchmark

Use the same representative query set, data snapshot, personas, and concurrency model across three explicitly labelled lanes.

Lane A: computed execution with result-set reuse disabled

Append the documented hint to eligible benchmark queries:

```sql SELECT Region, SUM(NetRevenue) AS NetRevenue FROM dbo.FactService WHERE ServiceDate >= @StartDate GROUP BY Region OPTION (USE HINT ('DISABLE_RESULT_SET_CACHE')); ```

This prevents result-set cache use for that query. It does not create a perfectly cold storage test: Fabric also uses transparent in-memory and SSD caching, which Microsoft manages automatically and does not expose for manual clearing.[^5] Describe this lane accurately as "result-set reuse disabled," not "all caches cold."

Lane A answers: Can the platform meet the SLO when it must process the query rather than reuse a persisted final result?

Lane B: cache creation

Run the query with result-set caching enabled and verify that it creates a cache entry. Microsoft documents `result_cache_hit = 1` in `queryinsights.exec_requests_history` as cache creation.[^1]

Lane B answers: What is the first qualifying experience, and what does it cost under realistic load?

Lane C: cache reuse

Repeat the same query under an eligible context and verify `result_cache_hit = 2`, which Microsoft defines as a cache hit. A value of `0` means result-set caching did not apply.[^1]

Lane C answers: What benefit do stable repeated queries receive, and how often does that path occur in the real workload?

Do not infer a hit from speed alone. Persist the telemetry state with every run.

3. Test the production security matrix

Execute the benchmark using representative Entra identities and groups, with production-equivalent Fabric permissions and SQL controls. Include at least:

• unrestricted operational support;
• a user constrained by RLS;
• a user subject to dynamic data masking or column restrictions;
• a report or application service identity; and
• a deliberately unauthorized identity for negative-path validation.

Fabric Warehouse combines Fabric permissions with granular SQL permissions. RLS applies restrictions in the database tier, and Microsoft documents that it is enforced across applications and reporting platforms that query the Warehouse or SQL analytics endpoint.[^6][^7]

Security acceptance must come before performance acceptance. Never remove required controls to chase a cache hit. Instead, establish the SLO for the secured path and tune or size for that path.

4. Capture a query execution ledger

Query Insights retains historical query information for 30 days and includes execution data, actual query text for appropriately privileged workspace roles, and aggregated views. Its execution history exposes the result-cache state.[^8]

A simple inspection query is:

```sql SELECT submit_time, start_time, end_time, login_name, command, result_cache_hit, allocated_cpu_time_ms, data_scanned_memory_mb, data_scanned_disk_mb, data_scanned_remote_storage_mb FROM queryinsights.exec_requests_history WHERE submit_time >= @WindowStart AND submit_time < @WindowEnd ORDER BY submit_time; ```

Treat the available schema in your tenant as authoritative and validate column availability before automating the extract. Attach business metadata outside the system view: test run ID, journey, persona, query class, expected eligibility, data snapshot, concurrency wave, release version, and SLO result.

The ledger should answer:

• Was this execution a cache creation, cache hit, or nonapplicable run?
• Was the outcome expected for this query class and persona?
• What were p50 and p95 by execution state?
• Did the result reconcile to the expected business value?
• Which release, data state, and capacity conditions produced it?

5. Correlate latency with capacity consumption

Use the Capacity Metrics app to identify peak intervals and the Warehouse items consuming capacity. Microsoft's documented drill-through process then uses the interval timestamps against `queryinsights.exec_requests_history` to identify requests that overlapped the billing window.[^3]

Use three levels of evidence:

```text Capacity Metrics app -> capacity and item-level CU peaks

Workspace monitoring / Warehouse Monitor -> execution concentration, failures, long-running and frequent queries

Query Insights execution ledger -> query, identity, timing, cache state, CPU and scan evidence ```

This design supports better decisions than a cache-hit percentage alone. A high hit rate for a rarely used dashboard may have little business value, while a cache-ineligible secured workflow may dominate the morning peak.

6. Tune the computed path, then benefit from the cached path

Treat Lane A failures as engineering evidence. Review statistics, query shape, data types, ingestion patterns, file layout, data skew, intermediate result size, and concurrency. Microsoft's Warehouse performance guidance explains that the optimizer uses statistics to select and cost plans, recommends batching data modifications, and documents how fragmented small writes can degrade performance until background compaction catches up.[^9]

The decision sequence should be:

1. Verify result correctness and access controls.
2. Make the computed path meet the business SLO where feasible.
3. Measure the incremental benefit of cache reuse.
4. Size for the observed production mix and peak overlap.
5. Re-test when query shape, security policy, data volume, connection path, or Fabric behavior changes.

Caching is then a measurable accelerator, not a hidden dependency.

Phased implementation roadmap

Phase 1: Discover and classify (one to two weeks)

• Inventory decision-critical dashboards, exports, applications, and direct SQL consumers.
• Extract frequent and long-running query shapes from Query Insights.
• Define identity personas, data-change patterns, peak windows, and SLOs.
• Classify each query as expected eligible, expected ineligible, or uncertain.
• Record the current Warehouse and SQL analytics endpoint caching setting.

Exit criterion: business owners and platform owners approve the workload contract and criticality tiers.

Phase 2: Build the benchmark harness (two to three weeks)

• Create production-shaped data and connection paths in a controlled test workspace.
• Implement the three lanes and a run ledger.
• Apply production-equivalent permissions, RLS, masking, and negative-path tests.
• Automate result reconciliation as well as latency measurement.
• Capture baseline Capacity Metrics intervals and query evidence.

Exit criterion: every reported performance number carries an execution state, persona, data snapshot, concurrency profile, and capacity context.

Phase 3: Prove under load (one to two weeks)

• Replay the workload mix at normal, peak, and recovery-after-peak volumes.
• Overlap representative ingestion and transformation jobs.
• Compare p50, p95, failures, cache-state distribution, and CU consumption.
• Tune the computed path and rerun all security personas.
• Model headroom and failure behavior, not only steady-state averages.

Exit criterion: the selected capacity and architecture meet the agreed SLOs and correctness checks under the production-shaped mix.

Phase 4: Release with evidence gates

• Include benchmark regression tests in deployment promotion.
• Require review when SQL, security policy, semantic model, source connection, or data volume materially changes.
• Validate the production endpoint after deployment.
• Retain the signed test report with the release evidence.

Exit criterion: release owners can explain any performance delta by query class, execution state, persona, and capacity interval.

Phase 5: Operate and recalibrate

• Trend the result-cache state distribution by business journey.
• Monitor p95 latency, failures, query volume, and capacity pressure.
• Revisit sizing when the workload mix changes, not merely when total row count grows.
• Sample result correctness after upstream changes and major releases.
• Revalidate assumptions when Microsoft updates eligibility behavior.

Exit criterion: the monthly service review connects user SLOs, cache behavior, engineering changes, and capacity cost.

Governance, security, adoption, and cost considerations

Governance

Assign clear accountability:

Do not allow a screenshot of a fast run to serve as acceptance evidence. Use a versioned result set that can be reproduced.

Security and privacy

Query Insights can expose query text to Admin, Member, and Contributor workspace roles.[^8] Query text may contain filters, identifiers, or business-sensitive logic. Apply least privilege to monitoring access, restrict the benchmark evidence store, avoid embedding secrets or unnecessary personal data in SQL literals, and align retention with organizational policy.

Adoption

Publish two user-facing commitments: the response-time SLO for each critical journey and the freshness SLO for its data. Do not teach users to expect the best cached demonstration time. Train support teams to distinguish a data-freshness incident, an authorization issue, a capacity-pressure event, and a cache-ineligible query.

Cost and capacity

Do not turn a laboratory cache-hit ratio directly into a savings claim. Measure CU consumption under the actual mix. The Capacity Metrics app shows the item-level capacity pattern; Query Insights provides the request evidence needed to explain it.[^3][^4]

Capacity decisions should include:

• secured and unsecured query proportions;
• concurrency at business peaks;
• ingestion and transformation overlap;
• cache creation and cache reuse distributions;
• growth in users, data, and result size;
• recovery from spikes and throttling; and
• operational headroom for releases and incidents.

Success metrics

Track a compact scorecard by business journey and persona:

• p50 and p95 end-to-end latency;
• p50 and p95 Warehouse execution duration;
• percentage of executions with `result_cache_hit` values `0`, `1`, and `2`;
• percentage of cache outcomes that match the expected eligibility classification;
• reconciliation pass rate for business results;
• authorization positive- and negative-test pass rate;
• failure and cancellation rate;
• CU consumption by item during normal and peak windows;
• count of critical journeys meeting both latency and freshness SLOs; and
• performance regressions detected before production.

Set targets from business requirements and measured baselines. An arbitrary enterprise-wide hit-rate target can reward low-value repeats and penalize correctly secured queries that are ineligible by design.

How YuniQ can help

YuniQ's Microsoft Fabric consulting offer maps directly to this problem without requiring caching to become a standalone project.

• Fabric Strategy & Readiness: YuniQ describes current-state workload diagnostics, F-capacity planning, workspace topology, security, governance, operating-model design, and proof-of-concept scoping. Those activities establish the workload contract and evidence standards before sizing.[^10]
• Data Integration & Engineering and OneLake architecture: YuniQ lists Fabric Warehouse implementation, data quality observability, workload optimization, domain workspaces, medallion layers, and reusable business metrics. These are the engineering foundations for a representative benchmark and a tunable computed path.[^10]
• Governance, Security & DevOps: YuniQ states that it implements least-privilege Entra role design, Dev/Test/Prod separation, Git and CI/CD controls, automated monitoring, and capacity threshold alerts. These controls help make identity-aware performance testing and release gates repeatable.[^10]
• Proof of Value: YuniQ's published Proof of Value offer includes production-grade data, a Medallion data product, a certified Direct Lake report, and capacity sizing recommendations. For this use case, the proof should explicitly separate cache states and validate production security personas.[^11]
• Managed Services: YuniQ lists capacity throttling and cost optimization, monitoring, and DevOps release management among its managed services. These capabilities align with ongoing SLO, regression, and capacity review after launch.[^11]

The practical engagement is to select one high-value dashboard or application, capture its real identity and query mix, prove all three execution lanes, and use the resulting evidence to set the production architecture and capacity decision.

Practical next steps

1. Identify one decision-critical Warehouse-backed dashboard with a known peak period.
2. Query recent execution history and label each run as cache creation, cache hit, or nonapplicable.
3. Map the dashboard's production identities, RLS, masking, connection path, and generated query shapes.
4. Re-run its critical journeys through the three benchmark lanes with production-shaped concurrency.
5. Correlate the test window with the Capacity Metrics app and reconcile returned business values.
6. Replace any unlabeled "average response time" in the business case with p50 and p95 results by persona and execution state.
7. Approve capacity only after the secured computed path and observed production mix meet the agreed SLOs.

Result-set caching can make Fabric Warehouse faster. Governance makes that speed interpretable. When every number carries its execution path, identity, data state, and capacity context, leaders can distinguish a genuine production capability from a fortunate repeat and invest accordingly.