Monitor health, resource utilization, and jobs

As a BigQuery administrator, you can monitor your organization'shealth, slots use, and jobs performance over time with operational health andresource utilization charts.BigQuery provides configurable charts to help you with thefollowing:

• Monitor operational health of BigQuery.BigQuery real-time operational health monitoring is acentralized monitoring system that lets you observe BigQueryusage across the organization in multiple locations.

• View BigQuery resource utilization.Use historical data to perform root-cause analysis, plan capacity, anddiagnose performance changes.

Before you begin

• To view operational health, you must usereservations that you canaccess with anadministrationproject. This isn't arequirement for viewing resource utilization charts.

Required roles

To get the permissions that you need to view all data in the operational health and resource utilization charts, ask your administrator to grant you the following IAM roles on your organization:

• BigQuery Resource Viewer (roles/bigquery.resourceViewer)
• BigQuery Metadata Viewer (roles/bigquery.metadataViewer)

For more information about granting roles, seeManage access to projects, folders, and organizations.

These predefined roles contain the permissions required to view all data in the operational health and resource utilization charts. To see the exact permissions that are required, expand theRequired permissions section:

You might also be able to get these permissions withcustom roles or otherpredefined roles.

1. In the Google Cloud console, go to the BigQueryMonitoringpage.

   Go to Monitoring

2. Select the administration project that you used to purchase slots and createreservations.

3. In theMonitoring page, go to theOperational health tab to view asummary of your organization's key metrics for all locations andreservations.

4. Optional: To view real-time metrics, where queries run on fresh data everyfive minutes, click theLive data toggle. By default, this setting isturned off and the maximum staleness of the data is about an hour.

5. To filter the metrics, configure the following fields:

   • In theSelect location list, select a region to monitor.
   • In theSelect reservation list, select the reservation.
   • In theSelect project list, select the project.
   • In theTime range list, select a time range.

6. Optional: To view more details about operational health with aresourceutilization chart orjobsexplorer, clickExplore more.

BigQuery gathers the metrics by querying the followingINFORMATION_SCHEMA views:

• INFORMATION_SCHEMA.JOBS
• INFORMATION_SCHEMA.JOBS_TIMELINE
• INFORMATION_SCHEMA.RESERVATIONS
• INFORMATION_SCHEMA.TABLE_STORAGE

Views

TheOperational health tab displays the following summary and detailedviews.

Summary view

The summary view shows you the health of your organization's subsystems,including reservations and regions, in the last 30 minutes.

To view the summary view, do the following:

• In theOperational health tab, in theSelect location list, selectAll locations.

Detailed view

The detailed view shows detailed timeline charts of different metrics at alocation or a reservation level.

To view the detailed view, do the following:

• In theOperational health tab, in theSelect location list, selectthe region or edition.

You can refine the data displayed in the detailed view using thefollowing optional filters:

• In theSelect reservation list, select the reservation name.
• In theSelect project list, select the project.

Charts

BigQuery provides the following table and chart options, whichlet you explore your operational health metrics in detail.

Summary table

The summary tables display the following metrics:

• Slot usage. The average slot utilization. The total slot capacity equalsbaseline capacity and autoscaled max capacity.
• Shuffle usage. The maximum of all average reservation shuffle usageratios.
• Concurrency. The maximum number of jobs running simultaneously.
• Bytes processed. The total bytes processed by completed jobs.
• Job duration. The average runtime of the jobs completed, calculatedfrom the time when the job was created until the time when the job ended.
• Total storage. The total active logical bytes at the last updated time.

There are two summary tables presented in the view:Location summary andReservation summary. Each table row represents usage for one location or onereservation.

The tables display metrics for the 30 minutes prior to the last update. IfLive data is enabled, the queried data refreshes every five minutes. IfLive data is disabled, then the maximum data staleness is approximately onehour.

The table cell is color coded if the metric is greater than the predefinedthreshold and if there are an increased number of performance insights forrepeated jobs:

• If the metric value over the 30 minutes prior to the last update is greaterthan the P99 value of the last week's usage for the same day, the table cellis marked with a dark blue background.
• If the metric value over the 30 minutes is between the P95 - P99 value ofthe last week's usage for the same day, the table cell is marked with alight blue background.

All thresholds are predefined and can't be customized. You cancheck a threshold by clicking infoInfo.

