Performance Concepts: Query Optimization

Question 1

History Tab

Answer 1

• Displays query history for the last 14 days
• Users can view other users queries but cannot view their query results

Question 2

Analysis of Query Duration: Compilation Time

Answer 2

Query engine performing operations like cost based optimization and micropartition pruning

Question 3

Analysis of Query Duration: Execution Time

Answer 3

Steps to process the data, processing time on the CPU

Question 4

Query Analysis: Profile Overview: Processing

Answer 4

The percentage of the total execution time spent on data processing by the CPU of the virtual warehouse

Question 5

Query Analysis: Profile Overview: Local Disk I/O

Answer 5

-The percentage of time processing was blocked by local disk access
- In other words, when the virtual warehouse had to read or write data from the local SSD storage

Question 6

Query Analysis: Profile Overview: Remote Disk I/O

Answer 6

When the virtual warehouse had to read or write data from the remote storage layer

Question 7

Database Order of Execution

Answer 7

Rows->Groups->Result

Question 8

Rows statements

Answer 8

FROM
JOIN
WHERE

Question 9

Group statements

Answer 9

GROUP BY
HAVING

Question 10

Result statements

Answer 10

SELECT
DISTINCT
ORDER BY
LIMIT

Question 11

Bytes spilled to local storage

Answer 11

Volume of data spilled to virtual warehouse local disk

Question 12

Bytes spilled to remote storage

Answer 12

Volume of data spilled to remote disk.

Question 13

Caching Layers

Answer 13

Services Layer
Warehouse Layer
Storage Layer

Question 14

Services Layer Cache Categories

Answer 14

Metadata Cache
Results Cache

Question 15

Metadata Cache

Answer 15

• highly available service residing in the cloud services layer
• maintains metadata on object information and statistics for each cache type
• sometimes referred to as the metadata store cloud services layer or just services layer

Question 16

What does the metadata cache store?

Answer 16

-maintains understanding of what tables create, what databases those are in, what are the micro partitions that make up those tables, clustering information and more
- enables queries to be executed without the need of a virtual warehouse
- For example, the row count of each table is stored in a cache allowing for executing something such as a select count without using the virtual warehouse
- Functions, context functions, describe commands, and show commands make use of metadata

Question 17

Results Cache

Answer 17

• Also referred to as Result 24 hour
• Each time a persistent result is reused, the 24 hour retention period is extended up to a maximum of 31, after which the result is purged

Question 18

To reuse a result, ‘Result Cache’ Rule

Answer 18

• New query exactly matches previous query
• The underlying table data has not changed
• The same role is used as the previous query
• If time context functions are used, such as CURRENT_TIME(), the result cache will not be used
• Result reuse is disabled using the session parameter USE_CACHED_RESULT

Question 19

Warehouse Cache

Answer 19

• Virtual warehouses have local SSD storage which maintains raw table data used for processing a query
• The larger the virtual warehouse the greater the local cache
• It is purged when the virtual warehouse is resized, suspended, or dropped
• Can be used partially, retrieving the rest of the data required for a query from remote storage

Question 20

Materialized View

Answer 20

• a pre-computed and persisted data set derived from a SELECT query
• are updated via a background process ensuring data is current and consistent with the base table
• improve query performance by making complex queries that are commonly executed, readily available
• an enterprise edition and above serverless feature
• use compute resources to perform automatic background maintenance
• use storage to store query results, adding to the monthly storage usage for an account

Question 21

Materialized Views can be created on top of ____________ _____________ to improve their query performance.

Answer 21

external tables

Question 22

Materialized Views are limited in the following ways:

Answer 22

• Can only query a single table
• Cannot use JOIN
• Cannot use UDF, HAVING, ORDER BY, LIMIT, WINDOW FUNCTIONS

Question 23

Clustering

Answer 23

a way describing the distribution of a column’s values

Question 24

Automatic Clustering

Answer 24

• Snowflake supports specifying one or more table columns/expressions as a clustering key for a table
• Clustering aims to co-locate data of the clustering key in the same micro-partitions
• Clustering improves performance of queries that frequently filter or sort on the clustered keys
• Clustering should be reserved for large tables in the multi-terabyte range

Question 25

When choosing a clustering key, Snowflake recommends a maximum of __ or __ columns (or expressions) per key.

Answer 25

3, 4

Question 26

A __________ cardinality column would be something like a unique ID or a timestamp down to a millisecond.

Answer 26

high

Question 27

A ________ cardinality would be something like country or gender.

Answer 27

Low

Question 28

When choosing a clustering key, a cardinality that is too _____ will negatively impact pruning because the values are too unique to be grouped in micro-partitions.

Answer 28

high

Question 29

If multiple columns are chosen to be the clustering key, it is suggested that the ______ cardinality is selected first followed by the ________ cardinality.

Answer 29

lowest, highest

Question 30

Clustering Key Code Examples

Answer 30

Question 31

As _____ operations are performed on a clustered table, the data in the table might become less clustered.

Answer 31

DML

Question 32

________________ is a background process which transparently reorganizes data in the micro-partitions by the clustering key.

Answer 32

reclustering

Question 33

Initial clustering and subsequent reclustering operations consume __________ and ________ credits.

Answer 33

clustering, reclustering, compute, storage

Question 34

Clustering is recommended for large tables which do not frequently _________ and are frequently ___________.

Answer 34

change, queried

Question 35

__________ ____________ ___________ is a table level property aimed at improving the performance of selective point lookup queries.

Answer 35

Search optimization service