Google Cloud Platform

BigQuery at speed: new features help you tune your query execution for performance

BigQuery is a managed analytics service that provides advanced cloud data warehouse capabilities with a diverse set of features. One of BigQuery’s most significant differentiators is its distributed analytics engine, which transforms your SQL queries into complex execution plans, dispatching them onto our execution nodes to promptly provide insights into your data.

Today, we're releasing into beta additional diagnostic and performance instrumentation from our analytics engine. This information provides more detail into how BigQuery organizes queries into stages and steps, dispatches workers, and progresses through complex queries.  Long-running queries also periodically publish diagnostic information while running, so you can monitor in-flight analytical workloads in greater detail.

In addition to the information published via our API, we're also bringing these insights directly to you via new and enhanced visualizations in the web user interface (UI), including a new timeline view that displays how much work is completed, queued, and actively being processed. With this new instrumentation, its possible to quickly identify whether query performance is bounded by factors such as inherent parallelism or on available resources.

People need detailed insight into query execution to isolate the principles at work, instead of just tweak-and-hope. Efficient queries allow for better resource utilization, which gives us faster results and happier users. Nicholas Ursa
New York Times Data Science Engineer
bq-perf-15rr3.PNG

For users with long-running queries, BigQuery will now also periodically provide live updates of the timeline and query plan for query jobs. This information also includes new stage statistics, which make it easier to reason about the current system utilization and queries at a much more granular level.

bq-perf-27ir2.PNG

In terms of usability enhancements, we've also made it easier to work with things like the query history by providing more filtering and search capabilities newly available in the UI. Additionally, for users who have been granted sufficient access, its possible to view the job histories of other users within your project, making it easier to review query activity from other users and service accounts.

It is our hope that these updates improve your experience. We've updated our query plan documentation with more information, and if you have feedback we'd love to hear more.