Result set caching (preview)

Applies to: ✅ SQL analytics endpoint and Warehouse in Microsoft Fabric

Result set caching (preview) is a built-in performance optimization for Fabric Data Warehouse and Lakehouse SQL analytics endpoints that improves read latency.

Result set caching works by persisting the final result sets for applicable SELECT T-SQL queries, so that subsequent runs that "hit" cache will process just the final result set. This can bypass complex compilation and data processing of the original query and return subsequent queries faster.

Data warehousing scenarios typically involve analytical queries that process large amounts of data to produce a relatively small result. For example, a SELECT query that contains multiple joins and performs reads and shuffles on millions of rows of data might result in an aggregation that is only a few rows long. For workloads like reports or dashboards that tend to trigger the same analytical queries repeatedly, the same heavy computation can be triggered multiple times, even though the final result remains the same. Result set caching improves performance in this and similar scenarios.

Automatic management of cache

Result set cache works transparently. Once it's enabled, the cache creation and reuse is applied opportunistically for queries.

Result set caching applies to SELECT T-SQL queries on warehouse tables, shortcuts to OneLake sources, and shortcuts to non-Azure sources. The management of cache is handled automatically, regularly evicting cache as needed.

In addition, as your data changes, result consistency is ensured by invalidating cache created earlier.

Configure result set caching

Result set caching is configurable at the item level.

Once enabled, it can then be disabled at the item level or for individual queries, if needed.

During the preview, result set caching is off by default for all items.

Item-level configuration

Use the ALTER DATABASE SET T-SQL command to enable result set caching for a lakehouse or warehouse. Use the SQL analytics endpoint of a Lakehouse to connect and run T-SQL queries.

ALTER DATABASE <Fabric_item_name>
SET RESULT_SET_CACHING ON;

The setting value can be checked in sys.databases, for example to see the value for the current context in Fabric Warehouse or Lakehouse SQL analytics endpoint:

SELECT name, is_result_set_caching_on 
FROM sys.databases
WHERE database_id = db_id();

To disable result set caching:

ALTER DATABASE <Fabric_item_name>
SET RESULT_SET_CACHING OFF;

Query-level configuration

Once result set caching is enabled on an item, it can be disabled for an individual query.

This can be useful for debugging or A/B testing a query. Disable result set caching for a query by attaching this hint at the end of the SELECT:

OPTION ( USE HINT ('DISABLE_RESULT_SET_CACHE') );

Check result set cache usage

Result set cache usage can be checked in two locations: Message Output and the queryinsights.exec_requests_history system view.

In the message output of a query (visible in the Fabric Query editor or SQL Server Management Studio), the statement "Result set cache was used" will be displayed after query execution if the query was able to use an existing result set cache.

In queryinsights.exec_requests_history, the column result_cache_hit displays a value indicating result set cache usage for each query execution:

• 2: the query used result set cache (cache hit)
• 1: the query created result set cache
• 0: the query wasn't applicable for result set cache creation or usage

For example:

SELECT result_cache_hit, command, *
FROM queryinsights.exec_requests_history
ORDER BY submit_time DESC;

There are various reasons the system can determine that a query isn't eligible for result set cache. Some of the reasons could include:

• The cache no longer exists or was invalidated by a data change, disqualifying it for reuse.
• Query isn't deterministic, and isn't eligible for cache creation.
• Query isn't a SELECT statement.

Related content

• In-memory and disk caching
• Performance guidelines in Fabric Data Warehouse