Use administrative jobs explorer

As a BigQuery administrator, you can use the administrative jobsexplorer to help you monitor jobs activity across your organization. Jobsexplorer also provides an array of filters and sorting options to troubleshootand identify problematic jobs. Jobs explorer removes the need for you to have adeep understanding ofINFORMATION_SCHEMA, and lets you quickly view jobinformation such as owner, project, slot usage, duration, and more, without theneed to writeINFORMATION_SCHEMA queries.

You can also select individual jobs to open thejob detailspage, which provides query details such as execution graphs,SQL text, and execution history to help you diagnose and troubleshoot queries.From this page, you cancompare two jobs to highlightsignificant differences between them and address potential performance issues.

BigQuery provides job details and insights through the followingINFORMATION_SCHEMA views:

• INFORMATION_SCHEMA.JOBS_BY_PROJECT
• INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
• INFORMATION_SCHEMA.JOBS_BY_USER

Note: If you use organization restrictions, seeEnable access to Google-owned resources.

Required roles

To get the permissions that you need to use administrative jobs explorer, ask your administrator to grant you theBigQuery Resource Viewer (roles/bigquery.resourceViewer) IAM role on your organization or project. For more information about granting roles, seeManage access to projects, folders, and organizations.

This predefined role contains the permissions required to use administrative jobs explorer. To see the exact permissions that are required, expand theRequired permissions section:

Required permissions

The following permissions are required to use administrative jobs explorer:

• To see data at project level: bigquery.jobs.listAll on the project
• To see data at organization level: bigquery.jobs.listAll on the organization
• To filter by reservations in your organization: bigquery.reservations.list on the organization

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

Note: This organization view is only available if you have defined Google Cloud organizations.

Filter jobs

To filter jobs for queries that are contained in theINFORMATION_SCHEMA.JOBS* views, do the following:

1. In the Google Cloud console, go to theBigQuery page.

   Go to BigQuery

2. In the navigation menu, clickJobs explorer.

3. From theLocation list, select the location for which you want to view thejobs.

4. Apply optionalFilters as needed:

   • Job scope. Such as the current project, organization, and your jobs.
   • Status. Such as completed, error, active, and queued.
   • Job priority. Such as interactive or batch jobs.
   • Job ID.
   • Owner. Email ID of the job's owner (only applicable when job scope is project or organization).
   • Project ID. (Only applicable when job scope is organization)
   • Reservation ID. (Only applicable when job scope is organization)
   • Slot time more than. Jobs that took more time than the specified slot time.
   • Duration more than. Jobs that took longer than the specified duration.
   • Bytes processed more than. Jobs that processed more bytes than the specified processed bytes.
   • Query Insights. Types of query insights, such as slot contention,memory shuffle capacity exceeded, and data input scale change.
   • Query Hash. Query hash contains the hashes of the query. It is a hexadecimal STRINGhash that ignores comments, parameter values, UDFs, and literals.This field appears for successfulGoogleSQL queries that are not cache hits.
   • Labels.key:value pairs that can be assigned to a job. You can filter using a key, value, orkey:value combination.
   • Job category. The type of query, such asStandard orContinuous query.

View query execution details

To view query execution details of jobs, do the following:

1. Go to theJobs explorer page.

   Go to Jobs explorer

2. To view jobs, clickJobs explorer.

3. Filter jobs to view limited jobs.

4. Click a job for which you want to view the query execution details.

5. In theQuery results pane, click theExecution graph tab to see the execution details of your job.

   

For information about how to interpret insights, seeInterpret query performance insights.

Get BigQuery job details

Preview

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

To request feedback or support for this feature, send an email tobq-performance-troubleshooting+feedback@google.com.

From the administrative jobs explorer, you can view the BigQuerydetails page. The BigQuery job details page combines severalquery details into one page to assist in diagnosing and troubleshooting queries.ThePerformance tab compiles query information including the executiongraph, the SQL text, and execution history.

ThePerformance tab also supports query comparison, enabling you to compareyour historical usage of the query and analyze and resolve any possibleperformance degradation. For more information about job comparison, seeCompare jobs.

Before you begin

To get the permissions that you need to work with BigQuery job details and system-leveldetails, ask your administrator to grant you the following IAM roles on your organization or project:

• View job details: BigQuery Resource Viewer (roles/bigquery.resourceViewer) - the project where the queries were run
• View system-level details: BigQuery Resource Viewer (roles/bigquery.resourceViewer) - the administration project

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

These predefined roles contain the permissions required to work with BigQuery job details and system-leveldetails. To see the exact permissions that are required, expand theRequired permissions section:

Required permissions

The following permissions are required to work with BigQuery job details and system-leveldetails:

• To see data at project level: bigquery.jobs.listAll on the project
• To see data at organization level: bigquery.jobs.listAll on the organization
• To filter by reservations in your organization: bigquery.reservations.list on the organization

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

View job details

To view the job details page, do the following:

1. Go to theJobs Explorer page.

   Go to Jobs Explorer

2. Optional:filter the jobsto narrow the available jobs displayed.

3. Click the job ID of the job you want to view. For queries that don't create ajob, the query ID appears and the link is disabled. For all otherqueries, clicking the job ID displays theJob details page.

ThePerformance tab is displayed by default. You can navigate to the othertabs to view additional job information.

Available query information

