Understanding query performance metrics

The Query Performance Metrics Explore in System Activity provides detailed breakdowns of each query that is run from your Looker instance.

For example, you can use this Explore to investigate which components of a query took longest to load. You can also use this Explore to identify query performance trends and anomalies.

For more information about the Explores that are available in System Activity, see the Monitoring Looker usage with System Activity Explores documentation page.

Viewing query performance metrics

Query performance metrics are visible in the System Activity Query Performance Metrics Explore. You must be a Looker admin or have the see_system_activity permission to view the Query Performance Metrics Explore.

Understanding the Looker query lifecycle

Each query that Looker sends to your database goes through several phases, each with several steps. Each of these steps is represented as a dimension in the Query Performance Metrics Explore.

At a high level, the phases can be conceptualized as follows:

Queue: The query waits for an available asynchronous worker.
Initialization and connection: The worker prepares the query and connects to your database.
Running queries: Looker runs the main query and any additional queries, such as totals or derived tables, on your database and loads the results.
Processing results: Looker post-processes the results and writes them to the cache.

Async processing tracks the total time an asynchronous worker spends on a query, spanning phases 2 through 4. For queries from Explores and dashboards, the cumulative duration of these phases is approximately equal to the Async processing value. For downloads, scheduled deliveries, and other exported results, Stream to Cache overlaps with the other phases; in these cases, the combined time of the individual phases may exceed the total Async processing duration.

Query performance metrics

The following sections list each query phase in chronological order. Each query phase section lists its query steps in alphabetical order. Each query step metric is measured in elapsed time in seconds.

Timings: Queue

Query Step	Metric Description
`Queued`	Time in seconds that a query spends waiting for an asynchronous worker to be available to run the query.

Timings: Initialization & Connection

Query Step	Metric Description
`Acquire Connection`	Time that it takes in seconds for the Looker instance to acquire a connection to the customer database. This step includes the time to look up the credentials for the user and create the connection pool if it does not already exist.
`Cache Load`	Time that it takes in seconds to pull raw results from the result set cache.
`Explore Init: Computed`	Time that it takes in seconds to initialize the Explore before starting to `prepare` it. If this step returns a null value, the Explore was retrieved from cache instead of loaded and parsed from scratch. (See the `Explore Init: From Cache` step.)
`Explore Init: From Cache`	Time that it takes in seconds to pull the Explore initialization from cache. If this step returns a null value, the Explore was loaded and parsed instead of pulled from cache. (See the `Explore Init: Computed` step.)
`Model Init: Computed`	Time that it takes in seconds to load and parse the model required to run a query. If this step returns a null value, the model was retrieved from cache instead of loaded and parsed from scratch. (See the `Model Init: From Cache` step.)
`Model Init: From Cache`	Time that it takes in seconds to pull the model definition from cache. If this step returns a null value, the model was loaded and parsed instead of pulled from cache. (See the `Model Init: Computed` step.)
`Per User Throttler`	Time in seconds that the query spends waiting for a connection to be available for the user to run the query.
`Prepare`	Time that it takes in seconds to prepare the query from the Explore definition.
`Project Init`	Time in seconds that it takes to initialize the LookML project or read it from cache.

Timings: Running Queries

