Configuring result set caching in Azure Synapse dedicated SQL pool

Result set caching is a feature in Synapse dedicated SQL pools that caches the result of the query in the control node of the Synapse dedicated SQL pool instance. In other words, the first time a query is executed, the result of the query is stored in the control node of the Synapse dedicated SQL pool instance. The next time, when the same query is executed and if the underlying data hasn’t changed, the query engine will quickly return the result directly from the cache in the control node without reading the data from any of the compute nodes of the Synapse dedicated SQL pool instance. Unlike other common memory-based caches, the result set cache is persisted even after the Synapse dedicated SQL pool instance has been restarted.

In this recipe, we will learn how to turn on result set caching and how to verify if the cache is being used.

Getting ready

Create a Synapse Analytics workspace, as explained...