We’re excited to announce a new addition to the Looker’s suite of System Activity dashboards: Performance Recommendations. Now, in addition to tracking user activity, content usage, and high-level instance performance, System Activity provides you with actionable recommendations for improving performance and enables you to drill into detailed query performance data.
This new dashboard can help you:
Improve content performance by aligning with best practices
Identify query bottlenecks that are slowing down performance for users
Prioritize work based on the severity of performance issues
Learn about ways to optimize performance of dashboards and Explores
This new Performance Recommendations dashboard is built using a new underlying Explore called Query Performance Metrics, which we have also made available through System Activity. The Query Performance Metrics Explore provides detailed performance measures for each step of query execution, enabling you to dig far beyond overall query runtime as you analyze performance.
Performance Recommendations dashboard
The new Performance Recommendations dashboard includes two tiles: one with dashboard recommendations and one with Explore recommendations. Let’s look at what you can find on each one.
The Dashboard Recommendations tile focuses on identifying specific dashboards that are out of line with Looker performance best practices, with each dashboard ranked based on the severity of the issue(s) identified. Common warnings that you’ll find here include:
Dashboard auto-refresh settings that are more frequent than would be recommended
Dashboard tile counts being too high
The number of merged queries on a dashboard being too high
As you’d expect, recommendations guide you to update settings or reduce the number of tiles and/or merged queries on a given dashboard. In addition, each recommendation links out to documentation that provides more information about the recommendation being made.
The Explore Recommendations tile is built from the new Query Performance Metrics Explore and provides recommendations based on average performance of each query step across queries run from a given Explore. This aims to help you identify query bottlenecks and offers suggestions for improvements like:
Places where PDTs could be helpful for simplifying complex SQL logic that takes a long time to execute
Opportunities to reduce custom formatting or table calculation usage in order to improve post-query processing
New features that could be enabled, like the new LookML runtime, that can help improve overall performance
In addition to these recommendations, you can also “Explore from here” to dig deeper into query performance with the Query Performance Metrics Explore.
Query Performance Metrics Explore
Within the Query Performance Metrics Explore, you are able to investigate specific queries to understand what’s happening at each step of the execution process.
Each phase of query execution includes even more detailed steps, so we’re making these details available at the most granular level possible. This new level of detail makes it easier to identify the specific bottlenecks that are resulting in long-running queries. Things like concurrency issues, connection limits, network latency, and slow query execution within the database can be more easily differentiated, diagnosed, and acted upon. To learn more about query phases and the metrics available, check out our documentation.
For those using BigQuery with Looker, this Explore also includes three database-specific metrics specifically aimed at highlighting BI Engine usage for query acceleration:
BigQuery Job ID
BI Engine Mode
BI Engine Reason
This makes it easier to tie Looker queries back to BigQuery. It also helps you determine whether a given query was able to be partially or fully accelerated using BI Engine. Note that these values will be null for queries run against databases other than Google BigQuery.
As you dig into query performance, you can also set up your own Looks and alerts using this data to help you proactively manage query performance. Consider creating weekly scheduled reports for long-running queries, or setting up alerts for queries that run longer than a set threshold. The addition of these granular query performance metrics should make it easier to identify and address query performance challenges.
Try it out
With this new Performance Recommendations dashboard and underlying Query Performance Metrics Explore, we are providing tools to more easily identify and address query bottlenecks so that you can optimize the efficiency of your data environment. Just head over to System Activity and check out the new Performance Recommendations dashboard to get started.