Azure Synapse Resultset caching – how it works?

SELECT        
 OBJECT_SCHEMA_NAME( object_id ) AS schemaName,        
 OBJECT_NAME( object_id ) AS tableName,        
 distribution_policy_desc 
FROM sys.pdw_table_distribution_properties;

Of course, it is not the best configuration from a performance point of view but for our purposes is good enough.

Now let’s run our testing query:

SELECT 
     YEAR(SOH.OrderDate) 
    ,C.SalesPerson 
    ,P.Color 
    ,AVG(UnitPrice) 
    ,SUM(OrderQty) 
    ,COUNT(*) 
FROM dbo.[SalesOrderDetail] AS SOD 
JOIN dbo.[SalesOrderHeader] AS SOH 
    ON SOH.SalesOrderId = SOD.SalesOrderId 
JOIN dbo.Product AS P 
    ON P.ProductId = SOD.ProductId 
JOIN dbo.Customer AS C 
    ON C.CustomerId = SOH.CustomerId 
GROUP BY 
     YEAR(SOH.OrderDate) 
    ,C.SalesPerson 
    ,P.Color 
OPTION (LABEL = 'Cache test query')

SELECT 
     request_id 
    ,command 
    ,status 
    ,result_cache_hit 
FROM sys.dm_pdw_exec_requests AS PDW 
WHERE [label] ='Cache test query'

ALTER DATABASE SynapseDWH SET RESULT_SET_CACHING ON ;

As you can see above, this time we have status 0 which means we didn’t get data from the cache because it is the first time that we run this query. After one more execution we have the following result:

Now we have the result that we expected. If we want to go deeper into the execution of a query we can check another DMV called sys.dm_pdw_request_steps:

SELECT 
    request_id
   ,step_index
   ,operation_type
   ,location_type
   ,status
   ,total_elapsed_time
   ,command 
FROM sys.dm_pdw_request_steps 
WHERE request_id IN ('QID7252','QID7263')

“Not cached query” required 18 steps to return data including some data movement activities etc, “Cached query” required only one step – return data from cache from Control Node. Sounds cool, isn’t it?  We have also command executed to return data from the cache and it looks like this:

select * from [DWResultCacheDb].dbo.[iq_{0C460BE7-CC96-4A77-A2CD-7FD737F59A14}] OPTION(MAXDOP 1)

Cache will be maintained automatically by Synapse – for example, it will be purged every 48 hours if it wasn’t used or has been invalidated or when it reaches maximum size. You can do those things also manually – the cache will be removed when you will turn it off for a database or when you will run DBCC DROPRESULTSETCACHE.

It was a little surprising for me when I first touch this feature that cache is not automatically dropped when we will pause our instance. It is very important information to keep in mind. It works like this because it is not in-memory RAM based cache but rather something similar to Bufferpool Extension from SQL Server (link) where data is placed in nonvolatile storage – in this case very fast NVMe SSD which is not as fast as RAM but still much faster than starndard disks. Please be aware that resultset cache is not working like query plan caching – in this case, to hit cache you have to run exactly the same query. Exactly in this case means that the query string must be 100% the same.

Let’s check how it works on example, I have a simple query that I executed two times:

SELECT TOP 1000  * FROM dbo.[SalesOrderDetail] OPTION (LABEL = 'Hit cache test')

SELECT   SUM(OrderQty) FROM dbo.[SalesOrderDetail] WHERE SalesOrderId = 71832 OPTION (LABEL = 'Hit cache - where clause')

SELECT   SUM(OrderQty) FROM dbo.[SalesOrderDetail] WHERE SalesOrderId = 71832 AND OrderQty<100 OPTION (LABEL = 'Hit cache - where clause')

As you can see resultset cache can be a powerful tuning technique but it is not feasible for all scenarios.

A few good examples come to my mind when this feature should be considered:

• Initial screen of Power BI or any other reporting tools – it is sending a query to the database that is known in advance – result set can be placed in the cache so users will not wait for initial data loading on the report,
• database is queried automatically by reporting tools like Power BI and some of those queries can be executed in advance to put data in cache (good old technique to warm cache is still valid),
• some data is exported (for example via Power BI Paginated Reports) and we have well-known queries used for exporting,
• during the ETL some query results must be referenced multiple times – it can be referenced from the cache,

Scenarios when resultset cache is not the best thing to use:

• a lot of ad-hoc queries – workload with a lot of unique queries will place data in the cache but it will not be reused,
• reporting tools will send tons of different queries,
• data will change very often so the cache will be invalidated and will not be reused,
• cached result set will be so big that it can cause high throttling on the control node and slow down the overall query response on the instance.