🏭🗒️Microsoft Fabric: Result Set Caching in SQL Analytics Endpoints [Notes] 🆕

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 21-Jun-2025

[Microsoft Fabric] Result Set Caching in SQL Analytics Endpoints

• {def} built-in performance optimization for Warehouse and Lakehouse that improves read latency [1]
• persists the final result sets for applicable SELECT T-SQL queries
• subsequent runs that "hit" cache will process just the final result set
• can bypass complex compilation and data processing of the original query[1]
• ⇐ returns subsequent queries faster [1]
• the cache creation and reuse is applied opportunistically for queries
• works on
• warehouse tables
• shortcuts to OneLake sources
• shortcuts to non-Azure sources
• the management of cache is handled automatically [1]
• regularly evicts cache as needed
• as data changes, result consistency is ensured by invalidating cache created earlier [1]
• {operation} enable setting
• via ALTER DATABASE <database_name> SET RESULT_SET_CACHING ON
• {operation} validate setting
• via SELECT name, is_result_set_caching_on FROM sys.databases
• {operation} configure setting
• configurable at item level
• once enabled, it can then be disabled 
• at the item level
• for individual queries
• e.g. debugging or A/B testing a query
• via OPTION ( USE HINT ('DISABLE_RESULT_SET_CACHE') 
• {default} during the preview, result set caching is off for all items [1]
• [monitoring] 
• via Message Output
• applicable to Fabric Query editor, SSMS
• the statement "Result set cache was used" is displayed after query execution if the query was able to use an existing result set cache
• via queryinsights.exec_requests_history system view
• result_cache_hit displays indicates result set cache usage for each query execution [1]
• {value} 2: the query used result set cache (cache hit)
• {value} 1: the query created result set cache
• {value} 0: the query wasn't applicable for result set cache creation or usage [1]
• {reason} the cache no longer exists
• {reason} the cache was invalidated by a data change, disqualifying it for reuse [1]
• {reason} query isn't deterministic
• isn't eligible for cache creation [1]
• {reason} query isn't a SELECT statement
• [warehousing] 
• {scenario} analytical queries that process large amounts of data to produce a relatively small result [1]
• {scenario} workloads that trigger the same analytical queries repeatedly [1]
• the same heavy computation can be triggered multiple times, even though the final result remains the same [1]

Previous Post <<||>> Next Post

References:

[1] Microsoft Learn (2025) Result set caching (preview) [link]

[2] Microsoft Fabric Update Blog (2025) Result Set Caching for Microsoft Fabric Data Warehouse (Preview) [link|aka]

[3] Microsoft Learn (2025)  [link]

Resources:
[R1] 

Acronyms:

MF - Microsoft Fabric

SSMS - SQL Server Management Studio