page87
69
Bad Query Profiles
Unfortunately, I see far more “bad” query profiles than “good” query profiles. One good
reason for writing this book is to impart sufficient information to developers to enable
them to identify “bad” query profiles. The very best developers check each query profile
for optimal behavior and performance by both unit testing and checking query profiles
before submitting their code for promotion into production systems.
However, performance tuning is often an after-thought. Our hope is the information
found within this book will greatly assist and reduce the time it takes to identify and
remediate performance issues.
please do not execute the queries within this section. Most will consume
significant credits along with execution time; they are for illustration purposes only.
Let’s now investigate what “bad” looks like in its many guises. While executing a
query using SnowSight, if you suspect the query has not completed within a reasonable
timeframe, clicking the ID will display the available query profile.
The most important message from this section is to simplify code.
Notes on Data Capture
In this section, you will create tables and select from the Account Usage Store the
query_
history
table. You can also create a view to overlay
query_history
; note that you also
use a seven-day time band.
The choice of tables is deliberate. Your code base is intended to be extensible for
deployment and data capture from a central provisioning team for which my previous
book Maturing the Snowflake Data Cloud provides a detailed hands-on guide.
You can assume performance tuning is not a one-off activity; you should pro-actively
monitor, detect, and continually remediate performance issues. This chapter establishes
a variety of tools and techniques used in support of these activities, and the last chapter
brings everything together. I can’t wait to get there!
ChapTer 3
The Query profiler