Breaking Down Latency per Service with NRQL FACET Queries

The answer first: FACET plus SINCE/UNTIL plus LIMIT

If you need to see which service in a distributed system is contributing the most latency, the NRQL pattern that works is:

SELECT average(duration) FACET serviceName
SINCE 15 minutes ago UNTIL now
LIMIT 1000

Run this in New Relic One under Query your data > NRQL. It returns one row per serviceName with the average transaction duration over the last 15 minutes, capped at 1000 rows. Three things make this query production-safe: FACET groups results by a dimension, SINCE/UNTIL bound the time window so the query scans a fixed slice of data, and LIMIT keeps the row count under NRQL's hard ceiling.

How the pieces fit together

The New Relic Java agent (and the other language agents) instrument your application's transactions and report a duration attribute on each Transaction event, along with dimensional attributes such as appName and, where service naming is configured, serviceName. NRQL queries that event data.

FACET serviceName tells the query engine to bucket every matching event by the value of serviceName and compute average(duration) within each bucket. Without a facet, you get one average across all services, which hides the outlier you are actually looking for.

SINCE 15 minutes ago UNTIL now defines an explicit window. NRQL accepts relative phrases (1 hour ago, 1 day ago) or absolute timestamps. Bounding both ends matters for dashboards that refresh: a query with only SINCE re-scans a growing window and gets slower as the day goes on.

A worked dashboard configuration

A practical latency-breakdown setup for a microservices environment looks like this:

SELECT percentile(duration, 50, 95, 99)
FROM Transaction
FACET serviceName
WHERE environment = 'production'
SINCE 30 minutes ago UNTIL now
LIMIT 500

Notes on this variant:

• percentile(duration, 50, 95, 99) is usually more useful than average() because averages hide tail latency. The p99 column surfaces the slow service that averages smooth over.
• The WHERE environment = 'production' clause filters out staging noise. Adjust the attribute name to whatever your agents actually report — check with SELECT keyset() FROM Transaction LIMIT 1 if you are unsure which attributes exist.
• LIMIT 500 is deliberate headroom. If you have 40 services, you will never approach it, but the explicit limit protects the query if a misconfigured agent suddenly reports hundreds of distinct names.

Pin this to a dashboard as a table or bar chart. A table sorted by p99 descending is the fastest way to answer "which service is slow right now."

Limits you will hit, and what they look like

Row ceiling. NRQL queries return at most 5000 rows. If a faceted query would exceed that, you get a "too many rows" style error rather than truncated data. The fix is always an explicit LIMIT below the ceiling, or faceting on a lower-cardinality attribute.

Query timeout. Long-running queries are aborted after roughly 15 minutes of execution, returning an error or partial results. In practice, timeouts come from unbounded time ranges (SINCE 30 days ago on a high-volume account) combined with a high-cardinality facet. Narrow the window first; it is almost always the cheapest fix.

High-cardinality facets. Faceting on serviceName or errorCode is safe because the number of distinct values is small and stable. Faceting on userId, traceId, or request IDs can produce millions of buckets, blow past the row limit, and slow the query engine. If you genuinely need per-user latency, facet by a coarse bucket (for example, a customer tier attribute) instead.

Common mistakes

1. Omitting SINCE/UNTIL. The query then uses whatever default window the chart or query builder applies, which is easy to misread and can silently change. Always state the range explicitly in saved queries.
2. No LIMIT on a FACET query. It works in testing with three services and fails in production when a deployment adds a burst of new names. Add the limit the first time you write the query.
3. Faceting on an attribute that is not reported. If serviceName is not populated by your agent configuration, the facet returns a single empty or "Other" bucket. Verify the attribute exists before building dashboards on it.
4. Using average() for alerting. Averages are fine for dashboards but lag real incidents. Use a percentile or a WHERE duration > threshold count for alert conditions.

Verifying the query does what you think

Three quick checks, all run in the NRQL query builder (no special permissions beyond read access to the account):

1. Paste the example query and confirm rows appear within the selected window. If the result is empty, widen SINCE to 1 hour and check whether data exists at all.
2. Run SELECT count(*) FROM Transaction FACET serviceName SINCE 15 minutes ago LIMIT 1000 to confirm each service is actually reporting events — a service with zero count will never appear in a latency breakdown.
3. To confirm the row-limit behavior, temporarily run the query with LIMIT 5001 and observe the error, then drop back to LIMIT 1000. This is a read-only query, so there is no state to roll back.

If serviceName returns no buckets, check the agent side: for the Java agent, confirm transaction tracing is enabled and that your services are named distinctly in newrelic.yml (or via the NEW_RELIC_APP_NAME environment variable). Attribute availability varies by agent version, so verify against the attributes your account actually reports with SELECT keyset() FROM Transaction rather than assuming a specific agent release.