Result Set Caching for Microsoft Fabric Data Warehouse (Preview)
Result Set Caching is now available in preview for Microsoft Fabric Data Warehouse and Lakehouse SQL analytics endpoint. This performance optimization works transparently to cache the results of eligible T-SQL queries. When the same query is issued again, it directly retrieves the stored result, instead of recompiling and recomputing the original query. This operation drastically cuts execution time for complex queries. The cache is then automatically managed on the user’s behalf. This lightweight performance boost is most beneficial for workloads like reports, that issue many repetitive T-SQL queries to the DW and SQL analytics endpoint.
How to use Result Set Caching (Preview)
Result Set Caching (Preview) is easy to enable for your artifact – simply run:
ALTER DATABASE <YourArtifactName> SET RESULT_SET_CACHING ON;You can then confirm that Result Set Caching is ON for a particular artifact with:
SELECT name, is_result_set_caching_on FROM sys.databases; Once enabled, Result Set Caching will be automatically created and applied wherever possible for T-SQL queries to that Data Warehouse or Lakehouse SQL analytics endpoint.
Checking for a Result Set Cache hit
If you’re interested in knowing when a query has hit Result Set Cache, you can check the Info Message output (from either SSMS, Fabric UI, or capturing the Info Message output when running queries programmatically):
In addition, you can use the queryinsights.exec_requests_history view to see the status of Result Set Cache for a past query.
SELECT distributed_statement_id, result_cache_hit, command
FROM queryinsights.exec_requests_history
WHERE distributed_statement_id = <'Your_Query_Distributed_Statement_ID'>
ORDER BY submit_time DESC;In this column (result_cache_hit), you will see:
- 2 if the query applied Result Set Cache.
- 1 if the query created Result Set Cache (i.e. did not yet leverage the cache).
- 0 if the query was not applicable for Result Set Cache creation or usage.
Conclusion
When it comes to options for improving performance, Result Set Caching is a no-brainer: it’s easy to enable, requires no tuning, and opportunistically applies whenever it can. We hope you give this hands-off optimization a try. For more information, check out all of our rich caching capabilities at aka.ms/FabricDWResultSetCaching.
