Data Warehousing on AWS Training Monitoring and Troubleshooting 2

Question 1

How can you identify which user is running a long-running query that is blocking other queries in Redshift?

Accepted Answer

Query STV_INFLIGHT joined with SVV_TRANSACTIONS to find the blocking session and user

Answer

Joining STV_INFLIGHT with SVV_TRANSACTIONS shows active queries, their lock status, and the associated user and session ID.

Question 2

Which Redshift command terminates a currently running query without disconnecting the user session?

Accepted Answer

CANCEL query_id

Answer

The CANCEL command stops a specific running query identified by its query ID while leaving the user session active.

Question 3

What does the SVV_TABLE_INFO view in Redshift provide that is useful for troubleshooting storage issues?

Accepted Answer

Table-level statistics including size, skew, and the percent of unsorted rows

Answer

SVV_TABLE_INFO shows per-table metadata including size, distribution skew ratio, and the percentage of rows that are unsorted.

Question 4

Which Amazon Redshift metric should alert you to a potential skewed distribution key problem?

Accepted Answer

NodeSliceDiskSpaceUsed variance across nodes

Answer

Uneven NodeSliceDiskSpaceUsed across cluster nodes indicates data skew where some nodes hold disproportionately more data.

Question 5

What is the purpose of the pg_cancel_backend() function in Amazon Redshift?

Accepted Answer

Terminates a specific backend process (query) by process ID

Answer

pg_cancel_backend(pid) sends a cancellation signal to the specified backend process ID, terminating its current query.

Data Warehousing on AWS Training Practice Test