Query profiling - Very high Synchronization rate?

Question

While doing some query optimizing work, one of our queries has a very high "synchronization" time in a table scan step (up to 98% of the total query execution time). The query joins 3 tables, one fact table with 100B+ rows and two small dimension tables. The virtual warehouse is a S size.

The Snowflake docs are very limited on explaining what "synchronization" is: " various synchronization activities between participating processes." Analyzing the Query History — Snowflake Documentation

Does anyone know what this synchronization component is and how to improve it?

Answer 1

TL,DR: syncronization measures the amount of time your warehouse workers are waiting for each other or are setting up the computation. Check the bullets below for pointers on how to improve further.

A warehouse is made up of some number of nodes (depending on size) and each node has some number (used to be 8, probably still is) of worker processes. When a query arrives, micro-partitions are assigned to each worker and they begin processing the query independently of each other.

This works nicely until a worker encounters a processing step that requires the results generated by other workers. During this time a worker sits idle and is syncronizing with the other worker processes.

An example of this would be a JOIN statement: Initially, each worker computes a part of the build-side hash table but requires the full hash table to be ready before it can start processing the join-side of the query. Once a worker has finished its assigned share of "build-side generation" it will sit idle and wait for the other workers to finish their share. Once all workers have finished (and no more writing to the hash table is needed) all workers proceed to process their share of the join side.

This coordination between worker processes is what shows up as syncronization in the profiler. I think (but don't know this for sure) that syncronization also includes the time needed to fetch table metadata and assign work to each worker process. You can test the latter by running the query repeatedly on the same warehouse (with result cache disabled) and checking the timings again.

Hence, there are three ways to further improve performance:

1. Denormalize/pre-compute the table joins (this avoids the need to syncronize on JOIN)
2. Use a smaller warehouse (between-node workers take longer to sync than within-node workers)
3. "warm up" your warehouse cache (run queries against the tables involved on the same warehouse before running this query)

That said, I wouldn't worry about optimizing this query any further. You are in a territory where you will see more performance gain from hitting the warehouse cache than you will from tweaking SQL.