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.
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 8Resource Monitors and Cost DashboardsCreate and manage Snowflake resource monitors from business needs, understand triggers and levels, and build a Snowsight…Diagram4 min
- 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
- 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
- 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
- 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
- 13ACCOUNT_USAGE Against INFORMATION_SCHEMAChoose between ACCOUNT_USAGE and INFORMATION_SCHEMA by latency, retention, dropped objects and visibility, with history functions…4 min
- 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
- 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
- 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.
- 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.
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.