Use query queues

BigQuery automatically determines the number of queries that canrun concurrently, called thedynamic concurrency. Additional queries arequeued until processing resources become available. This document explains howto control the maximum concurrency target, and set the queue timeout forinteractive and batch queries.

Overview

BigQuery dynamically determines the number of queries that canrun concurrently based on available computeresources. The number of queries that can run concurrently is calculated peron-demand project or perreservation. Additional queries are placedin a queue until there is enough capacity available to begin execution.The queue length is limited to 1,000 interactive queries and 20,000 batchqueries per project per region, regardless of whether the project is on-demandor using a reservation.The following example shows the behavior for an on-demand project when thecomputed query concurrency is 202:

For reservations, you have the option toset the maximum concurrency target, anupper bound on the number of queries that can run concurrently in a reservation,to ensure that each query isallocated some minimum number of slots. You can't specify a maximum concurrencytarget for an on-demand project; it is always dynamically computed.

Queuing behavior

BigQuery enforcesfair scheduling to ensurethat no single project can consume all of the slots in a reservation.

Queries from projects that have the smallest share of concurrency are dequeuedfirst. Duringexecution, slots are distributed fairly among projects before being distributedacross jobs within a project.

For example, suppose you have a reservation that is assigned to two projects:A and B. BigQuerycomputes 5 for the concurrency of the reservation. Project A has fourconcurrentlyrunning queries, project B has one running query, and other queries are queued.A query from project B would bedequeued first even if it was submitted after the query from project A. Aftera query begins execution, it receives a fair share of slots in the sharedreservation.

In addition to the total number of concurrent queries, BigQuerydynamically determines the maximum number ofconcurrent batch queries to run per on-demand project or reservation.If the number of concurrently running batch queries reaches this maximum, theninteractive queries are prioritized even if they were submitted later.

When you delete a reservation, all queued queries time out. When a projectassigned to a reservation is reassigned toanother reservation, all requests that are queued or running continue to do soin the old reservation, while all new requests go to the new reservation.When a project assigned to a reservation is removed from thereservation, running queries continue in the reservation whilenew and queued requests execute using the on-demand model. You can optionallycancel individual running orqueued query jobs.

Control queue timeout

To control the queue timeout for interactive or batch queries, use theALTER PROJECT SET OPTIONS statementor theALTER ORGANIZATION SET OPTIONS statementto set thedefault_interactive_query_queue_timeout_ms ordefault_batch_query_queue_timeout_ms fields in your project's ororganization'sdefault configuration.

To view the queue timeout for interactive or batch queries in your project,query theINFORMATION_SCHEMA.EFFECTIVE_PROJECT_OPTIONS view.

To turn off queuing, set the queue timeout to -1. If you reach your maximumquery concurrency, then additional queries fail with anADMISSION_DENIEDerror.

Set the maximum concurrency target

You can manually set the maximum concurrency target when you create areservation. By default, the maximum concurrency targetis zero, which means that BigQuerydynamically determines the concurrency based on available resources.Otherwise, if you set a nonzero target, the maximum concurrency targetspecifies an upper bound on the number of queriesthat run concurrently in a reservation, which guarantees a minimum amount ofslot capacity available for each query that runs.

Increasing the maximum concurrency target doesn't guarantee that more queriesexecute simultaneously. The actual concurrency depends on the available computeresources, which can be increased by adding more slots to your reservation.

Required roles

To get the permission that you need to set the concurrency in a new reservation, ask your administrator to grant you theBigQuery Resource Editor (roles/bigquery.resourceEditor) IAM role on theadministration projectthat maintains ownership of the commitments. For more information about granting roles, seeManage access to projects, folders, and organizations.

This predefined role contains the bigquery.reservations.create permission, which is required to set the concurrency in a new reservation.

You might also be able to get this permission withcustom roles or otherpredefined roles.

For more information about IAM roles in BigQuery,seePredefined roles and permissions.

Set the maximum concurrency target for a reservation

Select one of the following options:

bq mk \    --project_id=ADMIN_PROJECT_ID \    --location=LOCATION \    --target_job_concurrency=CONCURRENCY \    --reservation \RESERVATION_NAME