• Location. Regions where the organization has had activity within thepast 30 days are available. Thesummary view presentsa table summarizing usage in all active regions.
• Reservation. This field is available only in thedetailedview. All reservations owned by the administration projectare available. You can only choose one reservation.
• Project. This field is available only in thedetailedview. All projects that have traffic in the organizationare available. You can select only one project.
• Time range. This field is available only in thedetailedview. It affects theMetrics timelinecharts and theError donutchart in the detailed view. The data scale in theMetrics timeline charts is adjusted automatically according to theselected time range.

Top active queries chart

This chart shows the top ten jobs with active resources that are sorted indescending order. In the drop-down menu, you can select a sorting optionbased on slot usage or job duration. The job ID and relevant resource usagenumber are presented in the bar chart. SelectExplore more or the job ID inthe top active queries chart to view more details in thejobsexplorer. To learn more about theexecution details and diagnose performance issues for yourBigQuery jobs, seeGet query performance insights.

Error donut chart

This chart shows the proportion of the top causes of failure in the selectedtime period. In thesummary view, it defaults to cover the last30 minutes. In the detailed view, the time range selector controls its coverage.You can group the errors by type, owner project, or reservation.The count of failed jobs is presented in the donut chart.

Metric timeline charts

The charts display an overview of supported metrics over a maximum of 30days. The default time period is one hour. As the selected time period changes,the granularity of each data point in the chart is scaled automatically.

These charts display an aggregated value over a region or a reservation.Displaying data for multiple regions or multiple reservations is not supported.

The Metric timeline charts support the following metrics:

• Slot usage. The average slot usage in the selected time period. Itdisplays the overall slot usage from all jobs and failed jobs separately.
• Job concurrency. The maximum number of jobs running simultaneously inthe selected time period. It displays the count of running jobs and pendingjobs separately and is shown as a stacked bar chart.
• Bytes scanned. The total bytes processed by completed jobs in theselected time period.
• Shuffle. The maximum of all reservation average shuffle usage ratios inthe selected time period.
• Job duration. The average runtime of jobs completed in the selectedtime period, calculated from job creation time to job end time.
• Error by type. TheError donut chart shows theproportion of different causes of failure in the selected time period.

Besides the metric trends, the charts display reference lines of the P95and P99 metrics values of the last week's usage for the same day. TheJobconcurrency chart shows the threshold for the sum of pending and runningjobs. Those referenced values are used as the color coding thresholds in thesummary table.

To learn more about the chart, clickExplore more. You are redirected to theResource utilization tab.

Insights table

This table aggregates quota errors, access denied errors, and performanceinsights gathered during job executions within the selected time period.Insights are aggregated at reservation level. Each row provides informationabout the insight type, location, reservation, insight detail, and sample jobIDs. Click job IDs to view more job details in thejobsexplorer. In thesummaryview, the default observation timeframe covers the last 30minutes. In the detailed view, the time range selector controls the specifictime period for which observation data is displayed.

Monitor operational health for a single project

The project operational health dashboard is the default view you get if youdon't have access to the entire organization, or your project doesn't own anyreservation. This view can help project analysts monitor system health for theirprojects, much like the operational health dashboard at the organizationallevel, but it shows only project-level data in its charts and filters.

View BigQuery resource utilization

BigQuery resource charts help you track past resource use to planfor future needs and troubleshoot performance.

The charts gather metrics by querying the followingINFORMATION_SCHEMA views:

• INFORMATION_SCHEMA.JOBS
• INFORMATION_SCHEMA.JOBS_TIMELINE
• INFORMATION_SCHEMA.RESERVATION_CHANGES

The data can be updated in real time, going back a maximum of 30 days.

When you view resource utilization, you can configure the following:

• The event timeline chart shows an overview of data over a maximumof 30 days. The default is 6 hours.

• The main chart shows chosen metrics, such as slot usage or bytes processed,over time for your organization or administration project. A legend for thechart gives more details about the data shown.

• TheChart configuration pane lets you select predefined views of yourmetrics or customize your own metrics for the views.

The resource utilization chart has the following elements:

• In the resource utilization tab, hold the pointer over the status chip atthe top of the chart to display more information about a metric.

• The status chips also shows you the following:

  • If the metric is still loading, a loading icon is presented.
  • If the metric uses stale data, a clock icon is presented.
  • If the metric can't be loaded due to unsupported page-level filters, themetric is crossed out.
  • If the metric fails to load, the status chip is highlighted.