Query Step	Metric Description
`Execute Grand Totals Query`	Time that it takes in seconds to run the query to generate the grand total on the customer database. Applies only to queries with both totals and row totals enabled.
`Execute Main Query`	Time that it takes in seconds to run the `primary` query on the customer database. This does not include connection initialization time or throttler time; this timing begins after connection initialization and any throttlers have completed.
`Execute Misc. Ancillary Queries`	Time in seconds that it takes to run supporting queries, such as extents and dimension fill queries, on the customer database.
`Execute Row Totals Query`	Time that it takes in seconds to run the query to generate row totals on the customer database. Applies only to queries with row totals enabled.
`Execute Totals Query`	Time that it takes in seconds to run the query to generate totals on the customer database. Applies only to queries with totals enabled.
`Load Grand Totals Query In Memory`	Time that it takes in seconds to load the query to generate the grand total into memory. Applies only to non-streamed queries with both totals and row totals enabled.
`Load Main Query In Memory`	Time that it takes in seconds to load the main query results in memory from the customer database. Applies only to non-streamed queries.
`Load Misc. Ancillary Queries In Memory`	Time in seconds that it takes to load supporting queries, such as extents and dimension fill queries, into memory.
`Load Process and Stream Main Query`	Time that it takes in seconds to load the main query (from the customer database), process it (on the Looker instance), and stream it (to the client). Applies only to streamed queries.
`Load Row Totals Query In Memory`	Time that it takes in seconds to load the query to generate row totals into memory. Applies only to non-streamed queries with row totals enabled.
`Load Totals Query In Memory`	Time that it takes in seconds to load the query to generate totals into memory. Applies only to non-streamed queries with totals enabled.
`PDTs`	Time that it takes in seconds to build the persistent derived tables that are required for the query.
`Temporary DT`	Time in seconds that it takes to build temporary derived tables. Applies only to dialects that don't support common table expressions (CTEs) or inline derived tables, such as MySQL.

Timings: Processing Results

Query Step	Metric Description
`Postprocessing`	Time in seconds necessary for post-processing the query. Occurs after the connection is closed.
`Stream to Cache`	Time in seconds that results are streamed to the cache. For downloads, scheduled deliveries, and other exported results, this step includes running the query, so it overlaps metrics in other phases.

Timings: Overall

Query Step	Metric Description
`Async processing`	Time in seconds that an asynchronous worker spends on the query. The worker continues working on the query until after the processing results phase is complete, so this metric overlaps with most other metrics on this page.
`Connection Held`	Time in seconds that the Looker instance maintains a connection to the customer database. This step includes the time that it takes for the customer database to run the SQL query.

Each metric also has an average measure. The Timings: Summaries group shows the total and average time for each phase.

BigQuery BI Engine metrics

If you are using BigQuery BI Engine with Looker, you can use the Query Performance Metrics Explore to view database-specific information about your queries. Queries that don't use BI Engine and queries of databases other than BigQuery databases return null values for these metrics.

Metric	Metric Description
`BigQuery Job ID`	The BigQuery job ID for the query (or NULL). For faster response time for queries in BigQuery, Looker executes BigQuery queries using `jobCreationMode=JOB_CREATION_OPTIONAL`. In this mode, when BigQuery determines it can finish a query quickly, BigQuery prioritizes returning immediate results instead of creating a BigQuery job. In this case, the query will have a BigQuery query ID (and the BigQuery job ID will be NULL). If BigQuery determines that it cannot return immediate results, BigQuery will create a job and the query will have a BigQuery job ID (and the BigQuery query ID will be NULL).
`BigQuery Query ID`	The BigQuery query ID for the query (or NULL). For faster response time for queries in BigQuery, Looker executes BigQuery queries using `jobCreationMode=JOB_CREATION_OPTIONAL`. In this mode, when BigQuery determines it can finish a query quickly, BigQuery prioritizes returning immediate results instead of creating a BigQuery job. In this case, the query will have a BigQuery query ID instead of a BigQuery job ID. If BigQuery determines that it cannot return immediate results, BigQuery will create a job and the query will have a BigQuery job ID (and the BigQuery query ID will be NULL). If this field is blank, BigQuery was unable to execute the query immediately and instead created a job to run the query. See `BigQuery Job ID` instead to get an ID for the BigQuery job.
`BI Engine Mode`	Whether the query was able to run partially or fully accelerated. See BI Engine acceleration statistics for more information on the possible values for this field.
`BI Engine Reason`	If the query was not able to run fully accelerated, this field displays the reason. This message comes directly from Google BigQuery.

Troubleshooting with query performance metrics

Analyzing query metrics can help improve performance on your Looker instance. To get started, select the Performance Recommendations dashboard from the list of System Activity dashboards.