The following list describes the information and metrics available on thePerformance tab.

• SQL Query: the text of the SQL query that created the job.

• Job details: the information about the job, including the job ID, creationtime, bytes processed, and more. For more information, seeView jobdetails.

• Performance variance: information about how the job performed incomparison to a past execution of the same query. BigQuerycompares the current job to a past execution with similar bytes processed(+/- 5%) and the shortest job duration, if one exists. If no such pastexecution exists, then BigQuery compares the current job tothe average of past executions in the last 30 days. If there are no pastexecutions, then this section indicates that no similar job was found forcomparison.

• Execution history: a list of other executions of this query by query hash.From this panel, you can select a job to compare to the job you are viewing. Formore information about comparing jobs, seeComparejobs.

• System load during execution: a description of the resourcesBigQuery used to execute the job. This includes informationabout the reservation settings used by this job, if applicable.

• Execution graph: the execution graph of this job. For more information,seeGet query performance insights.

Compare jobs

Preview

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

To request feedback or support for this feature, send an email tobq-performance-troubleshooting+feedback@google.com.

The job performance comparison lets you compare your baseline job totargeted jobs, and the query analysis highlights job details that differsignificantly between the two jobs. This helps you troubleshoot potentialperformance issues between two query jobs.

Important details to consider for optimizing your queries when comparing twoqueries include job duration, slot time, and bytes processed.

Before you begin

To get the permissions that you need to work with BigQuery job details and system-leveldetails, ask your administrator to grant you the following IAM roles on your organization or project:

• View job details: BigQuery Resource Viewer (roles/bigquery.resourceViewer) - the project where the queries were run
• View system-level details: BigQuery Resource Viewer (roles/bigquery.resourceViewer) - the administration project

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

These predefined roles contain the permissions required to work with BigQuery job details and system-leveldetails. To see the exact permissions that are required, expand theRequired permissions section:

Required permissions

The following permissions are required to work with BigQuery job details and system-leveldetails:

• To see data at project level: bigquery.jobs.listAll on the project
• To see data at organization level: bigquery.jobs.listAll on the organization
• To filter by reservations in your organization: bigquery.reservations.list on the organization

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

Understand job performance comparison

The following sections describe theJob performance comparison page and thetroubleshooting information it provides.

You can turn on theShow only significantdifferences toggle to view only significant differencesbetween the two jobs for all metrics included on this page.

Significant differences are highlighted in green, yellow, and red:

• Green: The change moves in a positive direction. For example, becauseshorter query duration is better, if the target job runs faster than thebaseline job, it is marked as green.
• Yellow: The change moves in a negative direction, but the difference isless than 20%.
• Red: The change moves in a negative direction, and the difference islarger than 20%.

Query level analysis

TheQuery level analysis pane describes the differences between the two jobsat the query level. It contains three tabs: theMetrics,SQL text, andExecution graph tabs.

• TheMetrics tab describes the query metrics for the two jobs. Use this tabto determine if discrepancies exist between job duration, unusedaccelerators, and other metrics.

• TheSQL text tab shows the two SQL statements that created the jobs andhighlights differences between them. Use this tab to determine whetherchanges in the SQL statements affected job performance.

• TheExecution graph tab compares theexecutiongraphs of the two jobs. Use this tab todetermine whether discrepancies occurred at any stage during the execution ofthe jobs.

System level analysis

TheSystem level analysis pane describes the factors potentially affectingthe two jobs at the system level. It consists of three sections: theProject table, theReservation table, and theSettings table.

TheSystem level analysis pane can suggest areas forimprovement based on the differences between the two queries.

For example, if a job received fewer slots than previous executions, it might beimpacted by resource constraints in the system. If this is indicated, check theproject level metrics to see if the project as a whole received fewer slots. Ifthe project did not receive fewer slots, then contention likely occurred at theproject level, such as an increase in job concurrency. If the project didreceive fewer slots, then check the reservation level to identify anyconstraints.

• TheProject table compares the two jobs at the project level. Use thistable to determine if any optimization is possible at the project level.

• TheReservation table compares the two jobs at thereservation level. Use this table toidentify differences in reservation usage between the two queries thatcould affect job performance.

• TheSettings table compares the reservation settings between the twojobs. Use this table to detect any changes to reservation settings that couldhave caused a performance impact.

Compare two jobs

To compare two jobs:

1. Go to theJobs Explorer page.

   Go to Jobs Explorer

2. Optional:filter the jobsto narrow the available jobs displayed.

3. Click the job ID of the initial job you want to view and compare. TheJobdetails page appears.

4. Click thePerformance tab.

5. ClickCompare Job.

6. In theJob one (baseline job) field, clickBrowse. TheSimilar comparable jobs panel appears.

7. Identify the job you want to compare your baseline job to, and clickCompare. The job performance comparison appears.

8. To view only significant differences between the two jobs, turn on theShowonly significant differences toggle.

Change the jobs being compared

To change the jobs being compared:

1. Go to theJob performance comparison page.

2. In theJob one (baseline job) field, clickBrowse.

3. In theSimilar comparable jobs pane, identify the job you want to compareand clickCompare.

Pricing

Jobs explorer is available at no additionalcost. Queries that are used to populate these charts aren't billed and don't use slots inuser-owned reservations. Queries that process too much data are timed out.

What's next

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