We recently announced the release of Result Set caching for Azure Data Warehouse at Build and within our May 2019 Release notes for Azure DW, this new feature allows you to cache the final return results for user queries.
The feature is only Available with Azure DW Gen 2 and not for Azure SQL DB or APS/PDW.
The feature will not cache intermediate result sets and will not be used during CTAS or CRUD operations.
The cached results will be stored within the control node database and will consume a maximum of 1TB storage.
The results will be evicted from the cache if not referenced in the last 48 hours.
Queries which are a cache hit will simply return the data from the result cache instead of running the full plan creation and retrieving the data across all of the distributions.
In the event of the query being a Cache miss, it will populate a result set table in the cache DB synchronously for the next execution of the statement.
Only pure select statements are cached against non-system and non-external objects which have no run time constraints or row level security predicates.
Enabling the Feature
To enable the feature, you would require the following permissions
- Server Level Principal Login
- Member of the dbmanager database role
Once the relevant permissions have been granted you will enable the feature from the master database
Alter Database <DBNAME> Set Result_Set_Caching ON
To verify that the feature is enabled you can run the following statement
Select is_result_set_caching_on from sys.databases where name = '<dbname>'
This will return a 1 if it is enabled and 0 if it is not.
To disable the feature
Alter Database <DBNAME> Set Result_Set_Caching OFF
Monitoring use of the Feature
Cache usage can be monitored via DMV and will allow you review within the Query Request Steps if the specific Query ID had a cache hit or miss
If (SELECT step_index FROM sys.dm_pdw_request_steps WHERE request_id = '<Query Id>' and operation_type = 'ReturnOperation' and command like '%DWResultCacheDb%') = 0
SELECT 1 as is_cache_hit
ELSE
SELECT 0 as is_cache_hit
Additional statements are available on the Microsoft Docs website which will provide insight into the number of queries which have cache hit or miss.
Reference Information
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/release-notes-10-0-10106-0
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-result-set-caching-transact-sql?view=azure...
