A user executes the following SQL statement in Snowflake: SELECT FROM MY TABLE; The user reports that the query is running very slowly. Assuming the user has the necessary permissions to access the ta

The correct answer is A. The virtual warehouse is undersized. Check the query profile in Snowsight to observe spill to local B. The table 'MY _ TABLE is not clustered properly. Run 'SYSTEM$CLUSTERING_INFORMATION' C. The _ TABLE table is very wide with numerous columns, making it inefficient to select all columns.. Options A, B, and C are correct because they each represent legitimate, verifiable Snowflake-specific performance bottlenecks for a full table scan (SELECT ). An undersized warehouse (A) causes data to spill from memory to local disk, which is visible in the Query Profile in Snow

Querying and Performance

Question

A user executes the following SQL statement in Snowflake:

SELECT FROM MY TABLE; The user reports that the query is running very slowly. Assuming the user has the necessary permissions to access the table, which of the following could be the cause of the slow query performance and how could you verify that the root cause is the bottleneck?

Options

AThe virtual warehouse is undersized. Check the query profile in Snowsight to observe spill to local
BThe table 'MY _ TABLE is not clustered properly. Run 'SYSTEM$CLUSTERING_INFORMATION'
CThe _ TABLE table is very wide with numerous columns, making it inefficient to select all columns.
DNetwork latency between the user's location and the Snowflake region is high. Run a network
EThe query is waiting on lock contention. Check the LOCK WAIT TIME column in the QUERY

How the community answered

(41 responses)

A
63% (26)
D
12% (5)
E
24% (10)

Explanation

Options A, B, and C are correct because they each represent legitimate, verifiable Snowflake-specific performance bottlenecks for a full table scan (SELECT *). An undersized warehouse (A) causes data to spill from memory to local disk, which is visible in the Query Profile in Snowsight under the "Spilling" operator. Poor clustering (B) means Snowflake cannot prune micro-partitions efficiently, forcing a full scan of all partitions - SYSTEM$CLUSTERING_INFORMATION('MY_TABLE') reveals the clustering depth and overlap ratio to confirm this. A wide table with many columns (C) compounds a SELECT * by requiring Snowflake to decompress and return every column's data, increasing I/O and network transfer from the storage layer.

Option D is incorrect because Snowflake separates compute from storage - query execution happens within the cloud provider's network, so end-user network latency affects only result retrieval, not query execution time itself. Option E is also incorrect because lock contention is a concern for DML operations (INSERT, UPDATE, DELETE, MERGE), not simple SELECT queries, which do not acquire row or table locks in Snowflake.

Memory tip: Think "WAC" - Warehouse size, Architecture (clustering), Columns selected. These are the three internal Snowflake levers for SELECT performance, while network and lock contention are external or DML-specific concerns.

Topics

#Virtual Warehouse Tuning#Table Clustering#Query Optimization#Performance Profiling

Community Discussion

No community discussion yet for this question.

Full SOL-C01 Practice