In my early days as a Db2 DBA, it was occasionally easy to be a performance hero. In those days, on 32-bit systems, the default size of Db2’s default buffer pool (primary data cache) was 1,000 4k pages. For many databases, this was far too small. I remember increasing this to a more sane value for a database once, and with that alone taking the time for a critical query from 15 minutes to less than 30 seconds.
Caching continues to have a heavy impact on query response times in every database system I’ve worked with, even as storage has gotten faster, and Snowflake is no exception.
Caching in Snowflake
Unlike some of the systems I worked with earlier in my career, many Snowflake databases are far too large to be held entirely in memory. I realized today that I had been using a 9.7TB table in a sandbox. I’ve never seen an unpartitioned table that large in the other database systems I’ve worked with, much less a throwaway table in a sandbox.
That doesn’t mean that caching plays no role in Snowflake. Depending on the application, caching can be an important tool within Snowflake. There are main types of caching Snowflake uses to improve the response time of queries.
Result Set Cache
The result set cache is different than the caches I have dealt with in other database systems, though I think MySQL may have something similar. If I remember correctly, the feature in MySQL was problematic. The result set cache in Snowflake is something that makes a lot more sense for analytical applications than it would for OLTP applications. The result set cache is Snowflake simply storing the result of every query run. The result set is generally stored for up to 24 hours from the time a query is run. If the same query is run again, AND the data in the tables being queried has not changed, then a query can simply access this stored result and return it. Each time the same query is run, it resets the 24-hour ttl for the result set, up to 30 days.
There are limitations on the queries used for this. Non-repeatable functions (such as RANDOM()) or functions whose values change with time (such as CURRENT_TIMESTAMP() or even CURRENT_DATE()) can make a query ineligible, as can different values specified as input for query conditions. The full list of restrictions for result set reuse is well-documented in the Snowflake documentation.
The result set cache is particularly useful for data sets that change slowly and for query workloads that run exactly the same queries over and over again, such as dashboards. It may even be a choice to run dashboard queries right after a data set is updated to ensure the result sets are cached and available for future refreshes or users. This works well with infrequently updated data (such as daily) and frequently run queries like those in dashboards.
This cache is the only one where a customer can control whether it is used or not, and it’s probably the setting I use most frequently when playing with individual queries from a performance perspective. The result set cache can be disabled for a session by using ALTER SESSION SET USE_CACHED_RESULT=FALSE; I disable using the result set cache when I’m running a query repeatedly to test the timing with different tweaks.
Oddly, one of the big mistakes I see customers making is disabling the use of the result set cache when doing load testing. If load testing is properly done, the result set cache reuse should be on so the benefits from it are taken in to the load testing. We don’t size the buffer pools down on db2 or other systems when doing load testing.
When a query uses the result set cache, you will often see a blazingly fast query time, and the query profile will simply look like this:
Exploring Result Set Reuse in QUERY_HISTORY
There is no single indicator in query_history that result set caching was used, but it is easy to see, all the same. Let’s look at recent queries in my free trial account by querying query_history with SQL. Here’s the syntax I’m using:
select
query_parameterized_hash
, query_type
, warehouse_size
, execution_status
, total_elapsed_time
, compilation_time
, execution_time
, rows_written_to_result
from table(information_schema.query_history())
where query_type = 'SELECT'
order by start_time;and here is what my result set looks like for that:
In the output, the query on line 4 is the result set reuse of the query on line 3. You can see that the warehouse used is unset for line 4, because result set reuse does not require a warehouse. You can also see that while line 3 took over 65 seconds to run, line 4 was just over 100 milliseconds. This turns into not just faster queries, but usually much cheaper as well.
In the output here, instead of showing the query_id (which we could also return), I’m showing the query_parameterized_hash. Note that it is the same for the queries that are using the result set cache.
Explicitly Using Persisted Results
Having a feature like this can lead to a number of use cases. One is an ability to further refine a result set without re-querying the base data. This can be done using the RESULT_SCAN() table function.
The query id of my query on query history above was 01b627fa-0002-b61f-0000-007c0d073039. Using this, I can explicitly see the result set again:
There’s not a lot of point to that. The more interesting thing to me is that I can then apply standard SQL to that result set to refine further what I’m looking for or write a different query on that result set:
You might be able to imagine scenarios where this is useful – when you need to query a large data set, return that result, and also do further refinement on it. I have absolutely used this before, particularly when I’m exploring a large data set and information I find with one level of querying then informs what work I do next.
Virtual Warehouse Cache
Each virtual warehouse in Snowflake is made up of compute nodes. An XS warehouse for example, is essentially a single virtual machine. That virtual machine has both memory and local disk (SSD) associated with it. As you query data from object storage, that data must be placed on the local ssd storage for Snowflake to work with it. Snowflake uses that SSD as a caching layer, placing micro-partitions on local disk that can then be reused by subsequent queries. While there is certainly a cache eviction strategy, the immutable nature of cloud object storage means that if the data changes, then the micro-partitions are different micro-partitions with different names, so there’s no risk of reading old data from this cache.
For a simple example, I’ll run the same query twice in a row with result set caching off. Here’s the query profile for the first run:
Clearly, none of the data is read from the warehouse cache. Now look at a second run of the same query with result set caching off:
In the second run, we actually managed to get 100% of what we needed from the local warehouse cache without having to go to object storage at all. It’s actually fairly unusual to get 100% – usually even with small repeated queries, we only get part of what we need from the warehouse cache.
The timing difference is noticeable even at the small scale here. The first run of the query having to retrieve everything from S3 took 2.1 seconds, and the second one took just 1.4 seconds. This is for the same query run without the benefit of result set caching, just a few seconds apart.
Often virtual warehouses are being suspended and restarted all the time. This offers tremendous savings potential. I can’t imagine in the bad old days only having to pay for a server when I had work for it to do! It took weeks or months and sometimes millions of dollars to get new hardware! Most of the time when a warehouse is suspended, we loose the SSD cache of micro-partitions and therefore lose the effect of this cache.
One of the nice features of this kind of caching is that it applies to the data itself, not to result sets. This means that data cached for one query can easily be reused for another query if it has not changed.
Metadata Cache
There’s an entire layer of Snowflake you may not spend much time thinking about. The Cloud Services(CS) or Global Service(GS) layer is the layer where the optimizer lives, as do other functions like authorization. If this layer represents 10% or less of an account’s usage, Snowflake doesn’t even bill for this layer. But there is a lot that happens at this layer. There is a database at this layer that stores all of the metadata that is used for things like pruning. That database is FoundationDB or FDB. There is absolutely caching at that layer as well. This means that if a table has recently been queried, even if the querying was from an entirely different virtual warehouse, the metadata needed may already be in the metadata cache, meaning less time is need for optimization/compilation. Because this cache is at a shared layer of Snowflake, customers have little ability to see what is happening around it.
Summary
I’m a big believer that understanding a system better can lead to better design and query decisions. It is amazing to me how many Snowflake customers never have to think about caching, while for other customers taking maximum advantage of caching is the thing that gets them to an important performance objective.