Update the maximum concurrency target

You can update the maximum concurrency target for a reservation at any time.However, increasing the target doesn't guarantee that more queriesexecute simultaneously. The actual concurrency depends on the available computeresources. If you reduce the maximum concurrency target, then actively runningqueries are not impacted and queued queries aren't run until the number ofconcurrent queries falls below the new target.

If you set the maximum concurrency target to 0, BigQuerydynamically determines the concurrency based on available resources(the default behavior).

Required roles

To get the permission that you need to update the maximum concurrency target for a reservation, ask your administrator to grant you theBigQuery Resource Editor (roles/bigquery.resourceEditor) IAM role on theadministration projectthat maintains ownership of the commitments. For more information about granting roles, seeManage access to projects, folders, and organizations.

This predefined role contains the bigquery.reservations.update permission, which is required to update the maximum concurrency target for a reservation.

You might also be able to get this permission withcustom roles or otherpredefined roles.

For more information about IAM roles in BigQuery,seePredefined roles and permissions.

Update the maximum concurrency target for a reservation

Select one of the following options:

bq update \    --project_id=ADMIN_PROJECT_ID \    --location=LOCATION \    --target_job_concurrency=CONCURRENCY \    --reservation \RESERVATION_NAME

Monitoring

To find out which queries are running and which are queued, look at theINFORMATION_SCHEMA.JOBS_BY_* andINFORMATION_SCHEMA.JOBS_TIMELINE_BY_*views. Thestate field is set toRUNNING for actively running queries and toPENDING for queued queries.

To view how many concurrent queries ran when the dynamic concurrency threshold was reached for each second over the last day, run the following query:

SELECTt1.period_start,t1.job_countASdynamic_concurrency_thresholdFROM(SELECTperiod_start,state,COUNT(DISTINCTjob_id)ASjob_countFROM`PROJECT_ID.REGION_ID`.INFORMATION_SCHEMA.JOBS_TIMELINEWHEREperiod_startBETWEENTIMESTAMP_SUB(CURRENT_TIMESTAMP(),INTERVAL1DAY)ANDCURRENT_TIMESTAMP()ANDreservation_id="RESERVATION_ID"GROUPBYperiod_start,state)ASt1JOIN(SELECTperiod_start,state,COUNT(DISTINCTjob_id)ASjob_countFROM`PROJECT_ID.REGION_ID`.INFORMATION_SCHEMA.JOBS_TIMELINEWHEREstate="PENDING"ANDperiod_startBETWEENTIMESTAMP_SUB(CURRENT_TIMESTAMP(),INTERVAL1DAY)ANDCURRENT_TIMESTAMP()ANDreservation_id="RESERVATION_ID"GROUPBYperiod_start,stateHAVINGCOUNT(DISTINCTjob_id)>0)ASt2ONt1.period_start=t2.period_startWHEREt1.state="RUNNING";

Replace the following:

• PROJECT_ID: the name of the project in which you ran the queries
• REGION_ID: the location where the queries were processed
• RESERVATION_ID: the name of the reservation the queries are running in

You can monitor the query queue length for your reservation by usingBigQuery administrative resource chartsand selecting theJob Concurrency chart with the metricPending.

You can also monitor the queuelength in Cloud Monitoring by viewing thejob count metric and filtering bythe number of jobs in apending state.

Limitations

• Each on-demand project can queueup to 1,000 interactive queries and 20,000 batchqueries at one time. Queries that exceed this limit return aquota error. You cannot request an increase in these limits.
• Within a reservation, each project assigned to that reservation can queueup to 1,000 interactive queries and 20,000 batchqueries at one time. Queries that exceed this limit return aquota error. You cannot request an increase in these limits.
• By default, query jobs that haven't started execution time out after 6hours for interactive queries and 24 hours for batch queries.
• You cannot set the maximum concurrency target for queries running in anon-demand project.
• You cannot set the maximum concurrency target for queries running with aStandard edition reservation. For more information about editions, seeIntroduction to BigQueryeditions.

What's next

• Learn more about diagnosing and resolvingquery queue limit errors.