View and configure resource utilization charts

To view and configure resource utilization charts, follow these steps:

1. In the Google Cloud console, go to the BigQueryMonitoringpage.

   Go to Monitoring

2. Select the project. As an administrator monitoring reservation resourceuse, choose the administration project used to buy slots and createreservations. As a data analyst monitoring job resource use, choose thecorresponding project.

3. In theMonitoring page, go to theResource utilization tab to view asummary of the resource usage, broken down by location.

4. Choose a time period for the metrics in this view, such as1 day. Toview real-time metrics, where queries run on fresh data, click theLivedata toggle. This setting is turned off by default to improveperformance, and the maximum staleness of the data is about an hour.

5. In theChart configuration pane, configure the following fields:

   • In theMetric view field, select one of the views.
   • In theMetrics field, select a metric to view. Youcan add or edit a metric.
   • In theGroup by field, select how to group the data.
   • In theFilters field, select how to filter thedata.

6. To save the changes you've made to the chart configuration, clickApply.

Metric view

BigQuery provides pre-configured views of resourceutilization metrics. The following sections describe the metrics that you canconfigure in those views.

Reservation slot usage

This view shows you metrics about the slot usage breakdown for the reservationsin the administrative project. Each metric has the following default settings,which you can edit in theChart configuration pane by clicking the metric'sname:

• The default usage data is from the reservation resources.
• The default scope is the project.
• The default aggregation is by the average usage in the selected time period.

Edition utilization overview

This view shows you metrics about slot usage and capacity for editionresources in the administrative project. Each metric has the following defaultsettings, which you can edit in theChart configuration pane by clicking themetric's name:

• The default usage data is from the reservation resource and the capacitydata is from the edition resource.
• The default scope is the project.

Current project overview

This view shows you metrics about job resources in the project where you runqueries. Each metric has the following default settings, which you can edit intheChart configuration pane by clicking the metric's name:

• The default resource is the job resource.
• The default scope is the project.

Owned reservation overview

This view shows you metrics about job activity with reservation resources in theadministrative project. Each metric has the following default settings, whichyou can edit in theChart configuration pane by clicking the metric's name:

• The default resource is the reservation resource.
• The default scope is the project.

1. In the Google Cloud console,view the BigQuery resource utilization charts.

2. In theChart configuration pane, configure theMetrics field:

   1. ClickAdd an item> Select a metric.

   2. In theSelect a metric dialog, choose the following:

      1. The type of resource to monitor:Job,Reservation, orEdition.
      2. The scope to monitor. For example, to monitor job metrics in thecurrent project, select theProject scope.
      3. Choose a metric to monitor. For example, to monitor the job slotusage, chooseSlot usage.

   3. ClickApply.

   4. In theNew item dialog, choose the aggregation. For example, tomonitor the maximum slot usage in excess of the reservation's capacityin the selected time period, chooseMax.

   5. Save the metric by clickingDone.

3. Optional: To change an existing metric, click the metrics name and edit itssettings or delete it.

Supported resource types and scopes for metrics

Resource utilization metrics are categorized by resource type and scope.

Resource types

You can monitor the following resource types:

Scope types

After you select a resource for a metric, you select a scope.

• The reservation and edition resources default to a project-level scope.
• The job resource can be set to project-, folder-, or organization-levelscopes. Greater scopes require additional permissions.

Group by

To group data in your resource utilization chart, follow these steps:

1. In the Google Cloud console,view the BigQuery resource utilization charts.

2. In theChart configuration pane, configure theGroup by field byselecting one of the following options:

   • None: This option gives you a high-level overview of the chosenmetrics, over time, across the entire organization.
   • Project: This option groups chosen metrics by project. If more than50 projects are available, only the top 50 projects with the highestslot usage are shown.
   • All other group-by options: These options are similar to the projectview, with data grouped by the respective dimension. If a group-byoption isn't supported for all chosen metrics, the option can't beselected.

Filter

When you configure your resource utilization chart, you can apply filters toyour data, such as displaying resource usage for Enterprise editionresources, or by a resource ID.

To filter the chart data, apply filters in the filter pane. You can only selecta filter if it's supported for the metrics you selected. To view the requiredpermissions, click theFilter menu.

View project-level resource utilization data

