topic Disable caching in Serverless SQL Warehouse in Data Engineering

Disable caching in Serverless SQL Warehouse

drag7ter — Thu, 16 Jan 2025 20:41:11 GMT

I have Serverless SQL Warehouse claster, and I run my sql code in sql editor. When I run query for the first time I see it take 30 secs total time, but all next time I see in query profiling that it gets result set from cache and takes 1-2 secs total time.

Even when I stop my serverless warehouse cluster and start it again it uses cache.

Even when I create totally new serverless sql warehouse cluster with a new name and cluster id it uses cache from the previous one.

My questions are:
1. Why doesn't it clear the cache when I stop and start my serverless sql warehouse cluster ?

2. Why does it use cache from the previous cluster , even when I create totally new serverless sql warehouse cluster with a new name and cluster id?

3. Why doesn't work this setting SET use_cached_result = false on serverless sql warehouse cluster? And also I think it doesn't work on Classic and Pro I think.

4. Is it possible somehow disable using cache in serverless sql warehouse cluster?

Re: Disable caching in Serverless SQL Warehouse

NandiniN — Fri, 17 Jan 2025 04:05:10 GMT

Remote result cache: The remote result cache is a serverless-only cache system that retains query results by persisting them as workspace system data. As a result, this cache is not invalidated by the stopping or restarting of a SQL warehouse. Remote result cache addresses a common pain point in caching query results in-memory, which only remains available as long as the compute resources are running. The remote cache is a persistent shared cache across all warehouses in a Databricks workspace.

https://docs.databricks.com/en/sql/user/queries/query-caching.html

Re: Disable caching in Serverless SQL Warehouse

NandiniN — Fri, 17 Jan 2025 04:05:43 GMT

4. Is it possible somehow disable using cache in serverless sql warehouse cluster?

Remote result cache is available for queries using ODBC / JDBC clients and SQL Statement API.

To disable query result caching, you can run SET use_cached_result = false in the SQL editor.

Re: Disable caching in Serverless SQL Warehouse

drag7ter — Fri, 17 Jan 2025 12:16:31 GMT

As I mentioned above this setting doesn't work for sql warehouse cluster

SET use_cached_result = false

Re: Disable caching in Serverless SQL Warehouse

NandiniN — Sun, 26 Jan 2025 07:44:47 GMT

I am wondering if it is using Remote result cache, in that case the config should work.

There are 4 types of cache mentioned here https://docs.databricks.com/en/sql/user/queries/query-caching.html#types-of-query-caches-in-databricks-sql

Local cache: once the cluster is stopped or restarted, the cache is cleaned and all query results are removed.

Databricks SQL UI cache: the cache is invalidated once the underlying tables have been updated.

You can delete query results by re-running the query that you no longer want to be stored. Once re-run, the old query results are removed from cache.

Disk cache: stores data on disk, allowing for accelerated data reads.

disk cache automatically detects changes to the underlying data files. When it detects changes, the cache is invalidated. The disk cache shares the same lifecycle characteristics as the local result cache. This means that when the cluster is stopped or restarted, the cache is cleaned and needs to be repopulated.

Even when I stop my serverless warehouse cluster and start it again it uses cache.

So, that only leaves us to Remote result cache : persisting them as workspace system data. This cache is not invalidated by the stopping or restarting of a SQL warehouse.

SET use_cached_result = false;

This command disables the query result caching for the current session. By any chance you are using it in a different session? If yes, before you run your command, set this.