Contents
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:
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.
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.
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.
What is the default value for the result cache?
Specifies the expiration time (in minutes) for a result in the server result cache that depends on remote database objects. The default value is 0, which specifies that results using remote objects will not be cached. If a non-zero value is set for this parameter, DML on the remote database does not invalidate the server result cache.
What happens when the result of a query is not cached?
If the result is not cached, then the database executes the query, returns the result as output, and stores the result in the result cache. When users execute queries and functions repeatedly, the database retrieves rows from the cache, decreasing response time. Cached results become invalid when data in dependent database objects is modified.
When to turn off result set caching in azure?
Those queries are commonly used during data exploration or ETL operations. To avoid stressing the control node and cause performance issue, users should turn OFF result set caching on the database before running those types of queries.