Contents
How does result set caching improve database performance?
When result set caching is enabled, dedicated SQL pool automatically caches query results in the user database for repetitive use. This 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.
When to use a cached result set in a query?
Cached result set is reused for a query if all of the following requirements are all met: The user who’s running the query has access to all the tables referenced in the query. There is an exact match between the new query and the previous query that generated the result set cache.
When is result set caching turned on or off?
Result set caching is OFF by default at the database and session levels. Once result set caching is turned ON for a database, results are cached for all queries until the cache is full, except for these queries:
Why does result set caching cause high throttling?
When result set caching is turned ON, running queries that return large result set (for example, >1GB) can cause high throttling on the control node and slow down the overall query response on the instance. Those queries are commonly used during data exploration or ETL operations.
What happens when result set caching is disabled?
If data in the ORDER BY columns are not unique, there’s no guaranteed row order for rows with the same values in the ORDER BY columns, regardless if result set caching is enabled or disabled. The operations to create result set cache and retrieve data from the cache happen on the control node of a dedicated SQL pool instance.
What’s the maximum size of result set cache?
The maximum size of result set cache is 1 TB per database. The cached results are automatically invalidated when the underlying query data change. The cache eviction is managed by dedicated SQL pool automatically following this schedule: Every 48 hours if the result set hasn’t been used or has been invalidated.