Python and JVM stored-procedures cannot create temporary objects; but can createscoped temporary objects
Large result-sets are passed to the client via blob; smaller (set of) results are transferred directly via Snowflake VPC over HTTPS (Ref: Jim Brody PrivateLink lunch-n-learn)
All SO bloom search-trees are built case-insensitive (Ref: perf fcto)
temporary tables are also written to remote storage, this is because they are tied to the session, keeping them on local storage makes them tied to the cluster they are running on
performance:
select only the needed column due to Snowflake being a columnar database
stable plan: shows when a plan is pinned; can be requested through support
Shows only XP level information. i.e. post GS layer activities
Execution Log -> XP Misc => large value means high overhead (not useful work)
Query retries
Step # for retries are in 1000 + original step #;
customers can see step retries, but not job retries
a job failure is when the entire query is sent back to GS layer for rerunning
Misc time (Query Details -> Black bar in Statistics) - prev retry time
Worker => Node, Instance => thread
QAS has a separate query-id, which is in the Query Detail -> Execution Log. This QID is internal and can't be searched. But can be put directly in the URL to bring it up
Each parameter that is active in in Query Detail -> Execution Log
Can compare two query-ids using Compare tab
More useful compare: type a second query-id in the URL with , separating two query-ids
high mutex time => a lot of data exchanges (SnowVi Overview Video)
Skew = Tot threads / threads used, e.g. 16 threads / 2 busy threads => ~ 8x Skew
RSOs Skew tab: InputRecords is the number of rows flows in and NetSent is flow out of each RSO by instance and thread
Investigate Other time in RSO/RSO Normalized charts in All Stats Tab: Look at each RSO/thread to see when they started/ended
All Stats -> Instance Usage: Shows how many instances (threads) were busy
If an RSO showsdistribution method as local_synchronous => no shuffle, for hash => shuffle
Box# are meaningful (as in order of execution) in SnoVi v/s meaningless in Profile
BLOOM_ASYNC Join (not the first choice of Snowflake) is reshuffle (Ref PerfSol call) of both (build and prob) sides, whereas BROADCAST (preferred) is where build/left side is broadcasted to all nodes of probe/right side
SnoVi's CPU utilization doesn't include CPU used by UDFs
DOP = Degree of Parallelism
MV on external tables, aggregates at file level to materialize (Ref Darren Gardner perf CTO)
ScanBack operator usually appears in DML operations and reads and then rewrites all rows from the affected MPs
Total number of MPs/rows read/rewritten can be obtained by looking at filesUnregistered and numOriginalRows attributes of the ScanBack operator
Split operator can occur:
MERGE when some of the matched rows remain unchanged (e.g. due to additional condition WHEN MATCHED AND ...), the RSO references only the target columns; and the other branch contains changed data (RSO references target and source data)
Small File Compaction may happen during an INSERT (per Darren FCTO), where Snowflake, when performing INSERTs may combine micro-partitions that are too small
Two aggregates nodes in succession (first one being called either bottom or child) is for local and global sorts
ScanBack operator is used for writing micro-partitions that have some of the rows that were updated. It is depicted with number of rows going in and number of rows coming out. They represent how many rows were needed to be updated and how many rows actually ended up being written. Very high ratio of rows-written to rows-changed could indicate updates are spread across large number of micro-partitions. Clustering or faster-DML is normally the solution