+1 (726) 227-2971
Vistelio

Monitoring Looker with System Activity: Finding Slow Explores, Unused Fields, and Costly Users

Looker records every query, dashboard view, PDT build and login in System Activity, a set of explores on the instance's own database that admins (and anyone granted see_system_activity) can query like any other data. Most teams open it only when something is on fire. Used deliberately, it tells you which explores are slow, which fields and dashboards nobody uses, and which users and schedules are running up the warehouse bill. This tutorial walks through the explores that matter, the dashboards worth building on them, and how to feed what you learn back into the model.

The explores

Open Admin > System Activity (or Explore and look for the System Activity model). The ones you will use most:

  • History: one row per query run: who, which model and explore, fields used, runtime, status, source (dashboard, explore, API, schedule), cache hit, and the dashboard or Look it came from.
  • Dashboard / Look: content metadata with view counts, last viewed, owner and folder.
  • Field Usage: counts of how often each LookML field has been queried.
  • PDT Event Log / PDT Log: every derived table build, with duration, status and trigger.
  • Database Performance: query counts and runtimes by connection, with queue time separated from database time.
  • User: users with last login, role and group membership.
  • Event: audit events (logins, permission changes, content deletions).

System Activity retains about 90 days of history on most instances. If you need longer, schedule exports of the key explores to your warehouse monthly; a year of history is what makes the "unused" analyses trustworthy.

Finding slow explores and dashboards

Start in History. A first pass that finds what users feel:

  • Dimensions: Query > Model, Query > Explore, History > Source
  • Measures: History > Query Run Count, History > Average Runtime in Seconds, History > Max Runtime
  • Filter: History > Created Date in the last 14 days, History > Status is complete
  • Sort by average runtime, then look at run count

The top of that list is either a single painful explore or a dashboard whose tiles all hit the same explore. To find the dashboards:

  • Dimensions: Dashboard > Title, Dashboard > ID
  • Measures: History > Query Run Count, History > Average Runtime in Seconds, History > Total Runtime
  • Filter: History > Source is dashboard

Total Runtime (run count times average) is the number to sort by; it finds the dashboard that is only moderately slow but viewed two hundred times a day. Add History > Result Source to see how many of those runs were served from cache; a low cache share on a heavily viewed dashboard means the datagroup is wrong before it means the SQL is wrong.

For the query itself, add Query > Fields Used and Query > Filters as dimensions, or click through to the query's SQL from the History > Query ID link. In our experience the usual culprits are, in order: a filter that defeats partition pruning, a fan-out join that makes every measure a symmetric aggregate over a huge set, a count_distinct on a high-cardinality field, and a missing aggregate table. See Aggregate Awareness for the fix to the last one.

Separate database time from Looker time with Database Performance: if queue time is high but database runtime is low, you are hitting the connection's max_connections and the fix is concurrency settings or a smaller dashboard, not SQL.

Finding unused fields, explores and dashboards

Pruning is the other half of performance work: every field is validator time, IDE noise, and a candidate for Gemini to choose wrongly.

Unused fields. In Field Usage, dimensions Field Usage > Model, Field Usage > Explore, Field Usage > Field, measure Field Usage > Times Used, filtered to the last 90 days. Export it, join it to a list of all fields in the project (the LookML validator API or a quick script over the .lkml files), and the fields with zero usage are your candidates. Hide them first (hidden: yes), wait a cycle, delete on the next pass.

Unused explores. In History, Query > Explore with Query Run Count, last 90 days. Explores with a handful of runs, all by developers, should be hidden (hidden: yes on the explore) and then removed.

Unused dashboards. In Dashboard, dimensions Dashboard > Title, Dashboard > Folder Name, Dashboard > Last Viewed Date, with Dashboard > View Count over 90 days, sorted ascending. Pay special attention to dashboards with zero views but a schedule; they cost warehouse money every day for nobody. The Scheduled Plan explore lists them with their owners.

Finding costly users and schedules

Warehouse cost follows query volume and runtime, both of which History attributes to a user and a source.

  • Dimensions: User > Name, History > Source
  • Measures: History > Query Run Count, History > Total Runtime, History > Approximate Web Runtime
  • Filter: last 30 days

Three patterns recur. A scheduler user (often a service or former employee) running hundreds of schedules; an embed user pool generating most interactive queries, which is normal but should be sized; and one or two power users building enormous explores with no filters. On BigQuery, join this to INFORMATION_SCHEMA.JOBS by the labels Looker sets on each job to get bytes billed per Looker user and dashboard.

Dashboards to build on System Activity

Save these as a Looker Operations dashboard for the admin group:

  1. Top 20 dashboards by total runtime, last 7 days, with cache share.
  2. Slowest explores by average runtime with run count.
  3. PDT builds: duration by table, failures highlighted, from PDT Event Log.
  4. Queue time vs database time by connection.
  5. Scheduled plans with zero content views in 30 days.
  6. Queries by source by hour (the concurrency curve your BigQuery reservation is sized on).
  7. Error rate (History > Status is error) by explore, which surfaces broken fields before users report them.

Set an alert on PDT failures and on error rate; the rest is a weekly review.

Feeding it back into the model

The point of monitoring is model change. A repeatable loop:

  1. Weekly: review the operations dashboard; open a ticket per finding (slow dashboard, failing PDT, unused schedule).
  2. Monthly: prune. Hide unused fields and explores, delete dashboards with no views and no owner, cancel orphaned schedules.
  3. Quarterly: refactor. Add or adjust aggregate tables for the top dashboards, revisit datagroups against real refresh cadences, and re-run the BigQuery edition math with the concurrency curve.
  4. Before any AI rollout: use Field Usage to decide what the assistant is allowed to see; fields no human has used in a year should not be offered to Gemini.

This loop is the core of our recurring Looker health check, and the operations dashboard above is one of its deliverables. If you would rather have it set up than build it, get in touch.