Chapter 4 of 6 · Snowflake ADA-C02

Performance Monitoring and Tuning

Query Profile, tuning features, caching, locking, resource monitors and event tables.

  • 16topics
  • 1 h 13 minfast track
  • 4diagrams and exhibits
  • 20%of the exam

Topics in this chapter

Take them in order, or jump to the one you need.

  1. 1Monitoring Performance and Reading the Query ProfileMonitor Snowflake performance with Query History, ACCOUNT_USAGE and INFORMATION_SCHEMA, then read the Query Profile to find the…4 min
  2. 2Query Profile Steps, Operator Tree, Nodes and Operator TypesKnow the Query Profile building blocks for ADA-C02: steps, the operator tree, operator nodes and the operator types to recognize…Diagram5 min
  3. 3Efficient Queries - Compile and Runtime Optimization, Joins, Grouping and SortingWrite efficient Snowflake queries: compile versus runtime optimization, the execution path, effective join conditions, grouping…5 min
  4. 4Troubleshooting Spilling, Pruning, Timeouts and EXPLAINTroubleshoot slow Snowflake queries: data spilling, missing partition pruning, the timeout parameters and how to read an EXPLAIN…5 min
  5. 5Caching - Result, Local Disk (Warehouse) and Metadata CachesCompare Snowflake caches: result cache, local disk warehouse cache and metadata cache, and what happens to the warehouse cache on…Diagram5 min
  6. 6Performance Improvements - Materialized Views, Search Optimization, External Tables and Query AccelerationChoose the right performance feature: materialized views, search optimization service, external tables, data caching and the…5 min
  7. 7DML Locking, Concurrency and Transaction MonitoringUnderstand Snowflake DML locking and concurrency, apply best practices for UPDATE, DELETE, MERGE and INSERT, and monitor or abort…4 min
  8. 8Resource Monitors and Cost DashboardsCreate and manage Snowflake resource monitors from business needs, understand triggers and levels, and build a Snowsight…Diagram4 min
  9. 9Event Tables - Logging, Metrics and Tracing SetupEnable logging and tracing with Snowflake event tables: default versus custom tables, setup, and the LOG_LEVEL, METRIC_LEVEL and…Diagram4 min
  10. 10Analyzing Event Tables - Health, Security Monitoring, Alerts and Trace AnalysisQuery event tables for system health, monitor user activity and access with log data, create alerts, and use trace data for root…4 min
  11. 11Organization Costs and the Usage ViewsManage Snowflake costs across an organization: ACCOUNT_USAGE against ORGANIZATION_USAGE, who can query them, key views, latency…4 min
  12. 12Data Transfer, Replication, Storage and Credit CostsCalculate Snowflake data transfer, replication, storage and credit costs: which views to query, what drives each bill and the…5 min
  13. 13ACCOUNT_USAGE Against INFORMATION_SCHEMAChoose between ACCOUNT_USAGE and INFORMATION_SCHEMA by latency, retention, dropped objects and visibility, with history functions…4 min
  14. 14Warehouse Costs, Suspend and Resume, and Idle TimeControl virtual warehouse cost: when to suspend or resume, how to calculate credits by size, cost saving strategies and how to…5 min
  15. 15Budgets, Warehouse Monitoring and Automatic Clustering CostUse Snowflake Budgets for credit alerts, warehouse monitoring data to cut cost, and AUTOMATIC_CLUSTERING_HISTORY to assess…5 min
  16. 16Serverless, AI and Notebook CostsTrack credits from cloud services, serverless features, AI and ML functions and Notebooks, with the views and controls an…5 min

About this chapter

What this chapter covers

Performance Monitoring and Tuning is the chapter on how an administrator finds slow or costly work in Snowflake and fixes it. For the SnowPro Advanced: Administrator ADA-C02 exam it tests diagnosis and choice: which view or profile node shows the problem, which feature or parameter fixes it, and what it costs.

Questions are scenarios that start with "An administrator needs to ...". They are single answer, multiple select or interactive, and the right answer usually turns on one detail such as where a cache lives, which statements block each other, or what a resource monitor cannot control.

How the topics fit together

Work from symptom to cause to fix, and keep an eye on the cost of each fix.

Monitorhistory and usage viewsdrill intoQuery Profileoperators and statisticschooseTuningcaching, MV, SOS, QASprotectConcurrency and costlocks, resource monitorsobserveTelemetryevent tables, alerts, traces
  • Monitoring and the Query Profile show where time went: steps, operator tree, nodes, operator types and the Statistics pane.
  • Efficient queries and troubleshooting cover compile versus runtime work, joins, spilling, pruning, timeouts and EXPLAIN.
  • Caching and improvements compare the result, warehouse and metadata caches with materialized views, search optimization, external tables and query acceleration.
  • Locking and transactions explain which DML statements block each other and how to stop a blocker.
  • Resource monitors and event tables put limits on warehouse spend and collect logs, metrics and traces.
Exam tip

Many questions ask you to name the first thing to check. Query Profile Statistics, the pruning counters and the spill counters answer most of them.

How to use these pages

Each topic page has the same shape, so you can skim for what you need.

  • What it is: the idea in plain words.
  • How it works: the mechanism, with diagrams, SQL and comparison tables.
  • Common mistakes: the classic traps.
  • What to remember for the exam: the facts to memorize.
  • Check yourself: three questions to test your understanding.

Ready for more practice?

The full Snowflake ADA-C02 question bank has 814 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.