Amazon Redshift is a cloud-based data warehouse that lets you analyze data at scale, but it also provides performance metrics and data to monitor its health and performance.
The Query profiler is a new graphical tool that gives a visual representation of the query’s run order, execution plan, and various statistics that will help users analyze the query performance and troubleshoot it.
The Query profiler is part of the Amazon Redshift Console, available for both Redshift Serverless and Redshift provisioned clusters dashboard.
Using the Query profiler, users can identify the root cause of the query slowness and troubleshoot long-running queries effectively.
Amazon Redshift provides two categories of performance data that helps monitor database activity, inspect and diagnose query performance problems; Amazon CloudWatch Metrics and Query and Load Performance Data.
This article provides a step-by-step approach to analyze and troubleshoot longer running queries using the Query Profiler for two use cases: Nested loop joins and Suboptimal data distribution.
To avoid unwanted costs, dropping all tables in sample_data_dev under tpcds schema is necessary after completing the steps for Query Profiler.
The Query profiler displays information returned by SYS_QUERY_HISTORY, SYS_QUERY_EXPLAIN, SYS_QUERY_DETAIL, and SYS_CHILD_QUERY_TEXT views.
Queries run by Query profiler to return the query information run on the same data warehouse as the user-defined queries.
The authors of the article demonstrate how Query profiler can be used to monitor and troubleshoot long-running queries, recommends users try this feature and share feedback with AWS.