You can analyze project-level resource utilization using the sameconfigurationsteps as you would for viewingorganization-level data. Charts only display project-level data andconfiguration options (organizational-level options are greyed out). Thisproject-level scope shows the overall resource utilization, regardless ofbilling mode within the contextual project.

Resource utilization chart limitations

• The chart and table in the middle of the page display metrics that arerelevant to the time period and granularity selected.
• Metrics with the same unit share the same scale. The chart supports up totwo different scales.

Monitor autoscaling

When you monitor slot usage withadministrative resourcecharts, you might see significantly morescaled slots than your slot usage because the charts smooth the number of usedslots over the alignment period. To view autoscale slot usage with more precisedetails, reduce the time frame option. This automatically updates the alignmentperiod to a smaller increment.

In the following example, the chart displays significantly more scaled slotsthan the workload demands.

However, if you shorten the time frame option so that the alignment period istwo seconds, you can see that the autoscaler scales to the workload demand anddisplays more accurate data. You can adjust the time frame option by draggingthe start and end ranges of the time frame option. To display the most accurateworkload demand data, selectp99 from theMetric list.

For the most accurate view of autoscale usage,use an alignment period between 1 and 15 seconds.

For information about viewing your slot usage, seeView administrative resource charts.

Monitor job performance

You may need to adjust your autoscalingmax_slots to avoidhigher costs. The following query provides context on your job performance soyou can choose the correct amount of autoscaling slots for your workload.

The following query provides details on your reservations past job performance:

SELECTAVG(TIMESTAMP_DIFF(end_time,creation_time,MILLISECOND))asavg_latency_ms,SUM(total_bytes_processed)astotal_bytes,COUNT(*)asquery_numbers,FROM`PROJECT_ID.region-REGION_NAME`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATIONWHEREcreation_time>=START_TIMEANDcreation_time<END_TIMEAND(statement_type!="SCRIPT"ORstatement_typeISNULL)ANDreservation_id=RESERVATION_ID

Replace the following:

• PROJECT_ID: the ID of the project
• REGION_NAME: the region for your project
• START_TIME: the creation time you want to start viewing the data
• END_TIME: the creation time you want to stop viewing the data
• RESERVATION_ID: the reservation ID

The following example gets the job details over a five day period:

SELECTAVG(TIMESTAMP_DIFF(end_time,creation_time,MILLISECOND))asavg_latency_ms,SUM(total_bytes_processed)astotal_bytes,COUNT(*)asquery_numbers,FROM`myproject.region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATIONWHEREcreation_time>='2024-06-25 00:00:00-07'ANDcreation_time<'2024-06-30 00:00:00-07'AND(statement_type!="SCRIPT"ORstatement_typeISNULL)ANDreservation_id=reservationID

Troubleshoot slot contention

Slot contention can happen when there aren't enough slots to run all of yourjobs, causing performance issues. To troubleshoot slot contention issues, seethe following steps and best practices.

If you have tried these best practices but are still experiencing jobperformance issues, you canrequest support.

Job concurrency spikes

Use theDetailed view to check for sudden surge in job runswhere there are simultaneous slot usage spikes. This can indicate that there aretoo many jobs contending for slots limited by your reservation limit.

• Best practice: Consider optimizing resource-intensive queries oryour reservation's slot capacity. For more information about optimizing queryperformance, seeOptimize query computation.

High slot usage

Use theDetailed view to check for increased job durations,especially if there are jobs that exceed your reservation's maximum capacity.Consistently high slot usage can indicate ongoing slot contention.

• Best practice: Check queries using thejobsexplorer's slot contention filter toidentify those consuming the most slots and optimize them.

Lengthy job durations

If jobs are taking significantly longer to complete, check theDetailed view. High job concurrency and slot usage spikes canindicate slot contention.

• Best practice: Isolate critical jobs by temporarily pausing lessimportant jobs or reducing your overall job submission rate.

Slot contention messages

Theinsights table candisplay messages such asThere wereNUMBER jobs detected withslot_contention in the reservation. that indicate slot contention issues.Check thejobs explorer to review detailsabout the specific jobs flagged in these messages.

• Best practice: Optimize the identified queries or adjust yourreservation's slot allocation.

What's next

• Learn aboutreservations.
• Learn aboutpurchasing slots.
• Learn how toestimate slot capacity requirements.
• Learn how toview slot recommendations and insight.