page57
39
© Andrew Carruthers 2024
A. Carruthers, Tuning the Snowflake Data Cloud, https://doi.org/10.1007/979-8-8688-0379-6_3
CHAPTER 3
The Query Profiler
In Chapter 2, I showed how the query optimizer processes a SQL statement to produce a
physical query plan. You will investigate how the query profiler operates by executing a
query plan and generating execution statistics that expose various metrics.
This chapter initially focuses on the visual aspects of query profiling and later
focuses on remediating issues.
The hands-on examples utilize TPC-H data supplied by Snowflake; you can find
additional information about this data set at
https://docs.snowflake.com/en/user-
guide/sample-data-tpch
. I assume you have access to a Snowflake account, but if not,
a trial account is available at
www.snowflake.com
. Click the Start For Free button, and
enter a few details to start your 30-day free trial.
Throughout this chapter, I will reference use cases to illustrate the query profiler
behavior and later in this book will reference the same queries to demonstrate how you
can identify and remediate performance issues.
Please note that some of the “bad” queries will consume all your credits; therefore,
please read through this chapter carefully as I explain “why” and offer mitigating actions
to prevent excessive credit consumption.
You may prefer to set
statement_timeout_in_seconds
in the current session to
avoid overspend. In this example, you can set the timeout to 600 seconds (10 minutes).
ALTER SESSION SET statement_timeout_in_seconds = 600;
You can find further details at
https://docs.snowflake.com/en/sql-reference/
parameters#statement-timeout-in-seconds
. You can find supplemental information
at
https://community.snowflake.com/s/article/Parameter-STATEMENT-TIMEOUT-IN-
SECONDS- covers-the-overall-time-of-query-execution
.