Optimize common queries with result-set caching
Enable result-set caching when you expect results from queries to return the same values.
This option stores a copy of the result set on the control node so that queries do not need to pull data from the storage subsystem or compute nodes. The capacity for the result set cache is 1 TB and the data within the result-set cache is expired and purged after 48 hours of not being accessed.
Azure Synapse SQL automatically caches query results in the user database for repetitive use. Result-set caching allows subsequent query executions to get results directly from the persisted cache so recomputation is not needed. Result set caching improves query performance and reduces compute resource usage.
To enable result set caching, run this command when connecting to the MASTER database.
ALTER DATABASE [database_name]
SET RESULT_SET_CACHING ON;